如何在SQLite已有记录时递增seen字段？Bash导入CSV场景

原脚本直接用.import会在遇到重复的first+last组合时触发唯一约束错误，无法自动递增seen字段。可以通过临时表中转+INSERT OR REPLACE的方式实现需求，具体修改后的脚本如下：

cat <<EOF | sqlite3 "$1.db"
CREATE TABLE IF NOT EXISTS 'dump' (
pk INTEGER PRIMARY KEY,
first TEXT,
last TEXT,
seen INTEGER DEFAULT 1,
created DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(first, last)
);
-- 创建临时表用于导入最新CSV数据
CREATE TEMP TABLE IF NOT EXISTS tmp_dump (
first TEXT,
last TEXT
);
.mode csv
.separator ";"
.import '$1.tmp' tmp_dump
-- 用INSERT OR REPLACE处理新增和更新：
-- 若记录不存在则插入（seen默认1），若存在则递增seen
INSERT OR REPLACE INTO dump (first, last, seen, created)
SELECT 
    t.first, 
    t.last, 
    COALESCE(d.seen, 0) + 1,
    COALESCE(d.created, CURRENT_TIMESTAMP)
FROM tmp_dump t
LEFT JOIN dump d ON t.first = d.first AND t.last = d.last;
-- 清理临时表
DROP TABLE tmp_dump;
EOF

关键逻辑说明 #

• 先创建临时表tmp_dump，用来导入最新的CSV数据（仅保留first和last字段，其他字段由主表维护）
• 通过LEFT JOIN关联主表dump，用COALESCE(d.seen, 0) + 1实现：已有记录的seen值加1，新记录则从0开始加1（等价于默认计数1）
• COALESCE(d.created, CURRENT_TIMESTAMP)确保新记录的created字段取当前时间，已有记录保留原创建时间
• INSERT OR REPLACE借助UNIQUE(first, last)约束，自动完成新增记录插入、已有记录更新的操作

额外优化点 #

• 给seen字段添加DEFAULT 1，确保新插入的记录默认计数为1
• 临时表使用后立即删除，避免占用数据库资源

内容的提问来源于stack exchange，提问作者Random Data Monster