SQL 中 UPSERT 如何实现？如何设计幂等写入？

简化版

UPSERT 指“存在则更新，不存在则插入”。不同数据库语法不同：MySQL 常用 INSERT ... ON DUPLICATE KEY UPDATE，PostgreSQL 常用 INSERT ... ON CONFLICT ... DO UPDATE，SQL Server/Oracle 常见 MERGE。幂等写入的关键不是只写 UPSERT，而是用唯一约束定义冲突边界，例如业务单号、请求幂等键或自然唯一键。

详细版

UPSERT 常用于同步数据、创建或更新配置、消费消息落库、防重复提交等场景。它依赖数据库判断“是否已存在”，所以必须有唯一索引或主键作为冲突检测依据。

如果没有唯一约束，应用层先查再插在并发下可能插入重复数据。即使使用 UPSERT，也要明确更新哪些字段，避免把旧数据误覆盖。例如订单支付回调不能随便把状态从成功更新回处理中。

面试中要把 UPSERT 和幂等分开讲：UPSERT 是实现手段，幂等是业务语义和约束设计。

完整版教学

一、UPSERT 的业务场景

用户配置保存。

第三方回调落库。

消息消费记录。

商品库存快照同步。

导入任务重复执行。

这些场景都可能重复提交同一业务数据。

二、MySQL 写法

INSERT INTO user_settings (user_id, theme, updated_at)
VALUES (1001, 'dark', NOW())
ON DUPLICATE KEY UPDATE
  theme = VALUES(theme),
  updated_at = VALUES(updated_at);

这要求 user_id 或相关字段上有唯一约束。

冲突时执行更新逻辑。

三、PostgreSQL 写法

INSERT INTO user_settings (user_id, theme, updated_at)
VALUES (1001, 'dark', NOW())
ON CONFLICT (user_id) DO UPDATE
SET theme = EXCLUDED.theme,
    updated_at = EXCLUDED.updated_at;

EXCLUDED 表示本次准备插入但发生冲突的那行数据。

PostgreSQL 可以更明确地指定冲突列。

四、不同方案对比

方案	代表数据库	特点
`ON DUPLICATE KEY UPDATE`	MySQL	依赖唯一键冲突
`ON CONFLICT DO UPDATE`	PostgreSQL	冲突目标更明确
`MERGE`	Oracle、SQL Server 等	功能强，但语法复杂
先查再写	通用	并发下需要锁或唯一约束兜底

五、幂等键设计

幂等键可以是业务单号。

也可以是客户端生成的请求 id。

还可以是消息队列的 message id。

数据库中要对幂等键建立唯一约束。

重复请求命中同一个键时，返回同一业务结果或安全地忽略。

幂等的底线通常是唯一约束；只靠应用层判断很容易被并发击穿。

六、更新逻辑要保守

不是所有字段都应该被冲突更新覆盖。

创建时间通常不更新。

状态机字段要遵守状态流转规则。

金额、用户 id、订单号等关键字段要防止冲突时被篡改。

必要时只在特定条件下更新。

七、误区和追问

误区：UPSERT 可以不建唯一索引。 没有冲突约束，数据库无法可靠判断“已存在”。
误区：先查再插就能防重复。 并发下两个请求可能都查不到，然后都插入。
误区：冲突时把所有字段覆盖最省事。 可能破坏状态机或覆盖关键历史数据。
追问：支付回调如何幂等？ 用支付单号或业务订单号唯一约束，并按状态机安全更新。
追问：UPSERT 会有锁吗？ 会涉及唯一索引检查和行级冲突处理，要关注热点 key。
追问：重复请求应该返回什么？ 通常返回第一次成功的业务结果，或明确表示已处理。

八、面试表达方式

先解释不同数据库 UPSERT 语法。

再强调唯一约束和幂等键。

最后补充并发、状态机和字段覆盖风险。

简化版

详细版