SQLite中如何使用UPSERT保留原有created_at时间并实现访问量计数累加

操作前提 #

如果你还没有为urls表添加count字段，先执行以下DDL语句新增字段：

ALTER TABLE urls ADD COLUMN count INTEGER NOT NULL DEFAULT 1;

替换后的UPSERT实现代码 #

你可以直接替换原有数据库查询逻辑，使用SQLite原生ON CONFLICT语法实现需求：

await db.query(
  `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`,
  [url, article.title, article.excerpt, new Date().toISOString(), new Date().toISOString()],
);

逻辑说明 #

• 首次插入新记录时，count默认赋值为1，符合首次访问计数规则
• 当主键url冲突（即记录已存在）时，仅更新title、excerpt、updated_at三个字段，created_at会完全保留原有行的旧值，不会被覆盖
• 冲突时count直接在原有值基础上累加1，实现访问量统计逻辑
• 全程仅需单次数据库请求，原子性和性能都优于先查询再判断插入/更新的方案，兼容SQLite 3.24.0及以上版本

内容的提问来源于stack exchange，提问作者chovy