Mysql不常见的插入/更新SQL语句

本文章主要讲解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;