核心内容摘要
仙儿骚麦秽语喊麦dj版全部歌词
引言在数据库操作中我们经常需要处理存在则更新不存在则插入的场景。
MySQL 提供了INSERT ... ON DUPLICATE KEY UPDATE语句来高效实现这一需求特别是在批量操作时其性能优势更为明显。
基本语法与原理基本语法INSERTINTOtable_name(column1,column2,...)VALUES(value1,value2,...),(value1,value2,...),...ONDUPLICATEKEYUPDATEcolumn1VALUES(column
,column2VALUES(column
,...;工作原理尝试批量插入所有提供的行如果遇到主键或唯一键冲突执行更新操作使用 VALUES() 函数引用原本要插入的值不会删除原有记录直接在原记录上更新如果没有冲突正常插入所有新记录
批量更新优势
性能对比操作方式网络往返次数执行效率自增ID影响触发器单独INSERTUPDATE高低可能改变DELETEINSERT触发器ON DUPLICATE KEY UPDATE低高保持不变UPDATE触发器REPLACE INTO低中会改变DELETEINSERT触发器
批量操作示例-- 批量插入/更新5条记录INSERTINTOproducts(id,name,price,stock,update_time)VALUES(1,Product A,
1
99,100,NOW()),(2,Product B,
2
99,50,NOW()),(3,Product C,
3
99,75,NOW()),(4,Product D,
4
99,200,NOW()),(5,Product E,
5
99,30,NOW())ONDUPLICATEKEYUPDATEpriceVALUES(price),stockVALUES(stock),update_timeNOW();
高级用法
基于条件的更新-- 只有当新价格比旧价格低时才更新INSERTINTOproducts(id,name,price,stock)VALUES(1,Product A,
1
99,
ONDUPLICATEKEYUPDATEpriceIF(VALUES(price)price,VALUES(price),price),stockVALUES(stock);
增量更新-- 库存增量更新INSERTINTOproducts(id,stock_change)VALUES(1,
,(2,-
,(3,
ONDUPLICATEKEYUPDATEstockstockVALUES(stock_change);
多表关联更新使用JOIN模拟-- 先创建临时表或使用多值INSERTINSERTINTOproduct_updates(product_id,price_change,stock_change)VALUES(1,0,
,(2,
5,
,(3,-
0,
;-- 然后执行批量更新INSERTINTOproducts(id,price,stock)SELECTpu.product_id,p.priceIFNULL(pu.price_change,
,p.stockIFNULL(pu.stock_change,
FROMproduct_updates puLEFTJOINproducts pONpu.product_idp.idONDUPLICATEKEYUPDATEpriceVALUES(price),stockVALUES(stock);
实际应用场景
数据同步与ETL-- 从数据仓库同步到OLTP系统INSERTINTOdw_products(product_id,product_name,category,price)SELECTid,name,category,priceFROMstaging_productsONDUPLICATEKEYUPDATEproduct_nameVALUES(product_name),categoryVALUES(category),priceVALUES(price),sync_timeNOW();
计数器表更新-- 批量更新用户行为计数器INSERTINTOuser_metrics(user_id,metric_date,logins,purchases)VALUES(1001,
,1,
,(1002,
,1,
,(1003,
,0,
ONDUPLICATEKEYUPDATEloginsloginsVALUES(logins),purchasespurchasesVALUES(purchases);
缓存表维护-- 批量更新缓存表INSERTINTOcache_user_profiles(user_id,username,last_active,data_version)SELECTid,username,last_login_time,2FROMusersWHEREstatusactiveONDUPLICATEKEYUPDATEusernameVALUES(username),last_activeVALUES(last_active),data_versionVALUES(data_version);
性能优化技巧
批量大小控制# Python示例分批处理大数据量defbatch_upsert(connection,table,data,batch_size
:foriinrange(0,len(data),batch_size):batchdata[i:ibatch_size]placeholders, .join([(%s, %s, %s, %s)]*len(batch))values[itemforsublistinbatchforiteminsublist]sqlf INSERT INTO{table}(id, col1, col2, col
VALUES{placeholders}ON DUPLICATE KEY UPDATE col1 VALUES(col
, col2 VALUES(col
, col3 VALUES(col
withconnection.cursor()ascursor:cursor.execute(sql,values)connection.commit()
索引优化确保用于检测重复的键主键或唯一键有适当的索引-- 为频繁用于冲突检测的列添加索引ALTERTABLEordersADDUNIQUEINDEXidx_order_no(order_no);
事务处理-- 使用事务确保批量操作的原子性STARTTRANSACTION;INSERTINTOlarge_table(id,col1,col
VALUES(1,A,B),(2,C,D),...-- 大量数据ONDUPLICATEKEYUPDATEcol1VALUES(col
,col2VALUES(col
;-- 只有在所有行都处理成功后才提交COMMIT;
七、
常见问题与解决方案
如何获取受影响的行数# Python示例获取实际插入/更新的行数cursorconnection.cursor()cursor.execute(upsert_sql,params)affected_rowscursor.rowcount# 注意在批量操作中rowcount返回的是总影响行数# 实际插入的行数 affected_rows - (更新的行数*
2)
如何知道哪些行是插入的哪些是更新的-- MySQL
0 可以使用ROW_COUNT()和LAST_INSERT_ID()INSERTINTO...ONDUPLICATEKEYUPDATE...;SELECTROW_COUNT();-- 返回-1表示所有行都是更新正数表示插入的行数
与REPLACE INTO的性能对比指标INSERT ON DUPLICATE KEY UPDATEREPLACE INTO操作类型直接更新删除后插入自增ID保持不变可能改变触发器UPDATE触发器DELETEINSERT触发器批量性能优秀良好原子性是是
最佳实践明确业务需求需要部分更新 → 使用 ON DUPLICATE KEY UPDATE需要完全替换 → 使用 REPLACE INTO需要忽略冲突 → 使用 INSERT IGNORE批量大小选择通常
行/批是合理的测试不同批量大小以找到最佳值错误处理try:batch_upsert(connection,products,data_list)exceptExceptionase:# 记录错误并考虑重试机制logger.error(fBatch upsert failed:{str(e)})# 可能需要拆分批次重试监控性能-- 检查慢查询日志SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 秒
九、
总结INSERT ... ON DUPLICATE KEY UPDATE是MySQL中处理存在则更新不存在则插入场景的高效解决方案特别是在批量操作时表现出色。
通过合理使用这一语句可以减少数据库往返次数保持自增ID的稳定性简化应用逻辑无需先查询再决定插入或更新在事务中保证操作的原子性在实际应用中应根据业务需求选择合适的批量大小添加适当的错误处理和监控以充分发挥这一特性的优势。