欢迎来到尧图网

客户服务 关于我们

您的位置:首页 > 汽车 > 新车 > INSERT ... ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE

2025/3/16 6:16:18 来源:https://blog.csdn.net/zzw_17600691357/article/details/146281913  浏览:    关键词:INSERT ... ON DUPLICATE KEY UPDATE

‌**一、核心语法:INSERT ... ON DUPLICATE KEY UPDATE**‌

  1. 功能

    • 当插入数据触发唯一索引(主键或唯一键)冲突时,执行更新操作而非报错‌。
    • 适用于批量插入时自动判断插入或更新,减少业务层逻辑‌。
  2. 语法示例

    INSERT INTO table_name (col1, col2, col3)
    VALUES (val1, val2, val3)
    ON DUPLICATE KEY UPDATE col1 = VALUES(col1), col2 = VALUES(col2);
    
    • 使用VALUES(col_name)可引用插入时的原始值‌。

二、使用场景

  1. 批量插入更新

    • 通过唯一索引(如(name, age))批量插入数据,冲突时更新状态字段‌。

    • 示例(MyBatis动态SQL):

      <insert id="batchInsertUpdate">INSERT INTO w_user (w_name, w_age, status)VALUES <foreach collection="list" item="item" separator=",">(#{item.name}, #{item.age}, #{item.status})</foreach>ON DUPLICATE KEY UPDATE status = VALUES(status);
      </insert>
      
  2. 避免重复插入

    • 若表中已存在相同唯一键的记录,更新指定字段(如时间戳或计数器)‌。

三、锁机制与并发问题

  1. 加锁行为
    • 在可重复读(Repeatable Read)隔离级别下:
      • 命中唯一索引的记录:仅加记录锁(Record Lock)‌。
      • 未命中记录:对附近间隙加间隙锁(Gap Lock),阻止其他事务插入冲突值‌。
    • 范围查询时,对符合条件的记录和间隙加锁‌。
  2. 死锁风险
    • 高并发场景下,多线程使用该语句可能导致死锁,需谨慎设计事务逻辑‌。

四、替代方案

  1. ‌**INSERT IGNORE**‌

    • 忽略重复数据,不报错且不插入,适用于“存在则跳过”的场景‌。

    • 示例:

      INSERT IGNORE INTO my_table (id, name) VALUES (1, 'test');
      
  2. ‌**REPLACE INTO**‌

    • 先删除冲突记录再插入新数据,自增主键值会变化‌。

    • 示例:

      REPLACE INTO my_table (id, name) VALUES (1, 'new_test');
      

五、错误处理

  1. 唯一键冲突报错

    • 错误示例:Duplicate entry 'value' for key 'unique_key_constraint'‌。

    • ‌解决方法

      • ‌删除重复记录

        DELETE FROM table_name WHERE unique_col = 'value' LIMIT 1;
        
      • ‌修改重复值

        UPDATE table_name SET unique_col = 'new_value' WHERE unique_col = 'value';
        
  2. 约束检查

    • 使用SELECT ... FOR UPDATE提前锁定记录,或在业务层校验唯一性‌。

总结

场景推荐方案注意事项
插入冲突时更新字段ON DUPLICATE KEY UPDATE注意间隙锁和死锁风险
存在则跳过INSERT IGNORE不更新原有数据
强制替换REPLACE INTO自增主键值会变化
高并发批量操作事务拆分 + 提前校验唯一性避免长事务和锁竞争

合理选择方案,可有效提升数据操作效率和并发安全性‌

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com

热搜词