1. ON DUPLICATE KEY UPDATE 核心机制解析MySQL中的ON DUPLICATE KEY UPDATE语句是处理存在即更新不存在则插入场景的利器。这个语法糖的精妙之处在于它把原本需要两条SQL先查询后判断的操作压缩成一条原子操作。当执行INSERT时如果触发唯一键冲突MySQL会自动转为执行UPDATE操作。关键点这里的唯一键包括PRIMARY KEY和UNIQUE INDEX当这些约束被违反时就会触发更新逻辑。实际执行流程是这样的先尝试执行标准INSERT操作如果遇到Duplicate entry错误错误码1062自动转换为UPDATE指定字段受影响行数返回21表示插入成功2表示更新成功-- 基础语法示例 INSERT INTO users(id, name, score) VALUES (1, 张三, 90) ON DUPLICATE KEY UPDATE score VALUES(score) 10;这个语句在用户积分更新场景特别实用。假设用户ID是主键当用户不存在时会新建记录如果已存在则给原有积分加10分。相比传统方案避免了先SELECT判断存在与否的额外查询。2. 批量操作性能优化方案当需要处理大量数据时批量操作可以显著提升性能。MySQL支持用单条语句处理多条记录INSERT INTO products(id, stock, price) VALUES (101, 50, 2990), (102, 30, 5990), (103, 100, 1990) ON DUPLICATE KEY UPDATE stock VALUES(stock), price VALUES(price);实测对比10000条数据逐条执行约12秒批量处理约0.8秒事务批量约0.6秒性能提示批量操作时建议每批500-1000条过大的批量可能导致包大小超出max_allowed_packet限制。在MyBatis中的实现方式insert idbatchInsertOrUpdate INSERT INTO inventory(item_id, warehouse, quantity) VALUES foreach collectionlist itemitem separator, (#{item.id}, #{item.wh}, #{item.qty}) /foreach ON DUPLICATE KEY UPDATE quantity VALUES(quantity) /insert3. 高级应用与避坑指南3.1 字段值引用技巧VALUES()函数可以获取原本准备插入的值-- 经典计数器场景 INSERT INTO page_views(url, views) VALUES (/product/123, 1) ON DUPLICATE KEY UPDATE views views 1; -- 使用VALUES引用 INSERT INTO products(id, price, discount_price) VALUES (1001, 599, 499) ON DUPLICATE KEY UPDATE discount_price LEAST(VALUES(price)*0.8, discount_price);3.2 多唯一键处理策略当表有多个唯一键时冲突判断以第一个触发的唯一键为准。假设有UNIQUE(email)和UNIQUE(phone)-- 可能产生意外的场景 INSERT INTO users(email, phone, name) VALUES (atest.com, 13800138000, 李四) ON DUPLICATE KEY UPDATE name VALUES(name);如果email和phone分别对应不同记录MySQL只会处理最先冲突的那个唯一键。这种情况下建议明确指定判断依据WHERE email VALUES(email)或者拆分为两条语句处理3.3 事务与锁注意事项在事务中使用时要注意会获取行级排他锁X锁高并发时可能产生死锁建议控制事务粒度典型死锁场景事务A插入记录1获取锁事务B插入记录2获取锁事务A尝试插入记录2等待事务B尝试插入记录1死锁解决方案按固定顺序处理记录减小事务范围添加重试机制4. 生产环境实战案例4.1 电商库存管理实时库存更新是典型应用场景INSERT INTO product_inventory (product_id, sku_id, stock, modified_time) VALUES (P1001, S2001, 100, NOW()), (P1002, S2002, 50, NOW()) ON DUPLICATE KEY UPDATE stock stock VALUES(stock), modified_time NOW();重要细节这里用stock VALUES(stock)实现增量更新而非直接覆盖符合库存业务逻辑。4.2 用户行为统计用户行为去重统计方案INSERT INTO user_actions (user_id, action_date, action_type, count) VALUES (123, CURDATE(), click, 1), (123, CURDATE(), view, 1) ON DUPLICATE KEY UPDATE count count 1;配合复合唯一键ALTER TABLE user_actions ADD UNIQUE KEY uk_user_action (user_id, action_date, action_type);4.3 与MyBatis Plus集成使用MyBatis Plus的Wrapper条件构造器public void batchInsertOrUpdate(ListUser users) { String sql INSERT INTO user(id, name, age) VALUES users.stream() .map(u - String.format((%d, %s, %d), u.getId(), u.getName(), u.getAge())) .collect(Collectors.joining(,)) ON DUPLICATE KEY UPDATE name VALUES(name), age VALUES(age); jdbcTemplate.execute(sql); }5. 性能对比与替代方案5.1 REPLACE INTO的陷阱REPLACE INTO看似功能相似但实际是删除后重新插入会触发DELETE和INSERT两个操作自增ID会变化所有字段都会被覆盖未指定字段置为默认值-- 危险示例 REPLACE INTO users(id, name) VALUES (1, 张三); -- 如果原记录有email字段执行后email会被置为NULL5.2 INSERT IGNORE的局限INSERT IGNORE在冲突时静默跳过不报错但也不更新只能处理存在则跳过的场景无法知道最终是插入还是跳过5.3 存储过程方案对于复杂逻辑可以考虑存储过程DELIMITER // CREATE PROCEDURE upsert_user( IN p_id INT, IN p_name VARCHAR(50), IN p_score INT ) BEGIN INSERT INTO users(id, name, score) VALUES (p_id, p_name, p_score) ON DUPLICATE KEY UPDATE name IF(VALUES(name) ! , VALUES(name), name), score IF(VALUES(score) score, VALUES(score), score); END // DELIMITER ;这个存储过程实现了名字不为空时更新只更新更大的分数6. 监控与问题排查6.1 执行结果判断通过JDBC获取影响行数1表示插入了新行2表示更新了已有行0表示更新前后数据完全一致Spring JdbcTemplate示例int rows jdbcTemplate.update(sql); if(rows 1) { log.info(新记录插入); } else if(rows 2) { log.info(已有记录更新); }6.2 常见错误处理错误代码1062唯一键冲突但未指定UPDATE-- 错误写法缺少UPDATE部分 INSERT INTO test VALUES (1) ON DUPLICATE KEY UPDATE;错误代码1136列数不匹配-- 值数量与列数不匹配 INSERT INTO test(a,b) VALUES (1) ON DUPLICATE KEY UPDATE a 2;6.3 慢查询优化当批量操作变慢时检查唯一索引是否合理批量大小是否合适是否缺少合适的复合索引可以通过EXPLAIN分析EXPLAIN INSERT INTO ... ON DUPLICATE KEY UPDATE ...;关注以下指标type: 显示ALL表示全表扫描key: 显示使用的索引rows: 预估检查的行数