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('A', 'B');