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提前锁定记录，或在业务层校验唯一性‌。

‌总结‌

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