搞定重复数据痛点！MySQL ON DUPLICATE KEY UPDATE 插入更新一体化方案

MySQL ON DUPLICATE KEY UPDATE 的核心原理

该语法通过判断唯一键（主键或唯一索引）是否冲突，决定执行插入或更新操作。当插入数据时，若唯一键已存在，则触发更新逻辑而非报错。

适用场景分析

高频数据写入且存在重复风险的业务场景，如日志记录、计数器更新、实时数据同步等场景。尤其适合需要保证数据最终一致性的需求。

基础语法示例

INSERT INTO table_name (col1, col2) 
VALUES (val1, val2)
ON DUPLICATE KEY UPDATE col1 = VALUES(col1), col2 = VALUES(col2);

高级用法技巧

使用VALUES()函数引用原插入值，或直接指定新值。支持表达式运算：

ON DUPLICATE KEY UPDATE 
    counter = counter + 1,
    last_update = NOW();

性能优化建议

批量操作效率显著高于单条处理。推荐格式：

INSERT INTO table_name (id, data) 
VALUES (1,'a'), (2,'b'), (3,'c')
ON DUPLICATE KEY UPDATE data = VALUES(data);

事务与锁机制说明

该操作会产生行级锁，建议在事务中处理大批量数据。注意死锁风险，特别是在多个唯一键共存时。

与REPLACE INTO的对比

REPLACE INTO通过先删除再插入实现，会触发DELETE和INSERT触发器。而ON DUPLICATE KEY UPDATE只触发UPDATE触发器，性能更优。

实际应用案例

用户积分累计场景示例：

INSERT INTO user_points (user_id, points)
VALUES (1001, 10)
ON DUPLICATE KEY UPDATE points = points + VALUES(points);

异常处理方案

建议捕获以下错误代码：

• 1062：重复键错误（语法未生效时）
• 1213：事务死锁错误
• 1205：锁等待超时

监控与评估方法

通过SHOW STATUS LIKE 'Handler_write%'监控写入情况，对比Handler_write和Handler_update指标评估操作比例。