mysql - 定时计划分表，预创建第二天的表

1 创建存储过程：copy 存在的空表，预创建第二天的表，并删除 7 天前的表

CREATE PROCEDURE create_monitor_log_table_every_day()
BEGIN
-- 预先创建第二天的表
SET @nowDateStr = DATE_FORMAT(date_add(curdate(),interval 1 DAY),"%Y%m%d");
SET @createStr = CONCAT("create table if not EXISTS monitor_log_" ,@nowDateStr," like monitor_log");
PREPARE stmt FROM @createStr;
EXECUTE stmt;

-- 删除7天前的存储
SET @nowDateStr = DATE_FORMAT(SUBDATE(curdate(),interval 7 DAY),"%Y%m%d");
SET @createStr = CONCAT("drop table if EXISTS monitor_log_" ,@nowDateStr);
PREPARE stmt FROM @createStr;
EXECUTE stmt;
END;

1.2 如果报错：[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_gr...

修改 MySQL mysql.ini 配置文件，在 [mysqld] 下面添加：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

重启 mysql，重新执行即可。

2 创建 MySQL 任务计划，每天执行

-- 创建定时任务
-- 以$$作为分隔符
DELIMITER $$
SET GLOBAL event_scheduler = 1;  
-- 事件名
CREATE EVENT event_build_table_every_day
-- 间隔一天
ON SCHEDULE EVERY 1 DAY
-- 第一次执行时间
STARTS NOW() 
ON  COMPLETION  PRESERVE  
ENABLE  
DO  
BEGIN  
-- 要调用的函数或者存储过程
CALL create_monitor_log_table_every_day();  
-- CALL create_monitor_table_every_day();  
END $$  
-- 将分隔符重置为;
DELIMITER ;