处理SQLite数据库重复记录的高效优雅方案

嘿，针对你百万级数据量下合并SQLite重复记录的需求，完全可以用纯SQL的方案搞定，比你之前用Python+Pandas拉数据处理的方式高效太多，而且全程在数据库内操作，避免了大量的IO和内存开销！

核心思路：用SQLite自带的聚合函数直接合并 #

SQLite内置了GROUP_CONCAT()函数，专门用来将分组后的字段值合并成逗号分隔的字符串，完美匹配你的需求。

方案一：直接生成去重合并后的新表（推荐，最高效） #

如果你的表没有其他需要保留的额外字段，或者可以接受替换原表，这个方法最快：

1. 先备份原表！（重要，防止操作失误）

CREATE TABLE FILE_INFO_BACKUP AS SELECT * FROM FILE_INFO;

2. 生成合并后的新表

CREATE TABLE FILE_INFO_NEW AS
SELECT 
    FILENAME,
    GROUP_CONCAT(LABEL_NUMBER, ',') AS LABEL_NUMBER
FROM FILE_INFO
GROUP BY FILENAME;

• 这里GROUP_CONCAT()会自动把同一个FILENAME下的所有LABEL_NUMBER合并成逗号分隔的字符串
• 如果需要保持标签的顺序，可以加ORDER BY：GROUP_CONCAT(LABEL_NUMBER, ',' ORDER BY LABEL_NUMBER)

3. 替换原表

DROP TABLE FILE_INFO;
ALTER TABLE FILE_INFO_NEW RENAME TO FILE_INFO;

方案二：原地更新原表（保留原表结构/其他字段） #

如果原表还有其他字段需要保留，不想重建表，可以用临时表+更新+删除的方式：

1. 创建临时表存储合并后的结果

CREATE TEMP TABLE FILE_INFO_MERGED AS
SELECT 
    FILENAME,
    GROUP_CONCAT(LABEL_NUMBER, ',') AS MERGED_LABELS
FROM FILE_INFO
GROUP BY FILENAME;

• 临时表会在数据库连接关闭后自动删除，不用担心残留

2. 更新每个文件的第一条记录为合并后的标签

UPDATE FILE_INFO
SET LABEL_NUMBER = (SELECT MERGED_LABELS FROM FILE_INFO_MERGED WHERE FILE_INFO.FILENAME = FILE_INFO_MERGED.FILENAME)
WHERE ROWID IN (
    SELECT MIN(ROWID) FROM FILE_INFO GROUP BY FILENAME
);

• 这里用MIN(ROWID)确保每个文件只保留最早的那条记录，更新它的标签为合并后的值

3. 删除所有重复的记录

DELETE FROM FILE_INFO
WHERE ROWID NOT IN (
    SELECT MIN(ROWID) FROM FILE_INFO GROUP BY FILENAME
);

为什么这个方案比你的原方法高效？ #

• 避免跨层数据传输：所有操作都在数据库内部完成，不用把百万级数据拉到Python内存里处理，省掉了巨大的IO开销和内存占用
• 数据库级优化：GROUP_CONCAT()是SQLite原生优化的聚合函数，处理大量数据的速度远快于Pandas的groupby+agg
• 减少数据库交互次数：原方法的循环更新会产生百万次数据库请求，而这个方案只需要几次SQL操作，效率提升几个数量级

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