VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
UPSERT 关键字,但它有三种方式来插入行或处理冲突:INSERT ... ON DUPLICATE KEY UPDATE、REPLACE INTO 和 INSERT IGNORE。它们的行为差异很大——选错了可能会悄无声息地损坏数据。
INSERT … ON DUPLICATE KEY UPDATE
当插入会违反唯一约束(主键或唯一索引)时,MySQL 改为运行UPDATE:
VALUES(col) 语法也可以使用,但已被弃用——请改用行别名:
REPLACE INTO
REPLACE 删除冲突的行并插入一个新行。它看起来像 upsert,但行为像删除 + 插入:
BEFORE DELETE 和 AFTER DELETE 触发器,并为新行重置 AUTO_INCREMENT 计数器。由于该删除是真正的行删除,引用表上的 ON DELETE CASCADE 和其他外键动作会触发。REPLACE 中未列出的任何列都会取其默认值——而不是前一行的值。
只有当您确实想完全丢弃旧行时,才使用 REPLACE。
INSERT IGNORE
INSERT IGNORE 将错误降级为警告。遇到键冲突时它跳过该行;遇到类型不匹配或截断时,它改为写入一个强制转换后的值:
INSERT IGNORE 的问题在于它抑制所有错误,而不仅仅是重复项——而且非重复的情况比跳过一行更糟糕。INSERT IGNORE INTO page_views (page, views) VALUES ('/new', 'not_a_number') 会插入 ('/new', 0),只给出一个警告:这是一次悄无声息的损坏写入,而不是空操作。只有当您确实不关心结果时才使用它。
比较
AUTO_INCREMENT 副作用
ON DUPLICATE KEY UPDATE 即使走的是更新路径而不是插入路径,也会递增 AUTO_INCREMENT 计数器。随着时间推移,这会在序列中产生空洞。page_views 使用 page 作为主键,没有 AUTO_INCREMENT 列,因此要看到这个效果,请使用一个带代理键的表:
常见问题
ON DUPLICATE KEY UPDATE 是原子的吗?
是的——插入尝试和更新在一个原子操作中完成。您无需将其包在事务中来避免检查存在性和插入之间的竞争条件。如果多个唯一索引同时冲突会怎样?
如果一次插入同时违反多个唯一约束,MySQL 只更新一行——具体是哪一行是未定义的。请避免让单条 INSERT 可能在多个唯一索引上冲突的设计。如果您的模式有重叠的唯一约束,请仔细测试其行为。我可以在多行插入中使用 ON DUPLICATE KEY UPDATE 吗?
可以:VALUES,别名用 AS new。
故障排除
另请参阅
- MySQL 中的事务——何时将 UPSERT 包在事务中
- MySQL 中的死锁——INSERT … ON DUPLICATE KEY UPDATE 在并发下可能导致死锁
- MySQL 中的批量插入——快速加载大量行,带或不带冲突处理
- 常见 MySQL 错误及其修复方法——重复键错误及其含义
- MySQL INSERT ON DUPLICATE KEY UPDATE——该语句本身,附带实例