1. ON DUPLICATE KEY UPDATE 基础入门第一次听说这个语法时我也觉得挺神奇的。想象一下你正在整理一个通讯录每次收到新名片时如果这个人已经存在就更新信息不存在就新增记录。传统做法是先查询再判断而ON DUPLICATE KEY UPDATE就像个智能助手自动帮你完成这个繁琐过程。这个语法的核心逻辑其实很简单当插入数据时如果发现主键或唯一键冲突就执行更新操作而不是报错。举个实际例子我们有个用户表CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) UNIQUE, email VARCHAR(100), login_count INT DEFAULT 0 );假设我们要处理用户登录数据传统做法需要先查询用户是否存在-- 传统方式需要两条SQL SELECT id FROM users WHERE username 张三; -- 如果存在 UPDATE users SET login_count login_count 1 WHERE id 123; -- 如果不存在 INSERT INTO users(username, email) VALUES(张三, zhangexample.com);而用ON DUPLICATE KEY UPDATE只需要一条SQLINSERT INTO users(username, email) VALUES(张三, zhangexample.com) ON DUPLICATE KEY UPDATE login_count login_count 1;我在实际项目中测试过这种写法性能提升明显。特别是在处理用户行为日志时QPS从原来的200提升到了1500因为减少了网络往返和SQL解析开销。2. 生产环境中的四种典型应用场景2.1 主键冲突时的更新策略主键冲突是最常见的情况。我们有个商品表CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), stock INT, price DECIMAL(10,2) );批量导入商品数据时这样写INSERT INTO products(id, name, stock, price) VALUES (1, iPhone 15, 100, 8999), (2, MacBook Pro, 50, 12999) ON DUPLICATE KEY UPDATE name VALUES(name), stock VALUES(stock), price VALUES(price);这里有个坑我踩过如果只更新部分字段没列出的字段会保持原值。比如上面如果去掉price的更新已有商品的price不会被重置为NULL而是保留原值。2.2 唯一索引冲突的处理方案实际项目中用唯一索引比主键更常见。比如用户邮箱表CREATE TABLE user_emails ( user_id INT, email VARCHAR(100) UNIQUE, is_verified TINYINT(1) );处理用户邮箱变更时INSERT INTO user_emails(user_id, email) VALUES(123, newexample.com) ON DUPLICATE KEY UPDATE user_id VALUES(user_id);这里有个重要特性如果email冲突但user_id不同会更新该邮箱对应的用户ID。我们曾经用这个特性实现了邮箱账号转让功能。2.3 复合唯一键的巧妙运用更复杂的场景是用多个字段组合作为唯一键。比如用户签到表CREATE TABLE user_checkins ( user_id INT, date DATE, count INT, PRIMARY KEY (user_id, date) );每日签到可以这样处理INSERT INTO user_checkins(user_id, date, count) VALUES(123, 2023-06-15, 1) ON DUPLICATE KEY UPDATE count count 1;这种设计既保证了每个用户每天只能签到一次又能累加签到次数。我们在电商项目中用类似方案实现了每日登录奖励功能。2.4 条件更新的高级技巧有时候需要根据业务逻辑决定是否更新INSERT INTO products(id, stock) VALUES(1, 100) ON DUPLICATE KEY UPDATE stock IF(VALUES(stock) stock, VALUES(stock), stock);这个例子实现了只增加不减少的库存更新策略。在秒杀系统中我们用类似方法防止超卖stock IF(stock - VALUES(change) 0, stock - VALUES(change), stock)3. 批量操作的性能优化实战3.1 MyBatis整合方案在Java项目中MyBatis的批量操作可以这样实现insert idbatchUpsert INSERT INTO orders(order_no, user_id, amount) VALUES foreach collectionlist itemitem separator, (#{item.orderNo}, #{item.userId}, #{item.amount}) /foreach ON DUPLICATE KEY UPDATE amount VALUES(amount) /insert注意这里有个性能陷阱当批量数据量很大时比如超过1000条建议分批处理。我们做过测试每批500条左右效率最高。3.2 事务与锁的注意事项高并发下容易遇到死锁问题。建议控制事务粒度不要在单个事务中处理太多记录按照固定顺序更新记录比如按主键排序设置合理的锁等待超时时间SET SESSION innodb_lock_wait_timeout 30; START TRANSACTION; -- 批量操作 COMMIT;3.3 自增ID不连续的解决方案这个特性确实困扰过我们。有几种应对方案使用业务主键而非自增ID调整innodb_autoinc_lock_mode参数如果必须连续可以考虑用单独的表记录ID分配状态4. 生产环境避坑指南4.1 大小写敏感问题MySQL的utf8_general_ci校对规则不区分大小写而utf8_bin区分。我们曾因此出过生产事故-- 不区分大小写 CREATE TABLE users ( username VARCHAR(50) COLLATE utf8_general_ci UNIQUE ); -- 以下两条会被视为相同 INSERT INTO users(username) VALUES(User); INSERT INTO users(username) VALUES(USER); -- 触发更新4.2 唯一键修改的风险修改唯一键字段要特别小心INSERT INTO users(id, username) VALUES(1, new_username) ON DUPLICATE KEY UPDATE username VALUES(username);如果new_username已存在会导致报错。我们现在的做法是先查询确认新值是否冲突。4.3 性能监控建议在生产环境使用时建议监控CPU使用率批量操作可能突然增高锁等待时间慢查询日志可以配置告警规则-- 监控锁等待 SHOW STATUS LIKE innodb_row_lock%;这些经验都是我们在处理日均千万级数据同步任务中积累的。刚开始用这个语法时确实踩了不少坑但掌握后确实能极大提升开发效率。特别是在处理物化视图、缓存同步等场景时它已经成为我们工具箱中的必备利器。
