如何缩小SQLite数据库体积？解决数据库过大无法创建问题

1. 用批量插入替代单条循环插入 #

你当前的代码每次插入一条数据就执行commit，会产生大量事务日志和数据库碎片，直接导致体积膨胀。改用批量插入并统一提交事务，能大幅减少额外开销：

with open("resall.TXT", "r") as f:
    result = f.readlines()
# 预处理数据，注意清理count字段的空白字符
data = []
for i, (state, sex, year, forename, count) in enumerate(result):
    data.append((i, state, sex, year, forename, count.strip()))

import sqlite3 as sq3

con = sq3.connect("popular.db")
# 创建表（后续可结合数据类型优化）
con.execute("""
CREATE TABLE IF NOT EXISTS popname  (
    id INTEGER PRIMARY KEY,
    state TEXT,
    sex TEXT,
    year TEXT,
    forename TEXT,
    count INTEGER
);
""")
# 批量插入所有数据
con.executemany("INSERT INTO popname VALUES (?, ?, ?, ?, ?, ?);", data)
con.commit()
con.close()

2. 优化字段数据类型 #

当前表结构的部分字段可以用更紧凑的类型存储：

• year用INTEGER替代TEXT，年份是数字，占用空间仅为文本的1/2~1/3
• state如果是固定长度的州缩写（如两字符），用CHAR(2)替代TEXT
• sex是单字符标识，用CHAR(1)替代TEXT

修改后的表结构：

CREATE TABLE IF NOT EXISTS popname  (
    id INTEGER PRIMARY KEY,
    state CHAR(2),
    sex CHAR(1),
    year INTEGER,
    forename TEXT,
    count INTEGER
);

3. 启用WAL日志模式 #

默认的DELETE日志模式会生成较大的日志文件，切换到WAL模式能降低日志开销，同时提升写入效率：

con = sq3.connect("popular.db")
con.execute("PRAGMA journal_mode=WAL;")  # 开启WAL模式

4. 插入完成后执行VACUUM压缩 #

数据插入完成后，执行VACUUM命令可以清理数据库碎片、回收未使用的空间：

con.execute("VACUUM;")
con.commit()

注意：执行VACUUM需要临时存储空间（约等于当前数据库大小），完成后会生成紧凑的数据库文件。

5. 移除冗余主键 #

如果你的数据可以通过state+sex+year+forename组合唯一标识，可直接用这个组合作为主键，去掉额外的id列，减少每条记录的存储空间：

CREATE TABLE IF NOT EXISTS popname  (
    state CHAR(2),
    sex CHAR(1),
    year INTEGER,
    forename TEXT,
    count INTEGER,
    PRIMARY KEY (state, sex, year, forename)
);

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