Mysql用事件和存储过程每月创建一张数据库表

业务需求每个月创建一张以recharge_gain_为前缀，日期年月为后缀的数据库表，比如：recharge_gain_201612

首先创建存储过程：

DELIMITER // CREATE PROCEDURE create_table_recharge_gain_every_mon() BEGIN DECLARE `@suffix` VARCHAR(15); DECLARE `@sqlstr` VARCHAR(2560); SET `@suffix` = DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 MONTH),'%Y_%m'); SET @sqlstr = CONCAT( "CREATE TABLE recharge_gain_", `@suffix`, "( `id` int(11) NOT NULL AUTO_INCREMENT, `currency` int(11) NOT NULL, `date` bigint(20) NOT NULL, `operation` int(11) NOT NULL, `server_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;" ); PREPARE stmt FROM @sqlstr; EXECUTE stmt; END; 然后创建事件每月28日执行上面的存储过程：

DELIMITER $$ SET GLOBAL event_scheduler = 1; CREATE EVENT event_create_table_every_month ON SCHEDULE EVERY 1 MONTH STARTS '2016-12-28 00:00:00' ON COMPLETION PRESERVE ENABLE DO BEGIN CALL create_table_recharge_gain_every_mon(); END