Upsert 是什么

「存在则更新、不存在则插入」这类操作统称 upsert。MySQL 用 INSERT … ON DUPLICATE KEY UPDATE，PostgreSQL 用 INSERT … ON CONFLICT (列) DO UPDATE SET。判断「已存在」的依据不同：MySQL 依赖表上的主键或唯一索引，只要任一唯一键冲突就转到更新分支；PostgreSQL 必须显式写出冲突目标列，只在该唯一约束冲突时触发。用 upsert 能避免「先查再写」的竞态窗口，并发场景下比应用层判断可靠得多。

基本信息

典型使用场景

• 配置项写入：有则更新、无则创建，一条语句完成
• 计数器累加：ON DUPLICATE KEY UPDATE cnt = cnt + 1
• 数据同步：把上游全量数据幂等地写入本地表
• 任务幂等重试：重跑时不产生重复行
• 汇总表回写：按维度键 upsert 统计结果
• 并发写入唯一业务键：比先 SELECT 再 INSERT 更可靠

示例

-- MySQL：冲突时更新
INSERT INTO user_points (user_id, points, updated_at)
VALUES (100, 10, NOW())
ON DUPLICATE KEY UPDATE
    points     = points + VALUES(points),   # 累加而不是覆盖
    updated_at = NOW();

-- PostgreSQL：显式指定冲突列
INSERT INTO user_points (user_id, points, updated_at)
VALUES (100, 10, now())
ON CONFLICT (user_id) DO UPDATE
SET points     = user_points.points + EXCLUDED.points,
    updated_at = now();

只跳过不更新

-- MySQL
INSERT IGNORE INTO user_points (user_id, points) VALUES (100, 10);

-- PostgreSQL
INSERT INTO user_points (user_id, points) VALUES (100, 10)
ON CONFLICT (user_id) DO NOTHING;

-- 批量 upsert：多行 VALUES 同样适用
INSERT INTO user_points (user_id, points) VALUES (100, 1), (101, 2)
ON CONFLICT (user_id) DO UPDATE SET points = user_points.points + EXCLUDED.points;

两种 upsert 对照

实践要点

• 先确认冲突依据的那一列确实有唯一约束，否则 upsert 永远不会触发更新分支
• 更新分支里要写清是累加还是覆盖，两者业务含义完全不同
• 大批量 upsert 控制每批行数，避免单条语句过大
• upsert 的返回值也要关注：MySQL 的 affected rows 在插入与更新时数值不同
• 同步场景配合事务使用，保证一批数据要么全进要么全不进
• 把 upsert 当作幂等写入的基础，任务重试就不用额外去重

常见误用

• 表上没有对应的唯一约束，upsert 退化为普通插入，重复数据照样写进去
• MySQL 表上有多个唯一键，任意一个冲突都会触发更新分支，误覆盖数据
• 更新分支写成覆盖而不是累加（或反之），统计值逐渐偏离真实
• 批量 upsert 行数过多，单条语句过大，失败后整批回滚
• upsert 与普通 INSERT 混在同一段逻辑里，冲突处理行为不一致
• 依赖自增编号的连续性，而冲突会消耗自增值造成编号空洞