MySQL INSERT ... ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE，当插入的记录会引发主键冲突或者违反唯一约束时，则使用UPDATE更新旧的记录，否则插入新记录。

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uid   | int(11)     | NO   | PRI |         |       |
| uname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from test;
+-----+--------+
| uid | uname  |
+-----+--------+
|   1 | uname1 |
|   2 | uname2 |
|   3 | me     |
+-----+--------+
3 rows in set (0.00 sec)

mysql> INSERT INTO test values ( 3,'insertName' )
    -> ON DUPLICATE KEY UPDATE uname='updateName';
Query OK, 2 rows affected (0.03 sec)

mysql> select * from test;+-----+------------+
| uid | uname      |
+-----+------------+
|   1 | uname1     |
|   2 | uname2     |
|   3 | updateName |
+-----+------------+
3 rows in set (0.00 sec)

mysql> create index i_test_uname on test(uname);
Query OK, 3 rows affected (0.20 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test VALUES ( 1 , 'uname2')    -> ON DUPLICATE KEY UPDATE uname='update2records';Query OK, 2 rows affected (0.00 sec)

mysql> select * from test;+-----+----------------+
| uid | uname          |
+-----+----------------+
|   2 | uname2         |
|   1 | update2records |
|   3 | updateName     |
+-----+----------------+
3 rows in set (0.00 sec)

插入时会与两条记录发生冲突，分别由主键和唯一索引引起。但最终只UPDATE了其中一条。这在手册中也说明了，有多个唯一索引（或者有键也有唯一索引）的情况下，不建议使用该语句。