mysql insert select的使用

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/article/72.html