我正在嘗試用 UPSERT 替換這個 INSERT 陳述句（現在在 sqlite 中支持）
55 await db.query(
56 "INSERT OR REPLACE INTO urls(url, title, excerpt, created_at, updated_at) VALUES (?, ?, ?, ?, ?)",
57 [url, article.title, article.excerpt, new Date().toISOString(), new Date().toISOString()],
58 );
我想保留原始created_at時間戳，url如果該行已經存在，則可以更新其他屬性（除了鍵之外）。
如果您還可以count = count 1向表中添加一列以跟蹤視圖，則可以加分。
uj5u.com熱心網友回復：
如果列有唯一約束，url則UPSERT的語法為：
INSERT INTO urls(url, title, excerpt, created_at, updated_at) VALUES (?, ?, ?, ?, ?)
ON CONFLICT(url) DO UPDATE
SET title = EXCLUDED.title,
excerpt = EXCLUDED.excerpt,
updated_at = EXCLUDED.updated_at;
如果還有一列count：
INSERT INTO urls(url, title, excerpt, created_at, updated_at, count) VALUES (?, ?, ?, ?, ?, 1)
ON CONFLICT(url) DO UPDATE
SET title = EXCLUDED.title,
excerpt = EXCLUDED.excerpt,
updated_at = EXCLUDED.updated_at,
count = count 1;
或者，如果您count使用默認值 1進行定義，則可以從 INSERT 串列中省略它：
INSERT INTO urls(url, title, excerpt, created_at, updated_at) VALUES (?, ?, ?, ?, ?)
ON CONFLICT(url) DO UPDATE
SET title = EXCLUDED.title,
excerpt = EXCLUDED.excerpt,
updated_at = EXCLUDED.updated_at,
count = count 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/352700.html
上一篇：如何每天自動點擊網站上的按鈕