mysql存储过程批量建表

#创建 global_city_temperature_1_1 --------global_city_temperature_5_4 共创建20个表

 DROP PROCEDURE
 IF
     EXISTS create_temperature_tables;
 
 DELIMITER;;
 CREATE PROCEDURE create_temperature_tables () BEGIN
     DECLARE
         countryLimit INT UNSIGNED DEFAULT 1;
     DECLARE
         cityLimit INT UNSIGNED DEFAULT 1;
     WHILE
             countryLimit <= 5 DO
         WHILE
                 cityLimit <= 4 DO
                 
                 SET @table_name = concat( 'global_city_temperature_', countryLimit, '_', cityLimit );
             
             SET @create_table_sql = concat( 'create table ', @table_name, ' (
                 `tid` bigint UNSIGNED NOT NULL,
                 `station` bigint NULL DEFAULT NULL,
                 `date` date NULL DEFAULT NULL,
                 `latitude` double NULL DEFAULT NULL,
                 `longitude` double NULL DEFAULT NULL,
                 `elevation` float NULL DEFAULT NULL,
                 `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                 `temp` float NULL DEFAULT NULL,
                 `temp_attributes` tinyint NULL DEFAULT NULL,
                 `dewp` float NULL DEFAULT NULL,
                 `dewp_attributes` tinyint NULL DEFAULT NULL,
                 `slp` float NULL DEFAULT NULL,
                 `slp_attributes` tinyint NULL DEFAULT NULL,
                 `stp` float NULL DEFAULT NULL,
                 `stp_attributes` tinyint NULL DEFAULT NULL,
                 `visib` float NULL DEFAULT NULL,
                 `visib_attributes` tinyint NULL DEFAULT NULL,
                 `wdsp` float NULL DEFAULT NULL,
                 `wdsp_attributes` tinyint NULL DEFAULT NULL,
                 `mxspd` float NULL DEFAULT NULL,
                 `gust` float NULL DEFAULT NULL,
                 `max` float NULL DEFAULT NULL,
                 `max_attributes` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                 `min` float NULL DEFAULT NULL,
                 `min_attributes` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                 `prcp` float NULL DEFAULT NULL,
                 `prcp_attributes` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                 `sndp` float NULL DEFAULT NULL,
                 `city_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                 `country_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                 `frshtt` int NULL DEFAULT NULL,
                 `year` int NULL DEFAULT NULL,
                 PRIMARY KEY (`tid`) USING BTREE
             ) ENGINE = InnoDB  CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;' );
             PREPARE create_table_stmt 
             FROM
                 @create_table_sql;
             EXECUTE create_table_stmt;
             DEALLOCATE PREPARE create_table_stmt;
             
             SET cityLimit = cityLimit + 1;
             
         END WHILE;
         
         SET cityLimit = 1;
         
         SET countryLimit = countryLimit + 1;
         
     END WHILE;
     COMMIT;
     
 END;;
 DELIMITER;
   call create_temperature_tables();

 

批量改列字段类型

 DROP PROCEDURE
 IF
     EXISTS alter_temperature_tables;
 
 DELIMITER;;
 CREATE PROCEDURE alter_temperature_tables () BEGIN
     DECLARE
         countryLimit INT UNSIGNED DEFAULT 1;
     DECLARE
         cityLimit INT UNSIGNED DEFAULT 1;
     WHILE
             countryLimit <= 5 DO
         WHILE
                 cityLimit <= 4 DO
                 
                 SET @table_name = concat( 'global_city_temperature_', countryLimit, '_', cityLimit );
             
             SET @alter_table_sql = concat( 'alter table ', @table_name, '
                 modify `station` char(11) not NULL,
                 MODIFY `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                 modify `city_name` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL' );
             PREPARE alter_table_stmt 
             FROM
                 @alter_table_sql;
             EXECUTE alter_table_stmt;
             DEALLOCATE PREPARE alter_table_stmt;
             
             SET cityLimit = cityLimit + 1;
             
         END WHILE;
         
         SET cityLimit = 1;
         
         SET countryLimit = countryLimit + 1;
         
     END WHILE;
     COMMIT;
     
 END;;
 DELIMITER;   
 call alter_temperature_tables();