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
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.

调用

CALL SyncTableStructure('dev_log', 'dev_log_city_2024');
  • 1.

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

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
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.
  • 63.
  • 64.
  • 65.
  • 66.
  • 67.
  • 68.
  • 69.
  • 70.
  • 71.
  • 72.
  • 73.
  • 74.
  • 75.
  • 76.
  • 77.
  • 78.
  • 79.
  • 80.
  • 81.
  • 82.
  • 83.
  • 84.
  • 85.
  • 86.
  • 87.
  • 88.
  • 89.
  • 90.
  • 91.
  • 92.
  • 93.
  • 94.
  • 95.

如果这篇文章对你有用，可以关注本人微信公众号获取更多ヽ(^ω^)ﾉ ~

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