Sqlite
來自衝突的選擇中的 Sqlite upsert 並不總是更新行
假設使用以下內容建構數據庫:
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;