upsert是“insert or update”的合称，指主键或唯一索引冲突时更新、否则插入；mysql用insert ... on duplicate key update，postgresql用insert ... on conflict (col) do update，二者语法不兼容且均依赖唯一约束。
什么是 Upsert？MySQL 和 PostgreSQL 的语法差异
Upsert 不是标准 SQL 关键字，而是“insert or update”的合称，核心诉求是：当主键或唯一索引冲突时更新，否则插入。但 MySQL 和 PostgreSQL 实现方式完全不同，混用会直接报错。
- MySQL 用
INSERT ... ON DUPLICATE KEY UPDATE，依赖表中已定义的PRIMARY KEY或UNIQUE约束 - PostgreSQL 用
INSERT ... ON CONFLICT (...) DO UPDATE，必须显式写出冲突列（如ON CONFLICT (id)），不能只写ON CONFLICT DO UPDATE - SQLite 用
INSERT OR REPLACE INTO，但它是“删再插”，会触发删除逻辑（如外键级联、触发器），且不保留原行自增 ID
增量数据分组去重：先聚合再 Upsert 才安全
原始增量数据常含重复记录（比如同一订单多次上报），若直接 Upsert，可能因执行顺序不确定导致最终状态错误。正确做法是：在 Upsert 前，对增量批次按业务主键（如 order_id）分组，取最新/最全的一条。
- 用
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC)标记每组最新记录，再WHERE rn = 1过滤 - 避免用
GROUP BY + MAX(updated_at)后再 JOIN 回原表——容易丢失同时间戳下的其他字段值 - PostgreSQL 中可直接在
INSERT语句里嵌套 CTE：WITH dedup AS (SELECT ..., ROW_NUMBER() ...) INSERT INTO t SELECT ... FROM dedup WHERE rn = 1 ... - MySQL 8.0+ 支持 CTE；5.7 只能用临时表或子查询，注意子查询不能直接引用外层表别名（相关子查询限制）
Upsert 中如何引用“旧值”和“新值”？
更新动作中常需基于原值计算（如累加计数、合并 JSON 字段），不同数据库对“旧行”和“新行”的引用语法不同，写错会导致字段被覆盖为 NULL 或默认值。
- MySQL：在
ON DUPLICATE KEY UPDATE子句中，用col = VALUES(col)表示新值，col = col + 1表示旧值（即当前行已有值） - PostgreSQL：在
DO UPDATE SET中，用EXCLUDED.col指代本次要插入的新值，target_table.col或直接col指代已存在旧行的值（需给目标表起别名，如FROM target_table AS t） - 常见坑：
ON CONFLICT DO UPDATE SET json_col = jsonb_set(json_col, '{tags}', EXCLUDED.tags)—— 如果EXCLUDED.tags是 NULL，整个json_col会被置空，应加WHERE EXCLUDED.tags IS NOT NULL
并发写入下 Upsert 的一致性风险
即使语法正确，高并发 Upsert 仍可能因“检查-插入/更新”非原子性引发异常，比如双写导致唯一约束冲突（MySQL 报 Duplicate entry）或丢失更新（PostgreSQL 中两个事务同时读到旧值再各自更新）。
- 根本解法是让数据库承担冲突判断责任——确保
ON CONFLICT/ON DUPLICATE KEY覆盖所有可能的冲突路径（例如联合唯一索引也要写进冲突列） - 避免在应用层做“先查再决定 insert/update”，这在并发下必然出错
- PostgreSQL 中若需更严格控制，可在事务内加
SELECT ... FOR UPDATE锁住目标行，但会显著降低吞吐，仅适用于低频关键场景 - 真正难处理的是“部分字段更新 + 多源增量混合”——此时建议把 Upsert 拆成两步：先用临时表载入并去重，再用单条 Upsert 语句批量处理，减少网络往返和锁持有时间
user_id，后端存 uid）、以及 JSON 字段合并时未处理 null 边界。这些不会导致语法报错，但会让最终数据“看起来正常，实则丢值”。
