MySQL ON DUPLICATE KEY UPDATE：高效实现数据‘有则更新，无则插入’-编程实验室

1. 什么是ON DUPLICATE KEY UPDATE

如果你经常和MySQL打交道，一定遇到过这样的场景：需要往数据库插入一条记录，但如果这条记录已经存在，就更新它的某些字段。传统做法是先查询是否存在，再决定是执行INSERT还是UPDATE，这样不仅代码冗余，还增加了数据库的访问次数。

MySQL提供了一个非常实用的语法ON DUPLICATE KEY UPDATE，它能自动判断记录是否存在，存在就更新，不存在就插入。这个功能在业内有个专业术语叫"upsert"（update + insert的合成词）。我第一次接触这个功能是在处理用户签到系统时，当时需要记录用户签到次数，如果用户第一次签到就新增记录，后续签到就在原有次数上+1，用这个语法完美解决了问题。

2. 工作原理深度解析

2.1 核心判断机制

ON DUPLICATE KEY UPDATE的核心判断依据是表的主键(Primary Key)或唯一索引(Unique Index)。当执行INSERT语句时，MySQL会检查要插入的数据是否与已有记录的主键或唯一索引冲突。如果冲突，就执行UPDATE部分；如果不冲突，就正常插入新记录。

举个例子，我们有个用户表：

CREATE TABLE `users` ( `user_id` INT NOT NULL, `username` VARCHAR(50), `login_count` INT DEFAULT 0, PRIMARY KEY (`user_id`), UNIQUE KEY `idx_username` (`username`) );

当我们执行：

INSERT INTO users (user_id, username, login_count) VALUES (1, '张三', 1) ON DUPLICATE KEY UPDATE login_count = login_count + 1;

MySQL会先检查user_id=1或username='张三'是否已存在，存在就执行login_count+1，不存在就插入新记录。

2.2 影响行数的秘密

这个语句的执行结果会返回一个特殊的影响行数：

如果是新增记录，返回1
如果是更新记录，返回2
如果更新但数据实际上没变化，返回0

这个特性在实际开发中很有用。比如在做数据同步时，可以通过返回值知道是新增了数据还是更新了已有数据。我在做电商库存系统时，就利用这个特性来区分是新增商品还是更新库存。

3. 实际应用场景

3.1 用户行为统计

最常见的场景就是各种计数统计。比如文章阅读量、用户签到次数、商品浏览次数等。以前我们可能需要先查询再决定更新还是插入，现在一条语句就能搞定：

-- 用户签到 INSERT INTO user_sign (user_id, sign_date, sign_count) VALUES (123, '2023-06-15', 1) ON DUPLICATE KEY UPDATE sign_count = sign_count + 1; -- 文章阅读量 INSERT INTO article_stats (article_id, view_count) VALUES (456, 1) ON DUPLICATE KEY UPDATE view_count = view_count + 1;

3.2 配置项管理

系统配置项也经常用到这个功能。配置项通常有唯一键，如果不存在需要初始化，存在则需要更新：

INSERT INTO system_config (config_key, config_value, update_time) VALUES ('site_title', '我的网站', NOW()) ON DUPLICATE KEY UPDATE config_value = VALUES(config_value), update_time = NOW();

这里用了VALUES(config_value)的写法，可以直接引用INSERT部分的值，避免重复书写。

3.3 数据同步与合并

在做数据同步时，经常需要合并来自不同源的数据。比如从Excel导入数据到数据库，有些是新增的，有些需要更新：

INSERT INTO products (sku, name, price, stock) VALUES ('SKU001', '商品A', 99.9, 100), ('SKU002', '商品B', 199.9, 50), ('SKU003', '商品C', 299.9, 20) ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price), stock = VALUES(stock);

4. 高级用法与注意事项

4.1 多唯一键情况处理

当表有多个唯一键时，只要违反任何一个唯一键约束都会触发UPDATE。这有时会导致意想不到的结果。比如用户表同时以user_id和username作为唯一键：

INSERT INTO users (user_id, username, email) VALUES (1, '张三', 'zhangsan@example.com') ON DUPLICATE KEY UPDATE email = VALUES(email);

如果已存在user_id=1但username='李四'的记录，或者username='张三'但user_id=2的记录，都会触发更新。这点需要特别注意。

4.2 性能优化建议

虽然ON DUPLICATE KEY UPDATE很方便，但大量使用可能影响性能：

对高并发的计数场景，可以考虑先用内存计数再批量更新
大批量操作时，使用批量INSERT...ON DUPLICATE KEY UPDATE比单条执行效率高很多
确保相关字段有合适的索引，但也不要过度索引

4.3 事务与锁的考量

这个语句在执行时会获取行锁，在高并发环境下可能导致锁竞争。我曾经遇到过一个案例：用户签到系统在高峰期出现大量锁等待，后来我们改为使用Redis先记录签到，再用定时任务批量更新到MySQL，解决了性能问题。

5. 与其他方案的对比

5.1 REPLACE INTO

REPLACE INTO也能实现类似功能，但它是先删除旧记录再插入新记录，会导致自增ID变化和触发器执行，使用时需要注意这些差异。

5.2 INSERT IGNORE

INSERT IGNORE在遇到重复时会忽略错误，但不会更新数据。适合"有则跳过，无则插入"的场景。

5.3 存储过程方案

有些开发者喜欢用存储过程实现upsert逻辑，虽然更灵活但维护成本高，性能也不一定更好。

在实际项目中，我通常会根据具体场景选择最合适的方案。对于简单的upsert需求，ON DUPLICATE KEY UPDATE通常是最简洁高效的选择。

MySQL ON DUPLICATE KEY UPDATE：高效实现数据‘有则更新，无则插入’