mysql event表_MySQL event实现定时建表小记

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 ;