如何用无匹配列的CSV文件更新SQLite的scores表score列？

先分析你之前操作的问题 #

你之前执行的insert into scores select score from new_scores是往表中新增行而非更新原行，完全偏离需求；再加上CSV表头是,score（第一列无列名），导入时没做好列映射，导致new_scores表的分数列未被正确读取，最终插入的全是空行。

核心思路：利用SQLite原生rowid关联，避免全表重写 #

只要你的scores表没有用WITHOUT ROWID创建，且从未手动修改过rowid（比如没有删除行后再插入导致rowid断层），SQLite原生的rowid就是按初始插入顺序生成的连续整数，正好可以和CSV里的行顺序一一对应，不用额外添加自增列浪费资源。

高效操作步骤 #

1. 优化SQLite运行配置（必做，大幅提升速度）

先执行以下命令减少IO开销，加快大表操作：

PRAGMA synchronous = OFF;
PRAGMA journal_mode = WAL;
PRAGMA cache_size = -2000000; -- 分配2GB内存缓存，可根据机器内存调整，单位为页，1页=4KB

2. 创建临时表存储新分数（带自增序号）

CREATE TEMP TABLE temp_new_scores (
    seq INTEGER PRIMARY KEY AUTOINCREMENT, -- 自动生成1、2、3...的序号，对应CSV行顺序
    score REAL
);

3. 正确导入CSV到临时表

因为CSV表头是,score（第一列无列名），导入时指定列映射：

.mode csv
.import --skip 1 new_scores.csv temp_new_scores(,score)

--skip 1表示跳过表头行，后面的(,score)表示忽略CSV第一列，将第二列对应到临时表的score列。

4. 批量生成新表替换原表（比直接UPDATE快10倍以上）

对于8000万行的超大型表，直接UPDATE会逐行写入，速度极慢。更高效的方式是创建包含新分数的完整新表，再替换原表：

-- 创建新表，复制原表结构和数据，替换score为新值
CREATE TABLE new_scores AS
SELECT 
    s.GROUP_ID, 
    s.GROUP_INDEX, 
    t.score AS SCORE
FROM scores s
JOIN temp_new_scores t ON s.rowid = t.seq;

-- 替换原表
DROP TABLE scores;
ALTER TABLE new_scores RENAME TO scores;

-- 重建原表的唯一索引（原表有该约束，必须重建）
CREATE UNIQUE INDEX idx_group ON scores(GROUP_ID, GROUP_INDEX);

特殊情况：原表rowid不连续（比如曾删除过行）

如果你的scores表因为删除操作导致rowid断层，无法直接对应CSV顺序，先给原表生成连续序号：

-- 生成带连续序号的临时表
CREATE TEMP TABLE temp_scores_seq AS
SELECT 
    *, 
    ROW_NUMBER() OVER (ORDER BY rowid) AS seq -- 按rowid顺序生成1、2、3...的连续序号
FROM scores;

-- 关联新分数生成新表
CREATE TABLE new_scores AS
SELECT 
    s.GROUP_ID, 
    s.GROUP_INDEX, 
    t.score AS SCORE
FROM temp_scores_seq s
JOIN temp_new_scores t ON s.seq = t.seq;

-- 后续替换原表、重建索引步骤同上

注意事项 #

• 操作前务必备份原数据库，避免数据丢失
• 确保磁盘有足够空间（至少是原表大小的1.5倍）
• 操作期间不要中断进程，否则可能导致数据库损坏

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