Mysql表结构同步存储过程(适用于模版表)

Mysql表结构同步

流水模版表新增字段需要同步到所有区域年份的流水表

CREATE DEFINER=`root`@`%` PROCEDURE `SyncTableStructure`(IN sourceTable VARCHAR(255), IN targetTable VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE columnName VARCHAR(255);
    DECLARE columnType VARCHAR(255);
    DECLARE isNullable VARCHAR(3);
    DECLARE columnDefault TEXT;
    DECLARE extra VARCHAR(255);

    -- 游标查询源表的列结构
    DECLARE columnCursor CURSOR FOR
        SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = sourceTable;

    -- 当遍历完所有行时，关闭游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 打开游标
    OPEN columnCursor;

    read_loop: LOOP
        -- 读取游标中的一行
        FETCH columnCursor INTO columnName, columnType, isNullable, columnDefault, extra;
        
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 检查目标表中是否存在该列
        IF NOT EXISTS (
            SELECT * FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = targetTable AND COLUMN_NAME = columnName
        ) THEN
            -- 构建ALTER TABLE语句，添加新列
            SET @alterSQL = CONCAT('ALTER TABLE ', targetTable, ' ADD COLUMN ', columnName, ' ', columnType);
            
            -- 处理可为空字段
            IF isNullable = 'NO' THEN
                SET @alterSQL = CONCAT(@alterSQL, ' NOT NULL');
            END IF;
            
            -- 处理默认值
            IF columnDefault IS NOT NULL THEN
                SET @alterSQL = CONCAT(@alterSQL, ' DEFAULT ', QUOTE(columnDefault));
            END IF;

            -- 处理额外属性（如AUTO_INCREMENT）
            IF extra != '' THEN
                SET @alterSQL = CONCAT(@alterSQL, ' ', extra);
            END IF;

            -- 执行ALTER TABLE语句
            PREPARE stmt FROM @alterSQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END LOOP;

    -- 关闭游标
    CLOSE columnCursor;
END

调用

CALL SyncTableStructure('dev_log', 'dev_log_city_2024');

一个模版结构同步到多个表上

CREATE DEFINER=`root`@`%` PROCEDURE `SyncDevDataTablesStructure`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE currentTable VARCHAR(255);
    DECLARE columnName VARCHAR(255);
    DECLARE columnType VARCHAR(255);
    DECLARE isNullable VARCHAR(3);
    DECLARE columnDefault TEXT;
    DECLARE extra VARCHAR(255);

    -- 硬编码的源表和目标表前缀
    DECLARE sourceTableName VARCHAR(255) DEFAULT 'dev_log';
    DECLARE tablePrefix VARCHAR(255) DEFAULT 'dev_log_';

    -- 定义游标，用于查找所有符合前缀的表
    DECLARE tableCursor CURSOR FOR
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE CONCAT(tablePrefix, '%');

    -- 定义游标，用于查找源表的列结构
    DECLARE columnCursor CURSOR FOR
        SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = sourceTableName;

    -- 处理游标读取结束的情况
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 调试输出 sourceTableName 的值
    SELECT CONCAT('sourceTableName: ', sourceTableName) AS debug_output;

    -- 打开表游标
    OPEN tableCursor;

    table_loop: LOOP
        -- 获取一个表名
        FETCH tableCursor INTO currentTable;

        -- 调试输出 currentTable 的值
        SELECT CONCAT('tableName: ', currentTable) AS debug_output;

        IF done THEN
            LEAVE table_loop;
        END IF;

        -- 打开列游标
        SET done = 0;
        OPEN columnCursor;

        column_loop: LOOP
            -- 读取游标中的一行
            FETCH columnCursor INTO columnName, columnType, isNullable, columnDefault, extra;

            IF done THEN
                LEAVE column_loop;
            END IF;

            -- 检查目标表中是否存在该列
            IF NOT EXISTS (
                SELECT * FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = currentTable AND COLUMN_NAME = columnName
            ) THEN
                -- 构建ALTER TABLE语句，添加新列
                SET @alterSQL = CONCAT('ALTER TABLE ', currentTable, ' ADD COLUMN ', columnName, ' ', columnType);

                -- 处理可为空字段
                IF isNullable = 'NO' THEN
                    SET @alterSQL = CONCAT(@alterSQL, ' NOT NULL');
                END IF;

                -- 处理默认值
                IF columnDefault IS NOT NULL THEN
                    SET @alterSQL = CONCAT(@alterSQL, ' DEFAULT ', QUOTE(columnDefault));
                END IF;

                -- 处理额外属性（如AUTO_INCREMENT）
                IF extra != '' THEN
                    SET @alterSQL = CONCAT(@alterSQL, ' ', extra);
                END IF;

                -- 执行ALTER TABLE语句
                PREPARE stmt FROM @alterSQL;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
        END LOOP;

        -- 关闭列游标
        CLOSE columnCursor;
    END LOOP;

    -- 关闭表游标
    CLOSE tableCursor;
END