如何在sqlite中使用UPSERT以保留created_at时间

Question

我正试图用UPSERT(现在sqlite中支持(替换此INSERT语句

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列添加到用于跟踪视图的表中，则可获得额外积分。

Answer 1

如果列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;

或者，如果您定义了默认值为1的count，则可以从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;