UPSERT（插入或更新）

1. 語法

2. 說明

UPSERT 是新增到 INSERT 的子句，如果 INSERT 會違反唯一性約束，則會使 INSERT 的行為如同 UPDATE 或無運作。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 已經有電話號碼，則用新的電話號碼覆蓋之前的電話號碼。

請注意，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 子句，並且需要 DO UPDATE 的衝突目標。在 SQLite 3.35.0 版 (2021-03-12) 中，語法已泛化為允許多個 ON CONFLICT 子句，並允許在沒有衝突目標的情況下進行 DO UPDATE 解析。

本頁面最後修改時間：2024-04-11 23:26:09 UTC