mysql insert select的使用

标签: 复制
2018-02-08 阅读(325)

mysql使用 INSERT...SELECT 可以认为表复制,您可以快速地从一个或多个表中向一个表中插入多个行。



INSERT ... SELECT语法

首先来看下INSERT ... SELECT语法:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]



如何使用使用呢?

首先2建表个表作为演示用:

-- 用户表
CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `type` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 学生表
CREATE TABLE `student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `type` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现在向用户表user插入几条数据

INSERT INTO `user`(`name`,`type`) values('张三',1);
INSERT INTO `user`(`name`,`type`) values('李四',1);
INSERT INTO `user`(`name`,`type`) values('王五',2);


现在使用 insert select 复制用户表user数据到学生表student

-- 完整复制
INSERT INTO `student` SELECT * FROM `user`;

-- 使用条件(主键自增情况)
INSERT INTO `student`(`name`,`type`) SELECT `name`,`type` FROM `user` WHERE `type`=1;

-- 指定某列(主键自增情况)
INSERT INTO `student`(`name`,`type`) SELECT `name`,`type` FROM `user`;

复制后

mysql> select * from student;
+----+------+------+
| id | name | type |
+----+------+------+
|  1 | 张三 |    1 |
|  2 | 李四 |    1 |
|  3 | 王五 |    2 |
+----+------+------+
3 rows in set (0.00 sec)

为了确保二进制日志可以被用于再次创建原表,MySQL不允许在INSERT...SELECT运行期间同时进行插入操作。

目前,您不能在向一个表插入的同时,又在一个子查询中从同一个表中选择。



怎么使用ON DUPLICATE KEY UPDATE

重复键值更新

先去除用户表的自增,例如有数据像:

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int unsigned NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `type` int DEFAULT '0',
  KEY `name` (`name`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `user`(`id`,`name`,`type`) values(1,'张三',1);
INSERT INTO `user`(`id`,`name`,`type`) values(2,'李四',1);
INSERT INTO `user`(`id`,`name`,`type`) values(2,'王五',2);

注意看数据id列为非主键非唯一索引列,有2个从重复值

mysql> select * from user;
+----+------+------+
| id | name | type |
+----+------+------+
|  1 | 张三 |    1 |
|  2 | 李四 |    1 |
|  2 | 王五 |    2 |
+----+------+------+
3 rows in set (0.00 sec)


再使用 insert select 复制用户表user数据到学生表student

如果在插入student过程中id键值有重复,则更新指定为的值,如:

INSERT INTO `student`(`id`,`name`,`type`) SELECT `id`,`name`,`type` FROM `user` u ON DUPLICATE KEY UPDATE `name`=u.`name`;

复制后:

mysql> select * from student;
+----+------+------+
| id | name | type |
+----+------+------+
|  1 | 张三 |    1 |
|  2 | 王五 |    1 |
+----+------+------+
2 rows in set (0.00 sec)

发现李四跟王五重复键值,则在`name`=u.`name`条件下李四设置为王五。

在ON DUPLICATE KEY UPDATE的值部分中,只要您不使用SELECT部分中的GROUP BY,您就可以引用在其它表中的列。有一个副作用是,您必须使值部分中的非唯一列的名称符合要求。



原创文章,转载请注明出处:https://www.weizhixi.com/user/index/article/id/72.html

分类:
点击(368) 阅读(325) 举报