Home  >  Article  >  Database  >  MySQL event实现定时建表小记

MySQL event实现定时建表小记

WBOY
WBOYOriginal
2016-06-07 14:59:20992browse

MySQL event实现定时建表小记 每天凌晨一点建一个第二天的表DELIMITER // CREATE PROCEDURE test.create_tab_every_day() BEGIN

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 ;

本文永久更新链接地址

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn