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