数据库原理与选型 / 系统课时

冲突处理与 UPSERT

冲突处理与 UPSERT

本课解决“插入数据遇到主键或唯一键冲突时应该跳过、替换还是更新”这个问题。你将比较三种策略的真实行为,并使用 UPSERT 在保留原记录的同时更新指定字段。

学习目标

  • 理解唯一约束冲突的触发条件
  • 区分 OR IGNORE、OR REPLACE 和 UPSERT
  • 使用 excluded 引用本次准备插入的新值

准备唯一约束

下面的 email 列具有 UNIQUE 约束。重复邮箱会触发唯一性冲突。

sql
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    login_count INTEGER NOT NULL DEFAULT 0
);

INSERT INTO users (id, email, name, login_count)
VALUES (1, 'lin@example.com', '小林', 2);

预期结果:建表和插入成功,不返回结果集。初始数据只有一行,后续示例都与这条邮箱记录发生冲突。

OR IGNORE:跳过冲突行

INSERT OR IGNORE 遇到适用的约束冲突时,会跳过当前行而不抛出错误。原记录保持不变。

sql
INSERT OR IGNORE INTO users (id, email, name, login_count)
VALUES (2, 'lin@example.com', '林同学', 0);

SELECT changes() AS affected_rows;

SELECT id, email, name, login_count
FROM users;

预期结果:

text
affected_rows
0

id | email           | name | login_count
1  | lin@example.com | 小林 | 2

IGNORE 适合“重复就不处理”的导入任务,但必须检查 changes(),否则应用可能误以为数据已经写入。

OR REPLACE:删除后重新插入

INSERT OR REPLACE 不是普通 UPDATE。发生唯一键冲突时,SQLite 会移除冲突行,再插入新行,因此主键、外键关联和触发器行为都可能变化。

sql
INSERT OR REPLACE INTO users (id, email, name, login_count)
VALUES (2, 'lin@example.com', '林同学', 0);

SELECT id, email, name, login_count
FROM users;

预期结果:

text
id | email           | name   | login_count
2  | lin@example.com | 林同学 | 0

原来的 id = 1 已不存在。若其他表通过外键引用旧主键,REPLACE 可能带来级联删除或约束失败,因此不应把它当成无副作用的更新语句。

UPSERT:冲突时更新

UPSERT 使用 ON CONFLICT ... DO UPDATE。它保留冲突行,并只更新 SET 中指定的字段。excluded 表示本次原计划插入的新记录。

sql
INSERT INTO users (id, email, name, login_count)
VALUES (3, 'lin@example.com', '林老师', 1)
ON CONFLICT(email) DO UPDATE SET
    name = excluded.name,
    login_count = users.login_count + 1;

SELECT id, email, name, login_count
FROM users;

预期结果:

text
id | email           | name   | login_count
2  | lin@example.com | 林老师 | 1

冲突发生在 email,所以执行 UPDATE 分支。新值中的 id = 3 不会覆盖现有主键;只有 SET 明确列出的字段会变化。

选择合适策略

策略 冲突时行为 典型场景
OR IGNORE 跳过冲突行 去重导入、重复消息幂等
OR REPLACE 删除旧行后插入新行 明确接受主键和关联变化的覆盖写入
ON CONFLICT DO UPDATE 保留旧行并更新指定列 计数累加、资料同步、状态刷新

UPSERT 只能针对主键、UNIQUE 约束或唯一索引等唯一性冲突。业务上仍应明确冲突目标,避免把无关唯一键的冲突也当成同一种处理。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

冲突处理与 UPSERT

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通