SQL插入数据出现主键冲突_修改插入逻辑为UPSERT操作-SQL-PHP中文网

mysql用insert...on duplicate key update实现upsert，仅在主键或唯一索引冲突时转为更新，不支持条件判断或多表；postgresql用insert...on conflict可精准指定冲突约束；sqlite的insert or replace本质是删再插，慎用于有外键或触发器场景；应用层避免select+insert/update竞态，应使用原生命令。

sql插入数据出现主键冲突_修改插入逻辑为upsert操作

MySQL用 `INSERT ... ON DUPLICATE KEY UPDATE` 实现 UPSERT

主键或唯一索引冲突时，INSERT 直接报错（ERROR 1062: Duplicate entry），而 INSERT ... ON DUPLICATE KEY UPDATE 能自动转为更新。它只在触发唯一约束（主键或 UNIQUE 索引）时生效，不匹配则插入。

常见错误是误以为它能“按条件更新”，其实它不看 WHERE，只依赖索引冲突；也不支持多表联合判断。

ON DUPLICATE KEY UPDATE 后面必须写明要更新的列，比如 name = VALUES(name), updated_at = NOW()
VALUES(col) 是语法糖，代表本次 INSERT 中该列打算插入的值，不是函数调用
如果表有多个唯一索引，只要任一索引冲突就会触发更新，但无法指定“由哪个索引触发”
注意：执行后 ROW_COUNT() 返回 1（插入）或 2（更新），不是 0 或 1 —— 别用它判断是否新增成功

PostgreSQL 用 `INSERT ... ON CONFLICT` 更精准控制冲突目标

PostgreSQL 的 ON CONFLICT 显式声明冲突依据，比 MySQL 更可控。你得写清楚是主键冲突还是某个唯一索引冲突，避免误触发。

典型错误是漏写 ON CONFLICT ON CONSTRAINT 或 ON CONFLICT (col)，导致语法报错 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification。

主键冲突：用 ON CONFLICT ON CONSTRAINT table_pkey（table_pkey 是主键名，可用 \d table 查）
唯一索引冲突：用 ON CONFLICT (email)，括号里填索引字段，多字段就写 (tenant_id, external_id)
想跳过冲突不更新？用 DO NOTHING；想更新就写 DO UPDATE SET name = EXCLUDED.name，EXCLUDED 是关键字，代表本次想插入但被挡下的那行数据
EXCLUDED 不是变量，不能加引号，也不能拼接字符串 —— EXCLUDED.created_at || '_bak' 会报错

SQLite 用 `INSERT OR REPLACE` 本质是删再插，慎用于有外键或触发器的场景

INSERT OR REPLACE 看似简单，但它底层先 DELETE 再 INSERT，会破坏自增主键连续性，也可能意外触发 DELETE 触发器、级联删除子记录，甚至违反外键约束（如果子表没设 ON DELETE CASCADE）。

容易踩的坑是把它当“安全更新”用，结果发现关联数据被清掉了，或者主键 ID 突然跳变。

仅适用于单表、无外键、无复杂触发器的轻量场景
若需保留原主键且更新部分字段，改用 INSERT OR IGNORE + 单独 UPDATE 组合更稳妥
REPLACE 不支持 UPDATE 那样的条件判断，也无法访问原记录旧值做逻辑（比如“只在新值非空时覆盖”）

应用层做 UPSERT 要防竞态，别靠 SELECT + INSERT/UPDATE 两次查询

有人写先 SELECT 判断存在与否，再决定 INSERT 还是 UPDATE —— 这在并发下必然出错：两个请求同时查到“不存在”，然后都去 INSERT，必有一个失败。

除非你加了行锁（如 SELECT ... FOR UPDATE）并包在事务里，否则这种写法不可靠，而且性能差。

数据库原生命令（ON DUPLICATE KEY UPDATE / ON CONFLICT / OR REPLACE）才是正确解，它们原子执行
如果业务逻辑复杂到 SQL 难以表达（比如“更新时要比较旧值再决定是否覆盖”），那就用带重试的单次 INSERT，捕获主键冲突错误后执行 UPDATE，而不是提前查
ORM 框架如 Django 的 get_or_create()、SQLAlchemy 的 merge() 底层也依赖这些原生命令，别关掉它们去手动模拟

真正难的是理清“冲突到底该算谁的”——是主键重复？还是业务唯一键（比如 tenant_id + code）重复？选错冲突目标，UPSERT 就形同虚设。