INSERT ON DUPLICATE KEY UPDATE

语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
  {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
  [ ON DUPLICATE KEY UPDATE
    col=expr
      [, col=expr] ... ]

或：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)]
    SET col={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] ... ]

或：

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] ... ]

描述

INSERT ... ON DUPLICATE KEY UPDATE是MariaDB/MySQL对INSERT语句的扩展，当插入数据的时候发现有重复值冲突，将对原记录执行UPDATE操作来替代INSERT操作。

当插入数据的时候没有执行UPDATE而是单纯地插入数据，受影响的行数为1，如果执行了UPDATE操作，受影响的行数为2，除非设置了API的CLIENT_FOUND_ROWS标记。

如果有多个unique索引被匹配，则只有第一个会被更新。因此，对于有多个唯一索引的表，不建议使用该语句来插入数据。

如果表具有AUTO_INCREMENT的主键列，且插入了或更新了一行，则LAST_INSERT_ID()函数将返回它的AUTO_INCREMEN值。

VALUES()函数只能应用在ON DUPLICATE KEY UPDATE子句中，在其他格式的INSERT语句中该函数无意义。它会返回INSERT语句中列对应的值。该函数在多行插入的环境中非常有用。

如果使用了 ON DUPLICATE KEY UPDATE子句，则IGNORE和DELAYED选项会被忽略。

该语句会激活INSERT和UPDATE触发器。详细信息见Trigger Overview。

还有一个与之类似的语句REPLACE。

示例

CREATE TABLE ins_duplicate (id INT PRIMARY KEY, animal VARCHAR(30));
INSERT INTO ins_duplicate VALUES (1,'Aardvark'), (2,'Cheetah'), (3,'Zebra');

由于没有键值重复冲突，下面的语句将以普通的INSERT操作插入数据：

INSERT INTO ins_duplicate VALUES (4,'Gorilla') ON DUPLICATE KEY UPDATE animal='Gorilla';
Query OK, 1 row affected (0.07 sec)

SELECT * FROM ins_duplicate;
+----+----------+
| id | animal   |
+----+----------+
|  1 | Aardvark |
|  2 | Cheetah  |
|  3 | Zebra    |
|  4 | Gorilla  |
+----+----------+

由于键值重复，下面的INSERT语句插入失败：

INSERT INTO ins_duplicate VALUES (1,'Antelope');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

此时，可以使用INSERT ON DUPLICATE KEY UPDATE来更新重复值：

INSERT INTO ins_duplicate VALUES (1,'Antelope') ON DUPLICATE KEY UPDATE animal='Antelope';
Query OK, 2 rows affected (0.09 sec)

注意，上面的语句将会报告受影响的行数为2，这是UPDATE的行为。

SELECT * FROM ins_duplicate;
+----+----------+
| id | animal   |
+----+----------+
|  1 | Antelope |
|  2 | Cheetah  |
|  3 | Zebra    |
|  4 | Gorilla  |
+----+----------+

添加一个新的具有唯一性的字段：

ALTER TABLE ins_duplicate ADD id2 INT;
UPDATE ins_duplicate SET id2=id+10;
ALTER TABLE ins_duplicate ADD UNIQUE KEY(id2);

由于匹配了两个唯一键，因此只有第一个被匹配的键被更新。这是不安全的行为，因此不推荐在多个唯一键的表上执行INSERT ON DUPLICATE KEY UPDATE语句，除非你直到自己在做什么。注意，下面的警告只在MariaDB 5.5之前才会显示，在MariaDB 10.0中已经移除了，因为MariaDB假定按照顺序去检查各个键，顺序可由SHOW CREATE TABLE获取。

INSERT INTO ins_duplicate VALUES (2,'Lion',13) ON DUPLICATE KEY UPDATE animal='Lion';
Query OK, 2 rows affected, 1 warning (0.06 sec)

SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                  |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SELECT * FROM ins_duplicate;
+----+----------+------+
| id | animal   | id2  |
+----+----------+------+
|  1 | Antelope |   11 |
|  2 | Lion     |   12 |
|  3 | Zebra    |   13 |
|  4 | Gorilla  |   14 |
+----+----------+------+

尽管第三行id=3对应的id2=13也被INSERT语句匹配到了，但却没有去更新该行的记录。

将id字段更改为自增字段auto_increment。如果insert插入了一个新行，auto_increment的只将向前移动。如果更新了已存在的行，则auto_increment保留原有的值。

ALTER TABLE `ins_duplicate` CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE ins_duplicate DROP id2;
SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
|              5 |
+----------------+

INSERT INTO ins_duplicate VALUES (2,'Leopard') ON DUPLICATE KEY UPDATE animal='Leopard';
Query OK, 2 rows affected (0.00 sec)

SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
|              5 |
+----------------+

INSERT INTO ins_duplicate VALUES (5,'Wild Dog') ON DUPLICATE KEY UPDATE animal='Wild Dog';
Query OK, 1 row affected (0.09 sec)

SELECT * FROM ins_duplicate;
+----+----------+
| id | animal   |
+----+----------+
|  1 | Antelope |
|  2 | Leopard  |
|  3 | Zebra    |
|  4 | Gorilla  |
|  5 | Wild Dog |
+----+----------+

SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
|              6 |
+----------------+

从INSERT语句中引用字段的值来更新数据：

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
    ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

更多内容参见VALUES()函数。

See Also

• INSERT
• INSERT DELAYED
• INSERT SELECT
• HIGH_PRIORITY and LOW_PRIORITY
• Concurrent Inserts
• INSERT - Default & Duplicate Values
• INSERT IGNORE
• VALUES()