MySQL 的 INSERT ··· ON DUPLICATE KEY UPDATE

在MySQL数据库中，如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句，而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果插入的行数据与现有表中记录的唯一索引或者主键不重复，则执行新纪录插入操作。另外，ON DUPLICATE KEY UPDATE不能写where条件。

示例：

CREATE TABLE kid_score (
    id TINYINT UNSIGNED NOT NULL,
    birth_day date NOT NULL,
    score INT UNSIGNED NOT NULL,
    PRIMARY KEY (id, birth_day) -- 唯一索引是由 id + birth_day 两个字段组成
) ENGINE = INNODB;

-- 初始化数据 
-- 初始化数据 
INSERT INTO kid_score (id, birth_day, score) VALUES (1, '2019-01-15', 10),(2, '2019-01-16', 20);

下面开始验证执行INSERT ··· ON DUPLICATE KEY UPDATE语法的规则：如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执行insert语句而不是更新语句。

 1. 唯一索引重复

INSERT INTO kid_score (id, birth_day, score) VALUES (1,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;

2. 唯一索引不重复

insert into kid_score(id, birth_day, score) values (2,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;

3. 唯一索引重复，插入完全相同数据

insert into kid_score(id, birth_day, score) values (2,'2019-01-16',20) ON DUPLICATE KEY UPDATE score = 20;

4. 影响行数

mysql> select * from kid_score;
+----+------------+-------+
| id | birth_day  | score |
+----+------------+-------+
|  1 | 2019-01-15 |    10 |
|  2 | 2019-01-16 |    20 |
+----+------------+-------+
2 rows in set

-- 唯一索引重复，执行更新
mysql> insert into kid_score(id, birth_day, score) values (1,‘2019-01-15‘,30) ON DUPLICATE KEY UPDATE score = score + 50;
Query OK, 2 rows affected

mysql> select * from kid_score;
+----+------------+-------+
| id | birth_day  | score |
+----+------------+-------+
|  1 | 2019-01-15 |    60 |
|  2 | 2019-01-16 |    20 |
+----+------------+-------+
2 rows in set

-- 唯一索引不重复，执行插入
mysql> insert into kid_score(id, birth_day, score) values (2,‘2019-01-15‘,30) ON DUPLICATE KEY UPDATE score = score + 50;

Query OK, 1 row affected

mysql> select * from kid_score;
+----+------------+-------+
| id | birth_day  | score |
+----+------------+-------+
|  1 | 2019-01-15 |    60 |
|  2 | 2019-01-15 |    30 |
|  2 | 2019-01-16 |    20 |
+----+------------+-------+
3 rows in set

-- 唯一索引重复，应该执行更新，但更新值与原值相同
mysql> insert into kid_score(id, birth_day, score) values (2,‘2019-01-16‘,20) ON DUPLICATE KEY UPDATE score = 20;

Query OK, 0 rows affected

mysql> select * from kid_score;
+----+------------+-------+
| id | birth_day  | score |
+----+------------+-------+
|  1 | 2019-01-15 |    60 |
|  2 | 2019-01-15 |    30 |
|  2 | 2019-01-16 |    20 |
+----+------------+-------+
3 rows in set

 如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。例如，如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果：

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;  
mysql>UPDATE table SET c=c+1 WHERE a=1;

如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。

注释：如果列b也是唯一列，则INSERT与此UPDATE语句相当：

mysql>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

如果a=1 OR b=2与多个行向匹配，则只有一个行被更新。通常，您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。

您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT...UPDATE语句的INSERT部分引用列值。

换句话说，如果没有发生重复关键字冲突，则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT...UPDATE语句中有意义，其它时候会返回NULL

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

本语句与以下两个语句作用相同：

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

当您使用ON DUPLICATE KEY UPDATE时，DELAYED选项被忽略。