MySQL event实现定时建表小记

每天凌晨一点建一个第二天的表
DELIMITER //
?CREATE PROCEDURE test.create_tab_every_day()
?BEGIN
?DECLARE `@i` VARCHAR(15);
?DECLARE `@sqlstr` VARCHAR(2560);
?SET `@i` = DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 DAY),'%Y_%m_%d');
?SET @sqlstr = CONCAT(
?"CREATE TABLE day_tab_",
?`@i`,
?"(
? `prop` VARCHAR(40)
?);"
?);
?PREPARE stmt FROM @sqlstr;
?EXECUTE stmt;
?END;

?CALL test.create_tab_every_day();
?/*****DROP PROCEDURE test.create_tab_every_day;*****/
?/*****drop table test.day_tab_2015_09_11;*****/

?DELIMITER $$
?CREATE EVENT event_create_tab_days
?ON SCHEDULE EVERY 1 DAY
?STARTS '2015-09-10 01:00:00'
?ON? COMPLETION? PRESERVE
?ENABLE
?DO
?BEGIN
?CALL test.create_tab_every_day();
?END
?$$
?DELIMITER ;

每月的11号建一张下个月的表

DELIMITER //
?CREATE PROCEDURE test.create_tab_every_mon()
?BEGIN
?DECLARE `@i` VARCHAR(15);
?DECLARE `@sqlstr` VARCHAR(2560);
?SET `@i` = DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 MONTH),'%Y_%m');
?SET @sqlstr = CONCAT(
?"CREATE TABLE mon_tab_",
?`@i`,
?"(
? `prop` VARCHAR(40)
?);"
?);
?PREPARE stmt FROM @sqlstr;
?EXECUTE stmt;
?END;

?CALL test.create_tab_every_mon();
?/*****DROP PROCEDURE test.create_tab_every_mon;*****/
?/*****DROP TABLE test.mon_tab_2015_10*****/

?DELIMITER $$
?CREATE EVENT event_create_tab_mons
?ON SCHEDULE EVERY 1 MONTH
?STARTS '2015-09-11 01:00:00'
?ON? COMPLETION? PRESERVE
?ENABLE
?DO
?BEGIN
?CALL test.create_tab_every_mon();
?END
?$$
?DELIMITER ;