文章提供了一个MySQL存储过程的例子，用于动态创建一系列的日志表，这些表的结构相同但后缀按年月变化，用于日志存储的分表策略。存储过程可以创建指定数量的表，例如两年内的24个月份。此外，还提到了旧版本的存储过程示例，包括创建和删除多张表以及多个数据库的示例。
mysql一次创建多张表，表结构相同，表名后缀为年月
最近有个需求，由于日志表很大，要求日志表按月分表，产生的日志根据当前时间确定要存入的表名中。为了保险起见，确定手动创建表，先创建两年的表，即24张表，表名如：rz_interfacelog202008,rz_interfacelog202009。
1.创建存储过程：
DELIMITER $$
CREATE PROCEDURE `createInterfaceTables`()
begin
declare i int;
set i=0;
while i<24 do
set @tb = CONCAT('CREATE TABLE IF NOT EXISTS rz_interfacelog',date_format(date_add(curdate(), interval i month),'%Y%m'),
"( `id` varchar(32) NOT NULL,
`phone` varchar(50) NULL DEFAULT NULL COMMENT '手机号',
`createTime` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`parameter` varchar(3000) NULL DEFAULT NULL COMMENT '接口入参',
`paraEncrypt` varchar(3000) NULL DEFAULT NULL COMMENT '加密后的参数',
`operate` varchar(30) NULL DEFAULT NULL COMMENT '操作',
`resultCode` varchar(10) NULL DEFAULT NULL COMMENT '操作结果Code',
`result` varchar(2000) NULL DEFAULT NULL COMMENT '加密的操作结果',
`resultEncrypt` varchar(2000) NULL DEFAULT NULL COMMENT '未加密的操作结果',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ROW_FORMAT = Dynamic;");
PREPARE create_stmt FROM @tb;
EXECUTE create_stmt;
set i=i+1;
end while;
end $$;
DELIMITER ;
2.执行之后会在函数中出现createLogTables这个函数。
3.执行createLogTables：
call createInterfaceTables();
循环建表_循环创建多张表 和 多个数据库sql语句（数据库水平分库分表时方便）
循环创建多张表 和 多个数据库sql语句(数据库水平分库分表时方便)
----------------------靓丽的分割线 结束---------------------------------------
============靓丽的分割线 以下为老版本 不可以动态传参数 仅供参考==============
-----------------------------MySQL动态创建多张表-----------------------------------------------
说明：1、修改SET table_pre='score_stream_' 为你需要的数据库名称前缀
2、修改SET i=1 为你需要开始的数量后缀(最小后缀)
3、修改WHILE i<16 为你最大的数量后缀(最大后缀)
call create_tables();
CREATE PROCEDURE `create_tables`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE table_pre VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=1;
SET table_name='';
SET table_pre='score_stream_';
SET sql_text='';
WHILE i<16 DO
IF i<10 THEN SET table_name=CONCAT(table_pre,'0',i);
ELSE SET table_name=CONCAT(table_pre,i);
END IF;
SET sql_text=CONCAT('CREATE TABLE ', table_name, '(
id VARCHAR(32) NOT NULL,
user_id VARCHAR(32),
direction VARCHAR(8) ,
operate_score INT(11),
operator VARCHAR(32) ,
operate_time DATETIME,
score_status INT(4) ,
score_rule_id VARCHAR(32) DEFAULT NULL ,
member_level INT(4) DEFAULT NULL ,
score_type INT(4) ,
product_id VARCHAR(32) DEFAULT NULL ,
remark VARCHAR(256) DEFAULT NULL ,
operate_source INT(4) DEFAULT NULL ,
gmt_modify DATETIME NOT NULL,
gmt_create DATETIME NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8' );
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END
-----------------------------MySQL动态创建多个数据库-----------------------------------------------
说明：1、修改SET table_pre='score_stream_' 为你需要的数据库名称前缀
2、修改SET i=1 为你需要开始的数量后缀(最小后缀)
3、修改WHILE i<16 为你最大的数量后缀(最大后缀)
call create_databases();
CREATE PROCEDURE `create_databases`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE table_pre VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=1;
SET table_name='';
SET table_pre='score_stream_';
SET sql_text='';
WHILE i<16 DO
IF i<10 THEN SET table_name=CONCAT(table_pre,'0',i);
ELSE SET table_name=CONCAT(table_pre,i);
END IF;
SET sql_text=CONCAT('CREATE DATABASE ', table_name, '' );
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END
-----------------------------MySQL动态删除多个数据库-----------------------------------------------
说明：具体参数可以根据情况调整
CREATE PROCEDURE `create_databases`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE table_pre VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=1;
SET table_name='';
SET table_pre='score_stream_';
SET sql_text='';
WHILE i<16 DO
IF i<10 THEN SET table_name=CONCAT(table_pre,'0',i);
ELSE SET table_name=CONCAT(table_pre,i);
END IF;
SET sql_text=CONCAT('CREATE DATABASE ', table_name, '' );
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
-----------------------------MySQL动态删除多个数据表-----------------------------------------------
CREATE PROCEDURE `delete_tables`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE table_pre VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=0;
SET table_name='';
SET table_pre='score_stream_';
SET sql_text='';
WHILE i<1024 DO
SET table_name=CONCAT(table_pre,i);
SET sql_text=CONCAT('drop TABLE ', table_name, '' );
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
---------------------------------OK创建所有数据库中的表-------------------------------------------
调用：CALL create_tables_of_databases2('znke_z_log_', 'log' ,'znke_z_log.log' ,0 , 128);
参数说明：
1、目标数据库的前缀
2、要创建到目标数据库中的表名称
3、源数据库名称.源表名称
4、目标数据库后缀最小数
5、目标数据库后缀最大数(数据库总数量)
CREATE PROCEDURE `create_tables_of_databases2`(IN db_pre VARCHAR(20),IN table_name VARCHAR(20),IN source_db_and_tableName VARCHAR(30),IN min_num INT,IN max_num INT)
BEGIN
DECLARE i INT;
DECLARE db_name VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=min_num;
SET db_name='';
SET sql_text='';
WHILE i
SET db_name=CONCAT(db_pre,i);
SET sql_text=CONCAT('create table ', db_name , '.' ,table_name ,' like ' ,source_db_and_tableName);
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
---------------------------------OK删除所有数据库中的表-------------------------------------------
调用：CALL delete_tables_of_databases('znke_z_log_', 'log' ,0 , 128);
参数说明：
1、目标数据库的前缀
2、要创建到目标数据库中的表名称
3、目标数据库后缀最小数
4、目标数据库后缀最大数(数据库总数量)
CREATE PROCEDURE `delete_tables_of_databases`(IN db_pre VARCHAR(20),IN table_name VARCHAR(20),IN min_num INT,IN max_num INT)
BEGIN
DECLARE i INT;
DECLARE db_name VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=min_num;
SET db_name='';
SET sql_text='';
WHILE i
SET db_name=CONCAT(db_pre,i);
SET sql_text=CONCAT('drop table ', db_name , '.' ,table_name);
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
循环创建多张表 和 多个数据库sql语句（数据库水平分库分表时方便）
关键字：循环创建多张表 和 多个数据库sql语句（数据库水平分库分表时方便）
提示：创建库或表时可以用存储过程 删除库或表时也可以用存储过程 方便又省时
---------------------------------创建所有数据库-------------------------------------------
调用：call create_databases('znke_z_log_', 0, 128);
参数说明：
1、要创建的数据库前缀
2、目标数据库后缀最小数
3、目标数据库后缀最大数（数据库总数量）
CREATE `create_databases`(IN db_pre VARCHAR(20),IN min_num INT,IN max_num INT)
BEGIN
DECLARE i INT;
DECLARE db_name VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=min_num;
SET db_name='';
SET sql_text='';
WHILE i<max_num DO
SET db_name=CONCAT(db_pre,i);
SET sql_text=CONCAT('CREATE DATABASE ', db_name, '' );
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
---------------------------------删除所有数据库-------------------------------------------
调用：call delete_databases('znke_z_log_', 0, 128);
参数说明：
1、要删除的数据库前缀
2、目标数据库后缀最小数
3、目标数据库后缀最大数（数据库总数量）
CREATE `delete_databases`(IN db_pre VARCHAR(20),IN min_num INT,IN max_num INT)
BEGIN
DECLARE i INT;
DECLARE db_name VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=min_num;
SET db_name='';
SET sql_text='';
WHILE i<max_num DO
SET db_name=CONCAT(db_pre,i);
SET sql_text=CONCAT('drop DATABASE ', db_name, '' );
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
---------------------------------创建所有数据库中的表-------------------------------------------
调用：CALL create_tables_of_databases('znke_z_log_', 'log' ,'znke_z_log.log' ,0 , 128);
参数说明：
1、目标数据库的前缀
2、要创建到目标数据库中的表名称
3、源数据库名称.源表名称(原理从已创建的库中复制，所以先要在源数据库中创建好设计好的表结构)
4、目标数据库后缀最小数
5、目标数据库后缀最大数（数据库总数量）
CREATE PROCEDURE `create_tables_of_databases`(IN db_pre VARCHAR(20),IN table_name VARCHAR(20),IN source_db_and_tableName VARCHAR(30),IN min_num INT,IN max_num INT)
BEGIN
DECLARE i INT;
DECLARE db_name VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=min_num;
SET db_name='';
SET sql_text='';
WHILE i<max_num DO
SET db_name=CONCAT(db_pre,i);
SET sql_text=CONCAT('create table ', db_name , '.' ,table_name ,' like ' ,source_db_and_tableName);
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
---------------------------------删除所有数据库中的表-------------------------------------------
调用：CALL delete_tables_of_databases('znke_z_log_', 'log' ,0 , 128);
参数说明：
1、目标数据库的前缀
2、要创建到目标数据库中的表名称
3、目标数据库后缀最小数
4、目标数据库后缀最大数（数据库总数量）
CREATE PROCEDURE `delete_tables_of_databases`(IN db_pre VARCHAR(20),IN table_name VARCHAR(20),IN min_num INT,IN max_num INT)
BEGIN
DECLARE i INT;
DECLARE db_name VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=min_num;
SET db_name='';
SET sql_text='';
WHILE i<max_num DO
SET db_name=CONCAT(db_pre,i);
SET sql_text=CONCAT('drop table ', db_name , '.' ,table_name);
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
---------------------------------创建数据库中所有分表（单库多表 分片模式）-------------------------------------------
调用：call create_tables('alibaba_db', 'user_', 0 ,128);
参数说明：
1、目标数据库（在该数据库中建立很多分表）
2、要创建到目标数据库中的表前缀
3、目标数据库后缀最小数
4、目标数据库后缀最大数（数据库总数量）
CREATE `create_tables`(IN db_name VARCHAR(20),IN table_pre VARCHAR(20), IN min_num INT,IN max_num INT)
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=min_num;
SET table_name='';
SET sql_text='';
WHILE i<max_num DO
SET table_name=CONCAT(table_pre,i);
SET sql_text=CONCAT('CREATE TABLE ',db_name ,'.',table_name, '(
id VARCHAR(32) NOT NULL,
user_id VARCHAR(32),
direction VARCHAR(8) ,
operate_score INT(11),
operator VARCHAR(32) ,
operate_time DATETIME,
score_status INT(4) ,
score_rule_id VARCHAR(32) DEFAULT NULL ,
member_level INT(4) DEFAULT NULL ,
score_type INT(4) ,
product_id VARCHAR(32) DEFAULT NULL ,
remark VARCHAR(256) DEFAULT NULL ,
operate_source INT(4) DEFAULT NULL ,
gmt_modify DATETIME NOT NULL,
gmt_create DATETIME NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8' );
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
---------------------------------删除数据库中所有分表（单库多表 分片模式）-------------------------------------------
调用：call delete_tables('alibaba_db', 'user_', 0 ,128);
参数说明：
1、目标数据库（在该数据库中建立很多分表）
2、要创建到目标数据库中的表前缀
3、目标数据库后缀最小数
4、目标数据库后缀最大数（数据库总数量）
CREATE `delete_tables`(IN db_name VARCHAR(20),IN table_pre VARCHAR(20), IN min_num INT,IN max_num INT)
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=min_num;
SET table_name='';
SET sql_text='';
WHILE i<max_num DO
SET table_name=CONCAT(table_pre,i);
SET sql_text=CONCAT('drop table ', db_name , '.' ,table_name);
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
[color=red]----------------------靓丽的分割线 结束---------------------------------------[/color]
[color=red]============靓丽的分割线 以下为老版本 不可以动态传参数 仅供参考==============[/color]
-----------------------------MySQL动态创建多张表-----------------------------------------------
说明：1、修改SET table_pre='score_stream_' 为你需要的数据库名称前缀
2、修改SET i=1 为你需要开始的数量后缀（最小后缀）
3、修改WHILE i<16 为你最大的数量后缀（最大后缀）
call create_tables();
CREATE PROCEDURE `create_tables`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE table_pre VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=1;
SET table_name='';
SET table_pre='score_stream_';
SET sql_text='';
WHILE i<16 DO
IF i<10 THEN SET table_name=CONCAT(table_pre,'0',i);
ELSE SET table_name=CONCAT(table_pre,i);
END IF;
SET sql_text=CONCAT('CREATE TABLE ', table_name, '(
id VARCHAR(32) NOT NULL,
user_id VARCHAR(32),
direction VARCHAR(8) ,
operate_score INT(11),
operator VARCHAR(32) ,
operate_time DATETIME,
score_status INT(4) ,
score_rule_id VARCHAR(32) DEFAULT NULL ,
member_level INT(4) DEFAULT NULL ,
score_type INT(4) ,
product_id VARCHAR(32) DEFAULT NULL ,
remark VARCHAR(256) DEFAULT NULL ,
operate_source INT(4) DEFAULT NULL ,
gmt_modify DATETIME NOT NULL,
gmt_create DATETIME NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8' );
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END
-----------------------------MySQL动态创建多个数据库-----------------------------------------------
说明：1、修改SET table_pre='score_stream_' 为你需要的数据库名称前缀
2、修改SET i=1 为你需要开始的数量后缀（最小后缀）
3、修改WHILE i<16 为你最大的数量后缀（最大后缀）
call create_databases();
CREATE PROCEDURE `create_databases`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE table_pre VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=1;
SET table_name='';
SET table_pre='score_stream_';
SET sql_text='';
WHILE i<16 DO
IF i<10 THEN SET table_name=CONCAT(table_pre,'0',i);
ELSE SET table_name=CONCAT(table_pre,i);
END IF;
SET sql_text=CONCAT('CREATE DATABASE ', table_name, '' );
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END
-----------------------------MySQL动态删除多个数据库-----------------------------------------------
说明：具体参数可以根据情况调整
CREATE PROCEDURE `create_databases`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE table_pre VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=1;
SET table_name='';
SET table_pre='score_stream_';
SET sql_text='';
WHILE i<16 DO
IF i<10 THEN SET table_name=CONCAT(table_pre,'0',i);
ELSE SET table_name=CONCAT(table_pre,i);
END IF;
SET sql_text=CONCAT('CREATE DATABASE ', table_name, '' );
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
-----------------------------MySQL动态删除多个数据表-----------------------------------------------
CREATE PROCEDURE `delete_tables`()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
DECLARE table_pre VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=0;
SET table_name='';
SET table_pre='score_stream_';
SET sql_text='';
WHILE i<1024 DO
SET table_name=CONCAT(table_pre,i);
SET sql_text=CONCAT('drop TABLE ', table_name, '' );
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
---------------------------------OK创建所有数据库中的表-------------------------------------------
调用：CALL create_tables_of_databases2('znke_z_log_', 'log' ,'znke_z_log.log' ,0 , 128);
参数说明：1、目标数据库的前缀 2、要创建到目标数据库中的表名称 3、源数据库名称.源表名称 4、目标数据库后缀最小数 5、目标数据库后缀最大数（数据库总数量）
CREATE PROCEDURE `create_tables_of_databases2`(IN db_pre VARCHAR(20),IN table_name VARCHAR(20),IN source_db_and_tableName VARCHAR(30),IN min_num INT,IN max_num INT)
BEGIN
DECLARE i INT;
DECLARE db_name VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=min_num;
SET db_name='';
SET sql_text='';
WHILE i<max_num DO
SET db_name=CONCAT(db_pre,i);
SET sql_text=CONCAT('create table ', db_name , '.' ,table_name ,' like ' ,source_db_and_tableName);
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
---------------------------------OK删除所有数据库中的表-------------------------------------------
调用：CALL delete_tables_of_databases('znke_z_log_', 'log' ,0 , 128);
参数说明：1、目标数据库的前缀 2、要创建到目标数据库中的表名称 3、目标数据库后缀最小数 4、目标数据库后缀最大数（数据库总数量）
CREATE PROCEDURE `delete_tables_of_databases`(IN db_pre VARCHAR(20),IN table_name VARCHAR(20),IN min_num INT,IN max_num INT)
BEGIN
DECLARE i INT;
DECLARE db_name VARCHAR(20);
DECLARE sql_text VARCHAR(2000);
SET i=min_num;
SET db_name='';
SET sql_text='';
WHILE i<max_num DO
SET db_name=CONCAT(db_pre,i);
SET sql_text=CONCAT('drop table ', db_name , '.' ,table_name);
SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i=i+1;
END WHILE;
END;
1343
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
