数据库原理与选型 / 系统课时
冲突处理与 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 | 小林 | 2IGNORE 适合“重复就不处理”的导入任务,但必须检查 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 约束或唯一索引等唯一性冲突。业务上仍应明确冲突目标,避免把无关唯一键的冲突也当成同一种处理。
按自己的节奏,确认真正理解。