Sqlite

來自衝突的選擇中的 Sqlite upsert 並不總是更新行

  • February 17, 2022

假設使用以下內容建構數據庫:

PRAGMA foreign_keys = ON;

CREATE TABLE foo (
 id   INTEGER PRIMARY KEY NOT NULL,
 name TEXT NOT NULL,

 UNIQUE (name)
);
INSERT INTO foo (name) VALUES ('one'), ('two'), ('three');

CREATE TABLE bar (
  id    INTEGER PRIMARY KEY NOT NULL,
  key   TEXT    NOT NULL,
  fooID INTEGER NOT NULL,

  FOREIGN KEY (fooID) REFERENCES foo(id) ON DELETE CASCADE,
  UNIQUE (key)
);

如果我想將一個值插入到表中bar(這裡由’key’字元串表示,但在現實世界中它將被參數化,這將是$1並且’三’將是$2)使用foo我嘗試過的名稱欄位如下所示:

INSERT INTO bar (key, fooID)
 SELECT 'key', foo.id FROM foo WHERE foo.name='three'
 ON CONFLICT (key) DO UPDATE SET fooID=excluded.id;

但它有時似乎只更新現有行,例如。在上面的人為範例中,如果我從使用“三”foo.name變為“二”,它會更新得很好,但如果我再次使用“三”執行,它不會更新。

sqlite> delete from bar
sqlite> INSERT INTO bar (key, fooID) SELECT 'key', foo.id FROM foo WHERE foo.name='three' ON CONFLICT (bar.key) DO UPDATE SET fooID=excluded.id;
sqlite> select * from bar;
1|key|3
sqlite> INSERT INTO bar (key, fooID) SELECT 'key', foo.id FROM foo WHERE foo.name='two' ON CONFLICT (bar.key) DO UPDATE SET fooID=excluded.id;
sqlite> select * from bar;
1|key|2
sqlite> INSERT INTO bar (key, fooID) SELECT 'key', foo.id FROM foo WHERE foo.name='three' ON CONFLICT (bar.key) DO UPDATE SET fooID=excluded.id;
sqlite> select * from bar;
1|key|2

我嘗試過的其他變體excluded.id要麼不存在,要麼不起作用。有人可以解釋這裡的行為,或者我可以如何在排除的行中列印列名列表,或者我可以調試它的另一種方式(或者甚至可能是一種更好的方式來執行我正在嘗試執行的插入操作)?

這裡的問題似乎來自對“排除”關鍵字的誤解。該文件指出:

要使用在約束沒有失敗的情況下將被插入的值,請添加特殊的“excluded”。列名的表限定符。

本來要插入的值fooID當然是excluded.fooID,而不是excluded.id我在上面的例子中。我相信external.id指的是列的行 ID,在這種情況下,第三行的行 ID 應該是 2。

所以我們最終得到的最終查詢是:

INSERT INTO bar (key, fooID)
 SELECT 'key', foo.id FROM foo WHERE foo.name='three'
 ON CONFLICT (key) DO UPDATE SET fooID=excluded.fooID;

引用自:https://dba.stackexchange.com/questions/307592