UPSERT

1. 语法

2. 描述

UPSERT 是添加到 INSERT 中的一个子句，它使 INSERT 的行为类似于 UPDATE 或无操作，如果 INSERT 会违反唯一性约束。UPSERT 不是标准 SQL。SQLite 中的 UPSERT 遵循 PostgreSQL 建立的语法，并进行了一些泛化。

UPSERT 是一个普通的 INSERT 语句，后面跟着一个或多个 ON CONFLICT 子句，如上图语法图所示。

"ON CONFLICT" 和 "DO" 关键字之间的语法称为“冲突目标”。冲突目标指定将触发 upsert 的唯一性约束。冲突目标可以在 INSERT 语句中的最后一个 ON CONFLICT 子句中省略，但在所有其他 ON CONFLICT 子句中是必需的。

如果插入操作会导致冲突目标唯一性约束失败，则插入操作将被省略，而是执行相应的 DO NOTHING 或 DO UPDATE 操作。ON CONFLICT 子句按指定的顺序检查。如果最后一个 ON CONFLICT 子句省略了冲突目标，则如果任何唯一性约束失败且未被先前的 ON CONFLICT 子句捕获，则它将触发。

对于 INSERT 的每一行，只有一个 ON CONFLICT 子句（特别是第一个具有匹配冲突目标的 ON CONFLICT 子句）可能会运行。当一个 ON CONFLICT 子句触发时，该行的所有后续 ON CONFLICT 子句都将被绕过。

在多行插入的情况下，upsert 决策是针对插入的每一行单独做出的。

UPSERT 处理仅针对唯一性约束。 “唯一性约束”是在 CREATE TABLE 语句中显式的 UNIQUE 或 PRIMARY KEY 约束，或唯一索引。UPSERT 不会干预失败的 NOT NULL、CHECK 或外键约束，或使用触发器实现的约束。

DO UPDATE 表达式中的列名引用该列的原始未更改值，即在尝试 INSERT 之前的值。要使用如果约束未失败则将插入的值，请在列名中添加特殊的“excluded.”表限定符。

2.1. 示例

一些示例将有助于说明 UPSERT 的工作原理

CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
INSERT INTO vocabulary(word) VALUES('jovial')
  ON CONFLICT(word) DO UPDATE SET count=count+1;

上面的 upsert 会插入新的词汇“jovial”，如果该词不在字典中，或者如果该词已经在字典中，则会增加计数器。 “count+1”表达式也可以写成“vocabulary.count”。PostgreSQL 需要第二种形式，但 SQLite 接受这两种形式。

CREATE TABLE phonebook(name TEXT PRIMARY KEY, phonenumber TEXT);
INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212')
  ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;

在第二个示例中，DO UPDATE 子句中的表达式采用“excluded.phonenumber”的形式。“excluded.”前缀导致“phonenumber”引用如果不存在冲突则将插入的 phonenumber 的值。因此，upsert 的效果是，如果不存在 Alice 的电话号码，则插入 Alice 的电话号码，或者用新电话号码覆盖 Alice 的任何先前电话号码。

请注意，DO UPDATE 子句仅作用于在 INSERT 期间遇到约束错误的单个行。无需包含限制操作范围到该行的 WHERE 子句。DO UPDATE 末尾的 WHERE 子句的唯一用途是根据原始值和/或新值选择性地将 DO UPDATE 更改为无操作。例如

CREATE TABLE phonebook2(
  name TEXT PRIMARY KEY,
  phonenumber TEXT,
  validDate DATE
);
INSERT INTO phonebook2(name,phonenumber,validDate)
  VALUES('Alice','704-555-1212','2018-05-08')
  ON CONFLICT(name) DO UPDATE SET
    phonenumber=excluded.phonenumber,
    validDate=excluded.validDate
  WHERE excluded.validDate>phonebook2.validDate;

在这个最后一个示例中，只有当新插入值的 validDate 比表中已有的条目更新时，才会更新 phonebook2 条目。如果表已经包含具有相同名称和当前 validDate 的条目，则 WHERE 子句会导致 DO UPDATE 成为无操作。

2.2. 解析歧义

当附加 UPSERT 的 INSERT 语句从 SELECT 语句中获取其值时，存在潜在的解析歧义。解析器可能无法分辨“ON”关键字是引入 UPSERT 还是连接的 ON 子句。为了解决此问题，SELECT 语句应始终包含 WHERE 子句，即使该 WHERE 子句只是“WHERE true”。

ON 的歧义用法

INSERT INTO t1 SELECT * FROM t2
ON CONFLICT(x) DO UPDATE SET y=excluded.y;

使用 WHERE 子句解决歧义

INSERT INTO t1 SELECT * FROM t2 WHERE true
ON CONFLICT(x) DO UPDATE SET y=excluded.y;

3. 限制

UPSERT 目前不适用于虚拟表。

DO UPDATE 子句的更新操作的冲突解决算法始终为 ABORT。换句话说，行为就像 DO UPDATE 子句实际上写成“DO UPDATE OR ABORT”。如果 DO UPDATE 子句遇到任何约束违规，则整个 INSERT 语句将回滚并停止。即使 DO UPDATE 子句包含在指定其他冲突解决算法的 INSERT 语句或触发器中，也是如此。

4. 历史

UPSERT 语法是在 SQLite 版本 3.24.0（2018-06-04）中添加的。最初的实现紧密遵循 PostgreSQL 语法，因为它只允许一个 ON CONFLICT 子句，并且它需要用于 on DO UPDATE 的冲突目标。在 SQLite 版本 3.35.0（2021-03-12）中，语法被泛化以允许多个 ON CONFLICT 子句，并允许在没有冲突目标的情况下进行 DO UPDATE 解析。

此页面上次修改于 2024-04-11 23:26:09 UTC