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
如果这篇文章对你有用,可以关注本人微信公众号获取更多ヽ(^ω^)ノ ~
![微信公众号二维码](https://images.cnblogs.com/cnblogs_com/aeolian/1679458/o_wechat_gzh_qrcode.jpg)