如何在SQL中优雅实现UPSERT存在即更新不存在则插入？-SQL-PHP中文网

MySQL 8.0+ 中 INSERT ... ON DUPLICATE KEY UPDATE 最直接的用法是依赖表上已有的 UNIQUE 或 PRIMARY KEY 约束自动触发更新，无需先查后判，具备原子性与高性能；常见错误是未建唯一索引导致语句退化为普通插入，必须确保冲突字段（如 email）已建立对应唯一约束。

如何在sql中优雅实现upsert存在即更新不存在则插入？

MySQL 8.0+ 用 `INSERT ... ON DUPLICATE KEY UPDATE` 最直接

只要表有唯一索引（UNIQUE 或 PRIMARY KEY），就能靠冲突触发更新。不是靠“查一遍再决定”，而是靠约束冲突自动走更新分支，性能好、原子性强。

常见错误是没建唯一索引就写这个语句——会直接插入两行，不报错也不更新。务必确认 INSERT 中涉及的列（比如 user_id）在表里有对应唯一约束。

示例：

INSERT INTO users (id, name, email, updated_at) 
VALUES (123, 'Alice', 'alice@example.com', NOW())
ON DUPLICATE KEY UPDATE 
  name = VALUES(name), 
  email = VALUES(email), 
  updated_at = NOW();

VALUES(name) 表示本次 INSERT 语句中 name 的值，比写死字符串或变量更安全
如果主键是自增 id，但你想用 email 去判重，就得给 email 加 UNIQUE 索引
不能在 UPDATE 子句里引用未在 INSERT 列表中出现的列（比如漏写了 updated_at，却在 ON DUPLICATE 里用它）

PostgreSQL 用 `INSERT ... ON CONFLICT DO UPDATE`

语法更显式，必须明确写出冲突目标（比如 ON CONFLICT (email)），不依赖隐式主键推断，可读性和控制力更强。

容易踩的坑是忽略 WHERE 条件导致误更新：比如只想更新「状态为 active 的老记录」，但忘了加 WHERE excluded.status = 'active'，结果把所有冲突行都刷了。

示例：

INSERT INTO users (id, name, email, status) 
VALUES (123, 'Alice', 'alice@example.com', 'active')
ON CONFLICT (email) 
DO UPDATE SET 
  name = EXCLUDED.name,
  status = EXCLUDED.status,
  updated_at = NOW()
WHERE users.status != 'deleted';

EXCLUDED 是 PostgreSQL 关键字，代表本次想插入但被拦下的那行数据
ON CONFLICT (email) 中的 email 必须对应一个唯一索引或主键约束，否则报错
如果表用的是复合唯一索引（如 (tenant_id, email)），这里就得写 ON CONFLICT (tenant_id, email)

SQLite 用 `INSERT OR REPLACE` 要小心主键/唯一键丢失

SQLite 没有真正的 UPSERT 语义，INSERT OR REPLACE 实际上是「删+插」：先按唯一键匹配删掉旧行，再插入新行。副作用明显——自增 id 会变，触发器会执行两次，外键级联可能被意外触发。

更安全的做法是用 INSERT OR IGNORE + UPDATE 组合，但得自己保证原子性（比如包在事务里）。

示例（安全组合）：

BEGIN;
INSERT OR IGNORE INTO users (id, name, email) 
VALUES (123, 'Alice', 'alice@example.com');
UPDATE users SET name = 'Alice', email = 'alice@example.com' 
WHERE id = 123;
COMMIT;