SQL UPSERT / ON DUPLICATE KEY UPDATE / ON CONFLICT 的写法与性能对比

mysql的on duplicate key update要求主键或唯一索引存在，否则报错；postgresql的on conflict必须显式指定冲突字段；sqlite的replace into是先删后插，可能引发级联删除。

MySQL 用 ON DUPLICATE KEY UPDATE 做 UPSERT，但主键或唯一索引必须存在

没定义 UNIQUE 或 PRIMARY KEY，ON DUPLICATE KEY UPDATE 直接报错：Error 1062 (23000): Duplicate entry '...' for key 'PRIMARY' —— 这个错误不是“发生了冲突”，而是“连判断冲突的依据都没有”。

常见误操作是只建了普通索引，或者忘了给业务字段加 UNIQUE。比如想根据 email 去重更新，却只加了 INDEX email_idx (email)，没加 UNIQUE。

• INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice') ON DUPLICATE KEY UPDATE name = VALUES(name) 要求 email 列有 UNIQUE 约束（可以是单独列，也可以是联合唯一）
• 如果冲突字段是联合唯一（如 UNIQUE (tenant_id, external_id)），那 VALUES() 里必须包含这两个字段，否则无法触发更新
• 注意 VALUES(col) 是指本次 INSERT 语句中该列的值，不是表里原来的值；别写成 name = name，那会清空字段

PostgreSQL 的 ON CONFLICT 必须显式指定冲突目标，不能只靠索引名猜

PostgreSQL 不会自动识别“哪个唯一约束被违反”，你得明确告诉它：是按主键？还是某个唯一索引？还是某几个字段的组合？漏写或写错就直接报错：ON CONFLICT clause does not match any unique constraint。

典型场景是想按 email 去重，但表里有多个唯一约束（比如 PRIMARY KEY + UNIQUE (email)），不指定就失败。

• 正确写法：INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
• 如果冲突目标是联合唯一索引 idx_tenant_ext (tenant_id, external_id)，就得写 ON CONFLICT (tenant_id, external_id)，不能写索引名
• EXCLUDED 是个伪表，代表本次被拒绝插入的那行数据；别写成 NEW 或 INSERTED，它们不存在
• 如果字段允许 NULL，而冲突条件含 NULL，注意 PostgreSQL 中 NULL = NULL 为 FALSE，所以 ON CONFLICT (nullable_col) 对 NULL 值不生效

SQLite 的 REPLACE INTO 是删除+插入，可能意外删掉关联数据

REPLACE INTO 看似最简单，但它底层是先 delete 再 INSERT。一旦表上有外键级联（比如 ON DELETE CASCADE），或者触发器监听了 DELETE，行为就不可控了。

比如用户表和地址表有外键，用 REPLACE INTO users 更新邮箱，地址记录可能被连带删掉 —— 这不是“更新”，是“先杀后生”。

• 真正安全的 SQLite UPSERT 是 INSERT OR REPLACE INTO（等价于 REPLACE INTO），但同样走 delete-insert 流程
• 若需保留原行 ID 或避免级联删除，得用 INSERT OR IGNORE + 单独 UPDATE 两步，靠事务包住
• 没有 VALUES() 或 EXCLUDED 这类语法，所有更新值都得手写，容易漏字段或写错变量名

跨数据库写法不通用，性能差异集中在索引扫描和锁粒度

三者看着都是“插入或更新”，但执行路径完全不同：MySQL 在唯一索引上做一次查找+条件更新；PostgreSQL 先尝试插入，冲突时回退并执行 DO UPDATE；SQLite 删除再插，IO 和锁开销更大。

高并发下最容易出问题的是 MySQL 的 ON DUPLICATE KEY UPDATE：它会对冲突的索引记录加 next-key lock，可能比预期锁得更宽，引发间隙锁等待。

• PostgreSQL 的 ON CONFLICT 默认只锁冲突行，但如果 DO UPDATE 涉及其他索引字段，可能触发额外索引维护锁
• SQLite 在 WAL 模式下 REPLACE 仍会阻塞读，因为 delete 和 insert 是两个独立操作，中间有可见窗口
• 别指望用 ORM 的“upsert”抽象来屏蔽差异 —— django 的 update_or_create、SQLAlchemy 的 insert().on_conflict_do_update() 底层仍是各自方言，参数含义和边界行为不一致

最常被忽略的一点：所有这些语法都要求冲突判断字段有高效索引。没建索引的 ON CONFLICT (unindexed_col) 会全表扫，还报错；MySQL 里没索引的 ON DUPLICATE KEY 根本不工作。索引不是可选优化项，是功能前提。