1. 什么是ON DUPLICATE KEY UPDATE如果你经常和MySQL打交道一定遇到过这样的场景需要往数据库插入一条记录但如果这条记录已经存在就更新它的某些字段。传统做法是先查询是否存在再决定是执行INSERT还是UPDATE这样不仅代码冗余还增加了数据库的访问次数。MySQL提供了一个非常实用的语法ON DUPLICATE KEY UPDATE它能自动判断记录是否存在存在就更新不存在就插入。这个功能在业内有个专业术语叫upsertupdate 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_id1或username张三是否已存在存在就执行login_count1不存在就插入新记录。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, 张三, zhangsanexample.com) ON DUPLICATE KEY UPDATE email VALUES(email);如果已存在user_id1但username李四的记录或者username张三但user_id2的记录都会触发更新。这点需要特别注意。4.2 性能优化建议虽然ON DUPLICATE KEY UPDATE很方便但大量使用可能影响性能对高并发的计数场景可以考虑先用内存计数再批量更新大批量操作时使用批量INSERT...ON DUPLICATE KEY UPDATE比单条执行效率高很多确保相关字段有合适的索引但也不要过度索引4.3 事务与锁的考量这个语句在执行时会获取行锁在高并发环境下可能导致锁竞争。我曾经遇到过一个案例用户签到系统在高峰期出现大量锁等待后来我们改为使用Redis先记录签到再用定时任务批量更新到MySQL解决了性能问题。5. 与其他方案的对比5.1 REPLACE INTOREPLACE INTO也能实现类似功能但它是先删除旧记录再插入新记录会导致自增ID变化和触发器执行使用时需要注意这些差异。5.2 INSERT IGNOREINSERT IGNORE在遇到重复时会忽略错误但不会更新数据。适合有则跳过无则插入的场景。5.3 存储过程方案有些开发者喜欢用存储过程实现upsert逻辑虽然更灵活但维护成本高性能也不一定更好。在实际项目中我通常会根据具体场景选择最合适的方案。对于简单的upsert需求ON DUPLICATE KEY UPDATE通常是最简洁高效的选择。