使用 MySQL Scheduler 和 Event 周期性创建数据表,下面提供的是按月建表计划任务及事件通过 ON SCHEDULE EVERY 1 MINUTE 语句完成。
DELIMITER $$
DROP PROCEDURE IF EXISTS `auto_create_schedule_log_monthly`$$
CREATE /*DEFINER=`root`@`localhost`*/ PROCEDURE `auto_create_schedule_log_monthly`()
COMMENT "每月 1号创建下个月推广曝光日志表"
BEGIN
SET @table_name = CONCAT("schedule_log_", DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y%m'));
SET @create_table_sql = CONCAT("CREATE TABLE ", @table_name, " ("
, "`id` int(11) unsigned NOT NULL AUTO_INCREMENT,"
, "`log` varchar(32) NOT NULL COMMENT '日志'"
, ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4-unicode-ci COMMENT '投放曝光日志表'");
PREPARE auto_create_table FROM @create_table_sql;
EXECUTE auto_create_table;
DEALLOCATE PREPARE auto_create_table;
END$$
DELIMITER ;
-- ---------------------------------------------------------------------
DELIMITER $$
SET GLOBAL event_scheduler = ON$$
CREATE /*DEFINER=`root`@`localhost`*/ EVENT `auto_create_schedule_log_monthly`
ON SCHEDULE EVERY 1 MINUTE
STARTS '2018-08-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE
DO BEGIN
CALL auto_create_schedule_log_monthly();
END$$
-- 查看系统所有存储过程
SHOW PROCEDURE STATUS;
-- 查看单个存储过程定义
SHOW CREATE PROCEDURE auto_create_schedule_log_monthly;
DROP PROCEDURE IF EXISTS `auto_create_schedule_log_monthly`
-- 查看全部事件
SHOW EVENTS;
-- 查看事件定义
SHOW CREATE EVENT `auto_create_schedule_log_monthly` \G;
DROP EVENT IF EXISTS auto_create_schedule_log_monthly;