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