如何批量为SQLite数据库所有表添加自增主键ID列

操作前务必备份数据库文件，避免误操作导致数据丢失
你可以通过两种方式自动化完成所有表的改造，不需要逐表手动写SQL： #

方法1：纯SQLite命令行实现（无额外依赖，适合SQLite 3.16及以上版本） #

SQLite内置的sqlite_master系统表存储了所有用户表的元信息，你可以直接通过查询系统表自动生成所有改造SQL，一次性执行：

1. 打开终端进入sqlite3命令行，连接你的数据库：

   sqlite3 你的数据库文件路径.db
   

2. 执行以下命令，自动生成所有表的改造SQL并导出为脚本文件：

   .mode list
   .separator ''
   .output batch_update.sql
   SELECT 
       'CREATE TABLE `' || name || '`_new(id INTEGER PRIMARY KEY AUTOINCREMENT,' ||
       (SELECT GROUP_CONCAT('`' || name || '` ' || type || CASE WHEN "notnull"=1 THEN ' NOT NULL' ELSE '' END || CASE WHEN dflt_value IS NOT NULL THEN ' DEFAULT ' || dflt_value ELSE '' END, ',') 
        FROM pragma_table_info(name)) ||
       ');' || char(10) ||
       'INSERT INTO `' || name || '`_new(' ||
       (SELECT GROUP_CONCAT('`' || name || '`', ',') FROM pragma_table_info(name)) ||
       ') SELECT ' || (SELECT GROUP_CONCAT('`' || name || '`', ',') FROM pragma_table_info(name)) || ' FROM `' || name || '`;' || char(10) ||
       'DROP TABLE `' || name || '`;' || char(10) ||
       'ALTER TABLE `' || name || '`_new RENAME TO `' || name || '`;' || char(10)
   FROM sqlite_master 
   WHERE type='table' AND name NOT LIKE 'sqlite_%';
   .output stdout
   

3. 执行生成的脚本，完成所有表改造：

   .read batch_update.sql
   

方法2：Python脚本实现（兼容性最好，适配所有SQLite版本） #

如果你的SQLite版本较低不支持表值形式的pragma，或者需要更灵活的错误处理，可以直接用Python内置的sqlite3模块写脚本处理，不需要安装额外依赖：

import sqlite3
import os

# 配置你的数据库路径
DB_PATH = "your_database.db"

# 第一步：自动备份数据库
backup_path = f"{DB_PATH}.bak"
if os.path.exists(backup_path):
    os.remove(backup_path)
with open(DB_PATH, "rb") as src, open(backup_path, "wb") as dst:
    dst.write(src.read())
print(f"数据库已备份至: {backup_path}")

conn = sqlite3.connect(DB_PATH)
cur = conn.cursor()

# 获取所有用户表，排除系统表
cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
tables = [row[0] for row in cur.fetchall()]

for tbl in tables:
    print(f"正在处理表: {tbl}")
    # 读取原表列结构
    cur.execute(f"PRAGMA table_info(`{tbl}`)")
    cols = cur.fetchall()
    col_defs = []
    col_names = []
    for cid, cname, ctype, notnull, default, pk in cols:
        # 保留原列的类型、非空、默认值约束
        def_str = f" `{cname}` {ctype}"
        if notnull:
            def_str += " NOT NULL"
        if default is not None:
            def_str += f" DEFAULT {default}"
        col_defs.append(def_str)
        col_names.append(f"`{cname}`")
    
    new_tbl = f"`{tbl}_new`"
    old_tbl = f"`{tbl}`"
    # 拼接改造SQL
    sqls = [
        f"CREATE TABLE {new_tbl}(id INTEGER PRIMARY KEY AUTOINCREMENT, {', '.join(col_defs)})",
        f"INSERT INTO {new_tbl}({', '.join(col_names)}) SELECT {', '.join(col_names)} FROM {old_tbl}",
        f"DROP TABLE {old_tbl}",
        f"ALTER TABLE {new_tbl} RENAME TO {old_tbl}"
    ]

    try:
        cur.execute("BEGIN")
        for sql in sqls:
            cur.execute(sql)
        conn.commit()
        print(f"表 {tbl} 处理完成")
    except Exception as e:
        conn.rollback()
        print(f"表 {tbl} 处理失败，已回滚，错误: {str(e)}")

conn.close()
print("全部处理流程结束")

注意事项 #

• 处理大体积表时请预留至少1倍于当前数据库文件大小的磁盘空间，重建表过程会临时生成两份数据副本
• 上述逻辑和你手动处理单表的逻辑完全一致，不会修改原有列的任何数据
• 如果原表存在自定义索引、触发器，可以在脚本中额外读取sqlite_master中对应表的索引、触发器创建语句，替换表名后重新创建即可
• 处理完成后可随机抽取几张表执行SELECT * FROM 表名 LIMIT 10，确认id列自增正常、原有数据无缺失

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