本文章主要讲解INSERT ... SELECT
和INSERT ... ON DUPLICATE KEY UPDATE
这种比较特殊的数据操作(data manipulation)的SQL。
INSERT INTO … SELECT语句
新建两个测试表,并加入数据:
CREATE TABLE `test2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`content` varchar(255) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`content` varchar(255) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
使用该语句可以将select语句得到的结果快速插入到一个表中
insert into test (content, title) select content, title from test where test.id = 1;
该select语句可以查询其他的表,比如:
INSERT INTO table (id) SELECT table2.id FROM table2 WHERE table2.id > 100;
INSERT … ON DUPLICATE KEY UPDATE语句
使用该语句,如果在insert的列中有一列有唯一索引或者是主键列,且该列中即将insert的值在表中已经存在,那么将会执行该语句中的update操作。
比如,如果列a被声明为UNIQUE
,并且表中已经有列a的值为“1”的行,则以下两条语句执行效果相同:
INSERT INTO t1 (a, b, c) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = c + 1;
UPDATE t1 SET c = c + 1 WHERE a = 1;
如果不存在,那么会执行insert。
(在InnoDB表中,如果a是自动递增的列,则以上两者不尽相同,因为insert语句会增加auto-increment的值,但是update不会)
在上述场景下,如果b列也是唯一的,那么第一条语句将等同于:
UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2
匹配到多行,那么也只会更新一行,所以你应当避免在一个表含有多个唯一索引时使用该语句。
在组合索引上使用的情况:如果存在索引为UNIQUE(a, b)
,那么上面的语句效果如下:
UPDATE table SET c = c + 1 WHERE a = 1 AND b = 2;
特殊的VALUES()
函数,该函数只能在INSERT ... ON DUPLICATE KEY UPDATE
中使用,详情如下:
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c= VALUES(a) + VALUES(b);
其含义为:
INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=9;
以下SQL语句将会报错:
INSERT INTO t1 (a, b) SELECT c, d FROM t2 UNION SELECT e, f FROM t3 ON DUPLICATE KEY UPDATE b = b + c;
正确应该为:
INSERT INTO t1 (a, b)
SELECT * FROM
(SELECT c, d FROM t2
UNION
SELECT e, f FROM t3) AS dt
ON DUPLICATE KEY UPDATE b = b + c;
今天面试被问道“如果存在则更新,如果不存在则插入”的问题,没回答上来,回家百度搜索到您的文章,写的很详细,学习了,谢谢!
多谢支持,O(∩_∩)O~~