on duplicate key update用法

If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs.

即MySQL会自动检查要插入的值是否跟表定义的主键或唯一索引有重复，如果没有，就会INSERT，如果有重复，则会更新。

假设存在表：

CREATE TABLE `mapping` (
  `id` int NOT NULL,
  `l1` varchar(10) DEFAULT NULL,
  `l2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `l1` (`l1`,`l2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

以下2句sql的作用是一样。

INSERT INTO mapping VALUES(444,'a','b')
  ON DUPLICATE KEY UPDATE id = values(id);
  
update mapping set id = 444 where `id` = 444 or (`l1` = 'a' and `l2` = b); 

以下两句sql也是作用一样的：

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;

VALUES()函数会返回要插入记录的对应列的值。

In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name) function to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.

总的来说，在有primary key或者unique索引重复值的情况下，ON DUPLICATE KEY UPDATE子句后面指定的列字段就是要更新的值。