**一、核心语法:INSERT ... ON DUPLICATE KEY UPDATE
**
-
功能
- 当插入数据触发唯一索引(主键或唯一键)冲突时,执行更新操作而非报错。
- 适用于批量插入时自动判断插入或更新,减少业务层逻辑。
-
语法示例
INSERT INTO table_name (col1, col2, col3) VALUES (val1, val2, val3) ON DUPLICATE KEY UPDATE col1 = VALUES(col1), col2 = VALUES(col2);
- 使用
VALUES(col_name)
可引用插入时的原始值。
- 使用
二、使用场景
-
批量插入更新
-
通过唯一索引(如
(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>
-
-
避免重复插入
- 若表中已存在相同唯一键的记录,更新指定字段(如时间戳或计数器)。
三、锁机制与并发问题
- 加锁行为
- 在可重复读(Repeatable Read)隔离级别下:
- 命中唯一索引的记录:仅加记录锁(Record Lock)。
- 未命中记录:对附近间隙加间隙锁(Gap Lock),阻止其他事务插入冲突值。
- 范围查询时,对符合条件的记录和间隙加锁。
- 在可重复读(Repeatable Read)隔离级别下:
- 死锁风险
- 高并发场景下,多线程使用该语句可能导致死锁,需谨慎设计事务逻辑。
四、替代方案
-
**
INSERT IGNORE
**-
忽略重复数据,不报错且不插入,适用于“存在则跳过”的场景。
-
示例:
INSERT IGNORE INTO my_table (id, name) VALUES (1, 'test');
-
-
**
REPLACE INTO
**-
先删除冲突记录再插入新数据,自增主键值会变化。
-
示例:
REPLACE INTO my_table (id, name) VALUES (1, 'new_test');
-
五、错误处理
-
唯一键冲突报错
-
错误示例:
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';
-
-
-
约束检查
- 使用
SELECT ... FOR UPDATE
提前锁定记录,或在业务层校验唯一性。
- 使用
总结
场景 | 推荐方案 | 注意事项 |
---|---|---|
插入冲突时更新字段 | ON DUPLICATE KEY UPDATE | 注意间隙锁和死锁风险 |
存在则跳过 | INSERT IGNORE | 不更新原有数据 |
强制替换 | REPLACE INTO | 自增主键值会变化 |
高并发批量操作 | 事务拆分 + 提前校验唯一性 | 避免长事务和锁竞争 |
合理选择方案,可有效提升数据操作效率和并发安全性