MySQL分表同步

MeteorCat2026-06-112026-06-11

目前大部分数据库表都有涉及到分表功能, 而比较常见的就是取模分表法, 但是手动每次修改表就很麻烦

比如同步增加/删除/修改一个字段都要从 0~9 的手动调整修改过去, 如果一不小心误操作后果也很严重

所以推荐采用自动化的存储进程来执行这部分功能, 首先是示范下怎么创建表

-- 创建模板表, 这里假设要创建这样模板表
-- 这张表不要做任何的数据操作, 只是作为模板即可
create table if not exists nova_user_t
(
    # 通用信息
    uid         bigint       not null comment '数据主键, 用于标识递增(实际上会从 Redis 之类以游戏 APP_ID 分组来全局递增)',
    app_id      bigint       not null comment '对应的应用ID',
    channel_id  bigint       not null default '0' comment '绑定渠道ID, 对应 nova_channel_info.channel_id',


    # 基础信息
    username    varchar(255) not null comment '账号信息, 对于自己官方渠道就是直接用户设置账户名, 对于第三方渠道就是写入返回的用户唯一标识',
    password    varchar(255) not null comment '密码信息, 对于自己官方渠道注册就是 Bcrypt 处理的密码, 对于第三方渠道就是下发 token 凭据之类, 方便做更新跟踪',
    nickname    varchar(255) not null comment '用户展示的名称, 默认为 username, 后续可以扩展为被用户修改展示',
    status      tinyint      not null default '0' comment '数据状态, 默认为0代表可用, 不为0代表可能被封禁等情况不允许登录',
    token       varchar(255) not null default '' comment '最后下发给用户的最新 TOKEN, 用于提供用户授权; 可以用传统 JWT, 也可以用单纯哈希',

    # 设备和系统信息
    device      varchar(16)  not null default '' comment '设备平台信息, 比如 win32 等, 浏览器通过 navigator.platform 可以采集',
    os          varchar(16)  not null default '' comment '系统信息, 用于可以采集到 Windows/Linux/Mac/iOS, 浏览器通过 navigator.userAgent 可以采集',


    # 其他信息, 部分可能需要认证身份证/性别/电子邮件/生日信息等信息, 这部分我暂时留空, 需要用到的时候会补充


    # 登录登出信息
    login_time  bigint       not null default '0' comment '登录时间',
    login_ip    varchar(64)  not null default '' comment '登录IP',
    logout_time bigint       not null default '0' comment '登出时间',
    logout_ip   varchar(64)  not null default '' comment '登出IP',


    # 其他创建|更新信息
    create_time bigint       not null comment '创建时间',
    create_ip   varchar(64)  not null comment '创建IP',
    update_time bigint       not null default '0' comment '更新时间',
    update_ip   varchar(64)  not null default '' comment '更新IP',

    key find_app (app_id),
    primary key (uid)
) comment '应用用户表, 需要采用分表保存, 建议采用取模算法散列在 nova_user_{0-9} 之中, 而主键ID必须采用Redis外部全局递增获取写入'
    engine = InnoDB
    charset = utf8mb4
    collate = utf8mb4_unicode_ci
;


-- 设置创建表存储过程, 若已存在同名存储过程就先删除
DROP PROCEDURE IF EXISTS create_user_sub_tables;
DELIMITER // -- 临时修改语句结束符避免冲突


-- 开始遍历获取
CREATE PROCEDURE create_user_sub_tables()
BEGIN
    DECLARE i INT DEFAULT 0;
-- 循环 0 ~ 9
    WHILE i < 10
        DO
            -- 拼接建表语句，基于模板表结构复制
            SET @sql = CONCAT(
                    'CREATE TABLE IF NOT EXISTS nova_user_', i,
                    ' LIKE nova_user_t;'
                       );
            -- 执行动态 SQL
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            SET i = i + 1;
        END WHILE;
END //

DELIMITER ; -- 恢复默认结束符 ;


-- 最后调用进程并删除, 创建表的调用过程基本只需要使用一次
CALL create_user_sub_tables();
DROP PROCEDURE IF EXISTS create_user_sub_tables;

这样就可以创建好了分表, 具体可以自行动态调整, 最好先在本地或者测试数据库试验下

后续就是修改和删除功能, 这部分也是利用存储进程处理, 使用起来非常方便

# 如果需要同步修改追加字段, 就需要用自定义的同步功能 -------------------------------------------------------------------

-- 创建修改功能的存储过程
DROP PROCEDURE IF EXISTS update_all_user_tables;
DELIMITER //


-- 设定从同步创建
CREATE PROCEDURE update_all_user_tables(IN ddl_template VARCHAR(2000))
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 10
        DO
            -- 把模板中的 {table} 替换为真实表名 nova_user_{取模ID}
            SET @sql = REPLACE(ddl_template, '{table}', CONCAT('nova_user_', i));
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SET i = i + 1;
        END WHILE;
END //

DELIMITER ;


-- 最后就可以同步调用
CALL update_all_user_tables('ALTER TABLE {table} ADD COLUMN email VARCHAR(255) NOT NULL DEFAULT "" COMMENT "邮件地址"');

-- 需要同步到模板表
ALTER TABLE nova_user_t
    ADD COLUMN email VARCHAR(255) NOT NULL DEFAULT '' COMMENT '邮件地址';

-- 这里删除下字段即可
CALL update_all_user_tables('ALTER TABLE {table} DROP COLUMN email');

-- 需要同步到模板表
ALTER TABLE nova_user_t
    DROP COLUMN email;

需要注意: 同步修改功能的时候要对模板表一起同步修改, 方便后续项目重新立项的时候可以直接使用