如何合并SQL语句实现主从表批量插入，提升sqlite3性能？

前提：确保表结构约束 #

先确认两张表的约束配置，保证唯一性和关联有效性，这是后续批量操作的基础：

CREATE TABLE IF NOT EXISTS packages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS path_package (
    path TEXT NOT NULL PRIMARY KEY,
    package_id INTEGER NOT NULL REFERENCES packages(id)
);

• packages.name设为唯一约束，避免重复插入包名
• path_package.path设为主键，防止同一路径重复映射（若需同一路径对应多包，可改为复合主键或单独加唯一约束）

单条SQL实现原子插入 #

利用SQLite的INSERT OR IGNORE和CTE（公共表表达式），把「查包ID→插包（若不存在）→插路径映射」的逻辑合并为一条原子语句：

WITH inserted_package AS (
    INSERT OR IGNORE INTO packages (name) VALUES (?)
    RETURNING id
)
INSERT OR IGNORE INTO path_package (path, package_id)
VALUES (?, COALESCE((SELECT id FROM inserted_package), (SELECT id FROM packages WHERE name = ?)));

逻辑说明：

1. 尝试插入包名到packages，若已存在则忽略，返回新插入的ID
2. 插入路径映射时，优先用刚插入的包ID；若包已存在，则查询已有ID
3. INSERT OR IGNORE避免重复的路径映射，防止执行报错

Python中用executemany批量执行 #

把所有路径-包名对整理成参数列表，通过executemany批量提交，完全借助SQLite的C实现提升性能：

import sqlite3

def bulk_insert(conn, path_package_pairs):
    # 构造参数：每个元素为(path, 包名, 包名)，匹配SQL中的三个占位符
    params = [(path, pkg, pkg) for path, pkg in path_package_pairs]
    sql = """
    WITH inserted_package AS (
        INSERT OR IGNORE INTO packages (name) VALUES (?)
        RETURNING id
    )
    INSERT OR IGNORE INTO path_package (path, package_id)
    VALUES (?, COALESCE((SELECT id FROM inserted_package), (SELECT id FROM packages WHERE name = ?)));
    """
    conn.executemany(sql, params)
    conn.commit()

低内存与性能优化 #

• 分批次处理：百万级数据不要一次性加载到内存，拆成每批1000-10000条执行，降低内存占用
• 启用WAL模式：提升写入性能并减少磁盘碎片：

  PRAGMA journal_mode=WAL;
  

• 关闭自动提交：批量操作前关闭自动提交，减少磁盘IO次数
• 索引复用：依赖表结构自带的唯一索引（packages.name、path_package.path），避免额外创建索引增加磁盘开销

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