多租户数据库中多SQL Schema批量更新与未来同步方案咨询

针对独立Schema多租户架构下，Schema变更无法同步新旧租户的问题，核心解决思路是版本化管理所有Schema变更脚本，让新旧租户都通过统一的版本脚本链维护Schema一致性，具体实现步骤如下：

1. 重构Schema脚本为版本化增量库 #

把原来的单份初始化存储过程拆分为两类脚本：

• 基础初始化脚本（标记为v1.0）：对应原从零创建Schema的逻辑
• 增量变更脚本：每一次Schema修改都新增一个带版本号的独立脚本（比如v1.1_add_user_table.sql、v1.2_modify_order_status.sql）
• 所有脚本必须保证幂等性：比如用IF NOT EXISTS判断表/列是否存在，用ALTER TABLE ... ADD COLUMN IF NOT EXISTS等语法，避免重复执行报错

示例增量脚本：

-- v1.1_add_user_table.sql
CREATE TABLE IF NOT EXISTS {schema_name}.users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 维护租户Schema版本元数据表 #

在公共Schema（比如public或专门的tenant_meta）中创建一张表，记录每个租户的Schema当前版本：

CREATE TABLE tenant_schema_versions (
    tenant_id VARCHAR(50) PRIMARY KEY,
    schema_name VARCHAR(100) NOT NULL UNIQUE,
    current_version VARCHAR(20) NOT NULL DEFAULT '1.0',
    last_sync_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

新租户创建时，除了执行初始化脚本，还要向这张表插入记录，标记其初始版本为最新版本。

3. 批量同步现有租户的变更 #

编写批量同步逻辑（可以是数据库存储过程，也可以是应用端脚本），核心流程：

• 从tenant_schema_versions中拉取所有租户的Schema名称和当前版本
• 按版本号升序遍历增量脚本库，筛选出该租户未执行过的脚本
• 将脚本中的占位符{schema_name}替换为具体租户的Schema名称，执行脚本
• 执行完成后更新该租户在tenant_schema_versions中的current_version为最新版本

示例应用端伪代码（Python）：

def sync_all_existing_tenants():
    # 获取所有租户的版本信息
    tenants = db.query("SELECT schema_name, current_version FROM tenant_schema_versions")
    # 获取按版本升序排列的增量脚本
    change_scripts = load_sorted_change_scripts()  # 内部按版本号排序，比如v1.0 < v1.1 < v1.2
    latest_version = change_scripts[-1].version

    for tenant in tenants:
        schema_name = tenant["schema_name"]
        current_version = tenant["current_version"]
        # 执行所有高于当前版本的脚本
        for script in change_scripts:
            if script.version > current_version:
                # 替换Schema占位符
                sql_content = script.content.replace("{schema_name}", schema_name)
                # 执行SQL
                db.execute(sql_content)
        # 更新租户版本记录
        db.execute(f"""
            UPDATE tenant_schema_versions 
            SET current_version = '{latest_version}' 
            WHERE schema_name = '{schema_name}'
        """)

4. 新租户创建自动同步最新Schema #

修改原有的租户初始化流程：

1. 创建空的租户Schema
2. 执行基础初始化脚本（v1.0）
3. 按版本顺序执行所有增量变更脚本
4. 在tenant_schema_versions中插入该租户的版本记录，标记为最新版本
   这样新租户从创建之初就拥有最新的Schema结构，无需后续单独同步。

5. 日常变更规范 #

• 每次Schema变更必须新增独立的增量脚本，禁止直接修改原有初始化脚本
• 变更脚本先在测试环境的租户上验证通过，再推送到生产环境
• 生产环境执行批量同步时，建议分批处理（比如每次处理10个租户），避免数据库压力过载
• 同步过程中记录详细日志，包括每个租户的执行状态、报错信息，便于问题排查

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