跳转到主要内容

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
MySQL 没有 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。

故障排除

另请参阅