当前位置: 首页 > article >正文

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自增主键值会变化
高并发批量操作事务拆分 + 提前校验唯一性避免长事务和锁竞争

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


http://www.kler.cn/a/587078.html

相关文章:

  • 1216走迷宫
  • Keil5下载教程及安装教程(附安装包)
  • Unity AssetBundles资源加载管理器
  • Debain-12.9使用xinference部署音频模型/audio
  • 《灵珠觉醒:从零到算法金仙的C++修炼》卷三·天劫试炼(48)戮魂幡染节点 - 二分图检测(着色法)
  • 【NLP】 9. 处理创造性词汇 词组特征(Creative Words Features Model), 词袋模型处理未知词,模型得分
  • 【黑马点评|项目】万字总结(下)
  • Python软件和搭建运行环境
  • C++进阶——map和set的使用
  • Python在数据处理中的应用:从入门到精通
  • Linux date 命令使用指南
  • 用Python打造AI玩家:挑战2048,谁与争锋
  • Socket服务器和客户端
  • 安装SQL数据库并且在jupyter中连接,运行
  • 回溯法--力扣第17题“电话号码的字母组合”(java)
  • 【初级篇】如何使用DeepSeek和Dify构建高效的企业级智能客服系统
  • stable Diffusion 中的 VAE是什么
  • Maximize Rating
  • [动手学习深度学习]24. AlexNet
  • 神经网络的基本知识