MySQL 中批量创建表

发表于 更新于 分类于 阅读次数： 本文字数： 300 阅读时长 ≈ 1 分钟

当需要在 MySQL 数据库中创建表名类似于 log_20180101、log_20180102 这样基于日期的数据表时，可以使用下面的存储过程一次创建一个月的数据表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
delimiter //
CREATE procedure create_table()
BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(200); 
DECLARE table_pre VARCHAR(200); 
DECLARE sql_text VARCHAR(10000); 
SET i=1;
SET table_name='';
SET table_pre='log_201801';
SET sql_text='';

while i<32 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` int(11) NOT NULL AUTO_INCREMENT,
  `event_name` varchar(32) NOT NULL,
  `Log_Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8' );

SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;

SET i=i+1;

end while;
END;
call create_table();
drop procedure create_table;
//

也可以创建一个存储过程，然后通过事件调度每天 00:00:00 执行：

1
2
3
4
5
6
7
8
9
10
11
12
CREATE  PROCEDURE `create_log_table`()
BEGIN
	set @sqlstr=CONCAT('CREATE TABLE IF NOT EXISTS `log_',DATE_FORMAT(NOW(),'%Y%m%d'),'` (
		`id` int(10) AUTO_INCREMENT NOT NULL,`Gboard_UUID` varchar(32) NOT NULL,`Channel` varchar(4) NOT NULL,		
        `event_name` varchar(32) NOT NULL,
        `Log_Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY (`id`)
		)ENGINE=MyISAM DEFAULT CHARSET=utf8;');
  PREPARE create_stmt from @sqlstr;
  EXECUTE create_stmt;

END

事件

1
CREATE EVENT `create_log_daily` ON SCHEDULE EVERY 1 DAY STARTS '2018-01-01 00:00:00' ON COMPLETION PRESERVE ENABLE DO CALL create_log_table()

参考文档：
批量建立MySQL表
Mysql存储过程_一次性建立多张表