MySQL ON DUPLICATE KEY UPDATE影响行数

背景

经常使用 ON DUPLICATE KEY UPDATE 来插入数据或者更新已存在的记录（不推荐，如性能问题），今天联调时发现使用到 MySQL 一个 INSERT ... ON DUPLICATE KEY UPDATE 的语法，明明只更新了两条记录，返回的影响行数竟然是 4，导致判断更新记录数出了问题，把 SQL 拿到 Navicat 上执行也是如此。

原因

很奇怪，于是去网上搜索了一下原因，找到了这么一句说明：

原文链接：https://dev.mysql.com/doc/refman/8.4/en/insert-on-duplicate.html

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. 

也就是说，在 MySQL 中，使用 ON DUPLICATE KEY UPDATE 时，受影响的行数（affected-rows）根据操作类型有所不同：

• 如果插入一行新记录，受影响的行数为 1。
• 如果更新一行现有记录，受影响的行数为 2。
• 如果更新一行现有记录但新值和旧值相同（即记录没有实际改变），受影响的行数为 0。

验证

1、新建一个 users 表

mysql> CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);
Query OK, 0 rows affected (0.03 sec)

2、新增三条记录

INSERT INTO users (id, name, age) VALUES
(1, 'AA', 10),
(2, 'BB', 20),
(3, 'CC', 30)

3、使用 ON DUPLICATE KEY UPDATE 来更新两条记录——有更新

mysql> INSERT INTO users (id, name, age) VALUES
(1, 'Alice', 30),
(2, 'Bob', 35)
ON DUPLICATE KEY UPDATE
name = VALUES(name), age = VALUES(age);
Query OK, 4 rows affected (0.02 sec)
Records: 2  Duplicates: 2  Warnings: 0

由于主键冲突更新了两条记录，且数据有变化，确实返回了 4 rows affected。

4、使用 ON DUPLICATE KEY UPDATE 来更新两条记录——无更新

继续执行上条 SQL 语句：

mysql> INSERT INTO users (id, name, age) VALUES
(1, 'Alice', 30),
(2, 'Bob', 35)
ON DUPLICATE KEY UPDATE
name = VALUES(name), age = VALUES(age);
Query OK, 0 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> 

此时虽然两条记录主键冲突导致更新，但是更新后的旧值和原值一样，受影响行数返回的是 0。

5、使用 ON DUPLICATE KEY UPDATE 来更新并插入新记录

mysql> INSERT INTO users (id, name, age) VALUES
(1, 'AAA', 30),
(4, 'DD', 35)
ON DUPLICATE KEY UPDATE
name = VALUES(name), age = VALUES(age);
Query OK, 3 rows affected (0.02 sec)
Records: 2  Duplicates: 1  Warnings: 0

此时主键为 1 的记录实际更新了数据，影响行数为 2，主键为 4 的记录为新增记录，影响行数为 1，总影响行数为 3，符合官方说明。

总结

ON DUPLICATE KEY UPDATE 写起来虽然方便，但是这个受影响行数是不可用于业务判断的，如批量插入判断插入行数。并且也有一定的性能影响，比单独的更新语句更耗时，建议少用。