SQLite 更新避坑：为什么 INSERT OR REPLACE 会删掉关联数据

同步商品信息时，很容易把“存在就覆盖，不存在就新增”写成 INSERT OR REPLACE。如果只检查商品名称，结果似乎正确；一旦商品下面还有备注、附件等子表，问题就会显现：更新商品后，关联记录不见了。

原因藏在“替换”的具体语义里。本文用一组可以复现的 SQL，观察 REPLACE 与 UPSERT 对同一份数据的不同影响。示例在独立内存数据库运行，不连接业务数据库。

一、先建立能暴露问题的数据

在新数据库连接中，先开启外键检查，再建表并插入样例。PRAGMA foreign_keys 应在事务开始前设置；读取该配置应返回 1。

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

CREATE TABLE item (
    id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    revision INTEGER NOT NULL DEFAULT 0 CHECK (revision >= 0),
    created_label TEXT NOT NULL DEFAULT 'new'
);

CREATE TABLE note (
    id INTEGER PRIMARY KEY,
    item_id INTEGER NOT NULL REFERENCES item(id) ON DELETE CASCADE,
    content TEXT NOT NULL
);

INSERT INTO item VALUES (1, 'demo-001', 'old', 1, 'seed');
INSERT INTO note VALUES (10, 1, 'keep this note');

这里故意设置两个观察点：子表有一条备注；商品有一个未必会在同步请求中出现的创建标记。这样，测试就能检查“业务关系是否保留”和“未指定字段是否保留”，而不只看名称。

二、主键没变，也可能发生删除

在上述数据上执行：

INSERT OR REPLACE INTO item(id, sku, name)
VALUES (1, 'demo-001', 'replacement');

SELECT id, revision, created_label FROM item;
SELECT count(*) FROM note;

本地验证的结果分别为 (1, 0, 'new') 和 0。

当主键或唯一约束冲突触发 REPLACE 时，冲突旧行会先被删除，再完成新行写入。示例中的删除触发了外键 ON DELETE CASCADE，所以备注被级联删除。新行虽然重新使用 id=1，也不会把备注恢复回来；没有指定的 revision、created_label 则采用了默认值。

因此，“查询仍能找到同一个 id”不足以证明执行的是原行更新。观察关联表和被省略的字段，才能发现这种副作用。这里的级联删除现象以外键检查已经开启为前提。

三、需要保留原行时，明确写出 UPSERT

重新创建一份相同的初始数据库，再执行以下语句，与上一节分别测试：

INSERT INTO item(sku, name, revision)
VALUES ('demo-001', 'updated', 2)
ON CONFLICT(sku) DO UPDATE SET
    name = excluded.name,
    revision = excluded.revision
WHERE excluded.revision > item.revision;

SELECT id, name, revision, created_label FROM item;
SELECT count(*) FROM note;

结果为 (1, 'updated', 2, 'seed')，备注数量仍为 1。这里通过 sku 的唯一约束识别同一件商品，只更新 name 和 revision。原 id、创建标记及备注关系都得到保留。

excluded.name 表示本次尝试插入的新值；item.revision 表示已有行的版本。末尾条件让较旧或相同版本的事件跳过更新。对于不存在的新 sku，仍正常执行插入。

这条语句适用于“上游版本号单调递增，并且同版本内容一致”的同步约定。如果同一个版本可能携带不同内容，就需要额外检测冲突，不能仅凭版本相等就认定数据一致。SQL 中的更新条件也不能替代整个业务流程的事务设计。

四、冲突处理不能替代数据校验

UPSERT 的冲突目标对应唯一性约束。它不会把所有写入错误都转换成更新。例如：name 传入 NULL 仍违反 NOT NULL；负数 revision 仍违反 CHECK；备注引用不存在的商品仍违反外键约束。

应用程序应使用参数绑定，把这些失败按数据质量问题记录和处理。不要为了让导入“看起来成功”，把约束异常一律吞掉。选择 DO NOTHING 时也应明确统计跳过数量，否则一批导入执行完毕，并不意味着所有输入都已经保存。

五、如何给同步脚本做一次有效验证

本文使用 Python 标准库 sqlite3 和 SQLite 3.51.0，在独立内存数据库验证了以下行为：REPLACE 删除关联备注并重置省略字段；UPSERT 保留原行身份、创建标记与备注；较旧和同版本事件不覆盖数据；新 sku 正常插入；NOT NULL、CHECK 与外键错误仍然失败。

把测试关注点放在最终数据上：父表字段、子表数量和旧事件处理结果都应有明确预期。仅检查 SQL 没有抛异常，无法证明一次同步正确。

本文使用的基础 UPSERT 语法要求 SQLite 3.24.0 或更高版本。运行环境应检查实际链接的 SQLite 版本；部署前也应在与业务一致的表结构上测试，因为外键动作和约束设计会影响最终结果。

参考：SQLite 官方手册《UPSERT》《The ON CONFLICT Clause》《SQLite Foreign Key Support》。示例与验证过程为本文自行组织，内容经 AI 辅助整理；全部数据为演示数据。