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

本文章主要讲解INSERT ... SELECTINSERT ... ON DUPLICATE KEY UPDATE这种比较特殊的数据操作(data manipulation)的SQL。

INSERT … SELECT语句
使用该语句可以通过将select语句得到的结果快速插入到一个表中,该select语句可以查询一个或者多个表,比如:

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_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;

(在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;

《Mysql不常见的插入/更新SQL语句》有2个想法

  1. 今天面试被问道“如果存在则更新,如果不存在则插入”的问题,没回答上来,回家百度搜索到您的文章,写的很详细,学习了,谢谢!

发表评论

电子邮件地址不会被公开。 必填项已用*标注