INSERT ... ON DUPLICATE KEY UPDATE

原文链接:http://www.dubby.cn/detail.html?id=9075

如果在insert语句后加上ON DUPLICATE KEY UPDATE子句，那么如果这个insert语句因为UNIQUE或者PRIMARY KEY冲突而插入失败时，会执行这个子句来执行更新操作。例如，如果a上有唯一键约束，并且有一列的值是1，那么下面两句SQL是等价的:

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;

但是，如果这个插入操作同时违反了不止一个唯一键约束，那会update哪一行呢？

假设，在b上也有一个唯一键，那么上面那句SQL等价于:

UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

没错，只会更新一行，但是是哪一行呢，我们无法确定。所以，我们应该尽量避免使用ON DUPLICATE KEY UPDATE来触发多行更新。

如果我们关心操作的影响行数，应该怎么办呢？

在使用ON DUPLICATE KEY UPDATE时，如果执行了insert操作，影响行数就会返回1；如果执行了update，影响行数会返回2；如果执行了update，但是把原来的值set给自己，就会返回0（也就是并没有执行更新操作）。

但是，如果你使用C语言的mysql_real_connect()来操作时，指定了CLIENT_FOUND_ROWS，此时执行了update，把原来的值set给自己，会返回1，而不是0。

如果是插入多条记录呢？

我们在ON DUPLICATE KEY UPDATE子句里使用VALUES(col_name)来指定这一行的对应列的值，举个例子:

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;

甚至，还可以使用INSERT ... SELECT

但是select从句不能使用group by，而且你一定要可以引用非唯一约束的列名。

下面这段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;