SQLite UPSERT：ON CONFLICT DO UPDATE 与 DO NOTHING

插入，如果已存在就更新

一个常见需求：插入一行，但如果已经有相同键的行，就改为更新它。没有 UPSERT 的话，你得先写一个 SELECT，再根据结果分别执行 INSERT 或 UPDATE：两次往返，而且两者之间还存在竞态条件。

SQLite 的 UPSERT 用一条语句就能完成：

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (sku, name, price)
VALUES ('A-100', 'Widget', 9.99)
ON CONFLICT(sku) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price;

SELECT * FROM products;

第一次运行时，这一行被插入。换个价格、用同一个 sku 再运行一次，已有的那一行会被就地更新。没有重复，也没有报错。

ON CONFLICT 的结构

完整形式如下：

INSERT INTO table (...) VALUES (...)
ON CONFLICT(conflict_target) DO UPDATE SET col = expr, ...
WHERE condition;

关键在三个部分：

• conflict_target：你预计会发生冲突的列，这些列上有 UNIQUE 或 PRIMARY KEY 约束。SQLite 用它来决定监视哪个索引。
• DO UPDATE SET ...：发生冲突时，要对已有的行做哪些修改。（或者用 DO NOTHING 静默跳过。）
• 可选的 WHERE：额外的条件，必须为真时更新才会真正执行。

冲突目标必须对应一个真实存在的唯一约束。如果 price 不唯一，ON CONFLICT(price) 就无法编译，因为 SQLite 没有可以用来检测冲突的东西。

DO NOTHING：不存在才插入，否则跳过

这是更简单的形式。在填充初始数据或记录事件、重复数据只需悄悄忽略时很有用：

CREATE TABLE seen_events (
    event_id TEXT PRIMARY KEY,
    seen_at  TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO seen_events (event_id) VALUES ('evt-1')
ON CONFLICT(event_id) DO NOTHING;

INSERT INTO seen_events (event_id) VALUES ('evt-1')
ON CONFLICT(event_id) DO NOTHING;

INSERT INTO seen_events (event_id) VALUES ('evt-2')
ON CONFLICT(event_id) DO NOTHING;

SELECT * FROM seen_events;

第二条插入遇到了同一个 event_id，通常会报 UNIQUE constraint failed。有了 DO NOTHING，SQLite 直接跳过它。没有异常，也没有行受到影响。

这就是人们常常用 INSERT OR IGNORE 来实现的“幂等插入”。UPSERT 的 DO NOTHING 能做同样的事，而且和 WHERE、RETURNING 子句搭配得更好。

excluded 伪表

冲突发生时，突然有两行数据同时在场：表中已有的那一行，以及你尝试插入的新行。SQLite 提供了同时引用两者的方式。

• 直接写列名（price、name）指的是已有的行。
• excluded.column 指的是被拒绝的那条传入行。

CREATE TABLE inventory (
    sku      TEXT PRIMARY KEY,
    quantity INTEGER NOT NULL,
    updated  TEXT
);

INSERT INTO inventory (sku, quantity, updated)
VALUES ('A-100', 5, '2026-04-01')
ON CONFLICT(sku) DO UPDATE SET
    quantity = quantity + excluded.quantity,
    updated  = excluded.updated;

INSERT INTO inventory (sku, quantity, updated)
VALUES ('A-100', 3, '2026-04-02')
ON CONFLICT(sku) DO UPDATE SET
    quantity = quantity + excluded.quantity,
    updated  = excluded.updated;

SELECT * FROM inventory;

quantity = quantity + excluded.quantity 读作“已有的数量加上新的数量”。两次插入之后，A-100 的数量是 8。这种把数据累加到已有行上的写法，是 UPSERT 最实用的技巧之一。

带 WHERE 的条件 UPSERT

末尾的 WHERE 让你在条件不满足时跳过更新。它针对已有的行求值（也可以通过 excluded.* 引用传入的行）：

CREATE TABLE prices (
    sku        TEXT PRIMARY KEY,
    price      REAL NOT NULL,
    updated_at TEXT NOT NULL
);

INSERT INTO prices VALUES ('A-100', 10.00, '2026-04-01');

INSERT INTO prices (sku, price, updated_at)
VALUES ('A-100', 12.00, '2026-03-15')
ON CONFLICT(sku) DO UPDATE SET
    price      = excluded.price,
    updated_at = excluded.updated_at
WHERE excluded.updated_at > prices.updated_at;

SELECT * FROM prices;

新行带的 updated_at 更旧，所以 WHERE 为假，更新被跳过，已有的行保留了它更新的价格。把日期对调一下，更新就会执行。这就是“只用更新鲜的数据覆盖”的标准写法。

一次 upsert 多行

VALUES 可以包含很多行，ON CONFLICT 会分别作用于每一行：

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products VALUES ('A-100', 'Widget', 9.99);

INSERT INTO products (sku, name, price) VALUES
    ('A-100', 'Widget v2', 10.99),
    ('A-200', 'Gadget',     14.99),
    ('A-300', 'Gizmo',      19.99)
ON CONFLICT(sku) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price;

SELECT * FROM products ORDER BY sku;

A-100 发生冲突，被更新。A-200 和 A-300 是新的，被插入。一条语句，插入和更新混合的结果。用它来同步一批来自外部数据源的记录非常干净。

UPSERT 和 INSERT OR REPLACE

INSERT OR REPLACE 看起来做的是同一件事，其实不然。

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT,
    notes TEXT
);

INSERT INTO users VALUES (1, 'Ada', 'ada@example.com', 'VIP customer');

-- Replace: deletes the old row, inserts a new one.
INSERT OR REPLACE INTO users (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com');

SELECT * FROM users;

notes 不见了。INSERT OR REPLACE 把第 1 行整个删掉，再插入一行全新的数据，你没有列出的列都被重置为 NULL 或默认值。它还会触发 DELETE 触发器，并通过 ON DELETE 外键产生级联影响。

UPSERT 会保留这一行：

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT,
    notes TEXT
);

INSERT INTO users VALUES (1, 'Ada', 'ada@example.com', 'VIP customer');

INSERT INTO users (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com')
ON CONFLICT(id) DO UPDATE SET
    name  = excluded.name,
    email = excluded.email;

SELECT * FROM users;

notes 还在。只有 SET 中指定的列发生了变化。默认就用 UPSERT；只有当你确实想要“先删除再重新插入”的语义时，才用 INSERT OR REPLACE。

多个冲突目标

如果一行可能在多个约束上发生冲突，你可以把多个 ON CONFLICT 子句串起来：

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,
    username TEXT UNIQUE,
    email    TEXT UNIQUE,
    score    INTEGER DEFAULT 0
);

INSERT INTO accounts (username, email, score)
VALUES ('rosa', 'rosa@example.com', 10)
ON CONFLICT(username) DO UPDATE SET score = score + excluded.score
ON CONFLICT(email)    DO UPDATE SET score = score + excluded.score;

INSERT INTO accounts (username, email, score)
VALUES ('rosa', 'rosa@example.com', 5)
ON CONFLICT(username) DO UPDATE SET score = score + excluded.score
ON CONFLICT(email)    DO UPDATE SET score = score + excluded.score;

SELECT * FROM accounts;

哪个约束先触发，就执行哪个分支的 DO UPDATE。实际上，大多数表只有一个明显的冲突目标，也就是主键或某个唯一列，很少需要不止一个子句。

常见陷阱

几个容易踩的坑：

• 没有对应的唯一索引，就没有 UPSERT。 ON CONFLICT(col) 要求 col 是 PRIMARY KEY 或带有 UNIQUE 约束。否则 SQLite 会报错“no such constraint”。
• 没有冲突时，DO UPDATE 不会执行。 它是插入的替代方案，而不是附加行为。某个键第一次出现时，只会执行插入。
• excluded 是只读的。 你可以从中读取，但不能写入。SET 的目标永远是已有的行。
• 自动生成的 INTEGER PRIMARY KEY rowid。 如果你不提供 id，每次插入都会得到一个新的 id，根本不会冲突。只有当冲突列的值由调用方确定地提供时，UPSERT 才有意义。

下一步：RETURNING

UPSERT 不会告诉你哪些行是插入的、哪些是更新的，也不会告诉你它们最终的值是什么。要知道这些，你需要 RETURNING 子句：它在同一条语句中把受影响的行返回给你，不需要再执行 SELECT。这就是下一页的内容。