mysql insert select的使用
阅读(1719)
2018-02-08
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