MySQL INSERT ... ON DUPLICATE KEY UPDATE 批量更新详解
一、引言
在数据库操作中，我们经常需要处理"存在则更新，不存在则插入"的场景。MySQL 提供了INSERT ... ON DUPLICATE KEY UPDATE语句来高效实现这一需求，特别是在批量操作时，其性能优势更为明显。
二、基本语法与原理
基本语法
INSERTINTOtable_name(column1,column2,...)VALUES(value1,value2,...),(value1,value2,...),...ONDUPLICATEKEYUPDATEcolumn1=VALUES(column1),column2=VALUES(column2),...;工作原理
- 尝试批量插入所有提供的行
- 如果遇到主键或唯一键冲突：
- 执行更新操作，使用 VALUES() 函数引用原本要插入的值
- 不会删除原有记录，直接在原记录上更新
- 如果没有冲突：
- 正常插入所有新记录
三、批量更新优势
1. 性能对比
| 操作方式 | 网络往返次数 | 执行效率 | 自增ID影响 | 触发器 |
|---|---|---|---|---|
| 单独INSERT+UPDATE | 高 | 低 | 可能改变 | DELETE+INSERT触发器 |
| ON DUPLICATE KEY UPDATE | 低 | 高 | 保持不变 | UPDATE触发器 |
| REPLACE INTO | 低 | 中 | 会改变 | DELETE+INSERT触发器 |
2. 批量操作示例
-- 批量插入/更新5条记录INSERTINTOproducts(id,name,price,stock,update_time)VALUES(1,'Product A',19.99,100,NOW()),(2,'Product B',29.99,50,NOW()),(3,'Product C',39.99,75,NOW()),(4,'Product D',49.99,200,NOW()),(5,'Product E',59.99,30,NOW())ONDUPLICATEKEYUPDATEprice=VALUES(price),stock=VALUES(stock),update_time=NOW();四、高级用法
1. 基于条件的更新
-- 只有当新价格比旧价格低时才更新INSERTINTOproducts(id,name,price,stock)VALUES(1,'Product A',18.99,100)ONDUPLICATEKEYUPDATEprice=IF(VALUES(price)<price,VALUES(price),price),stock=VALUES(stock);2. 增量更新
-- 库存增量更新INSERTINTOproducts(id,stock_change)VALUES(1,10),(2,-5),(3,20)ONDUPLICATEKEYUPDATEstock=stock+VALUES(stock_change);3. 多表关联更新（使用JOIN模拟）
-- 先创建临时表或使用多值INSERTINSERTINTOproduct_updates(product_id,price_change,stock_change)VALUES(1,0,10),(2,2.5,0),(3,-1.0,5);-- 然后执行批量更新INSERTINTOproducts(id,price,stock)SELECTpu.product_id,p.price+IFNULL(pu.price_change,0),p.stock+IFNULL(pu.stock_change,0)FROMproduct_updates puLEFTJOINproducts pONpu.product_id=p.idONDUPLICATEKEYUPDATEprice=VALUES(price),stock=VALUES(stock);五、实际应用场景
1. 数据同步与ETL
-- 从数据仓库同步到OLTP系统INSERTINTOdw_products(product_id,product_name,category,price)SELECTid,name,category,priceFROMstaging_productsONDUPLICATEKEYUPDATEproduct_name=VALUES(product_name),category=VALUES(category),price=VALUES(price),sync_time=NOW();2. 计数器表更新
-- 批量更新用户行为计数器INSERTINTOuser_metrics(user_id,metric_date,logins,purchases)VALUES(1001,'2023-05-20',1,0),(1002,'2023-05-20',1,1),(1003,'2023-05-20',0,1)ONDUPLICATEKEYUPDATElogins=logins+VALUES(logins),purchases=purchases+VALUES(purchases);3. 缓存表维护
-- 批量更新缓存表INSERTINTOcache_user_profiles(user_id,username,last_active,data_version)SELECTid,username,last_login_time,2FROMusersWHEREstatus='active'ONDUPLICATEKEYUPDATEusername=VALUES(username),last_active=VALUES(last_active),data_version=VALUES(data_version);六、性能优化技巧
1. 批量大小控制
# Python示例：分批处理大数据量defbatch_upsert(connection,table,data,batch_size=1000):foriinrange(0,len(data),batch_size):batch=data[i:i+batch_size]placeholders=", ".join(["(%s, %s, %s, %s)"]*len(batch))values=[itemforsublistinbatchforiteminsublist]sql=f""" INSERT INTO{table}(id, col1, col2, col3) VALUES{placeholders}ON DUPLICATE KEY UPDATE col1 = VALUES(col1), col2 = VALUES(col2), col3 = VALUES(col3) """withconnection.cursor()ascursor:cursor.execute(sql,values)connection.commit()2. 索引优化
确保用于检测重复的键（主键或唯一键）有适当的索引：
-- 为频繁用于冲突检测的列添加索引ALTERTABLEordersADDUNIQUEINDEXidx_order_no(order_no);3. 事务处理
-- 使用事务确保批量操作的原子性STARTTRANSACTION;INSERTINTOlarge_table(id,col1,col2)VALUES(1,'A','B'),(2,'C','D'),...-- 大量数据ONDUPLICATEKEYUPDATEcol1=VALUES(col1),col2=VALUES(col2);-- 只有在所有行都处理成功后才提交COMMIT;七、常见问题与解决方案
1. 如何获取受影响的行数？
# Python示例：获取实际插入/更新的行数cursor=connection.cursor()cursor.execute(upsert_sql,params)affected_rows=cursor.rowcount# 注意：在批量操作中，rowcount返回的是总影响行数# 实际插入的行数 = affected_rows - (更新的行数*2)2. 如何知道哪些行是插入的，哪些是更新的？
-- MySQL 8.0+ 可以使用ROW_COUNT()和LAST_INSERT_ID()INSERTINTO...ONDUPLICATEKEYUPDATE...;SELECTROW_COUNT();-- 返回-1表示所有行都是更新，正数表示插入的行数3. 与REPLACE INTO的性能对比
| 指标 | INSERT ON DUPLICATE KEY UPDATE | REPLACE INTO |
|---|---|---|
| 操作类型 | 直接更新 | 删除后插入 |
| 自增ID | 保持不变 | 可能改变 |
| 触发器 | UPDATE触发器 | DELETE+INSERT触发器 |
| 批量性能 | 优秀 | 良好 |
| 原子性 | 是 | 是 |
八、最佳实践
明确业务需求：
- 需要部分更新 → 使用 ON DUPLICATE KEY UPDATE
- 需要完全替换 → 使用 REPLACE INTO
- 需要忽略冲突 → 使用 INSERT IGNORE
批量大小选择：
- 通常100-1000行/批是合理的
- 测试不同批量大小以找到最佳值
错误处理：
try:batch_upsert(connection,"products",data_list)exceptExceptionase:# 记录错误并考虑重试机制logger.error(f"Batch upsert failed:{str(e)}")# 可能需要拆分批次重试监控性能：
-- 检查慢查询日志SETGLOBALslow_query_log='ON';SETGLOBALlong_query_time=1;-- 秒
九、总结
INSERT ... ON DUPLICATE KEY UPDATE是MySQL中处理"存在则更新，不存在则插入"场景的高效解决方案，特别是在批量操作时表现出色。通过合理使用这一语句，可以：
- 减少数据库往返次数
- 保持自增ID的稳定性
- 简化应用逻辑（无需先查询再决定插入或更新）
- 在事务中保证操作的原子性
在实际应用中，应根据业务需求选择合适的批量大小，添加适当的错误处理和监控，以充分发挥这一特性的优势。