MySQL event to implement regular table creation note
MySQL event to implement regular table creation note
Create a table for the next day every morning
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 '2017-09-10 01:00:00'
ON COMPLETION PRESERVE
ENABLE
DO
BEGIN
CALL test. create_tab_every_day ();
END
$
DELIMITER;
Create a table for the next month on the 11th of each month
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 '2017-09-11 01:00:00'
ON COMPLETION PRESERVE
ENABLE
DO
BEGIN
CALL test. create_tab_every_mon ();
END
$
DELIMITER;
This article permanently updates the link address: