使用MySQL定时任务 动态增加分区
一、查看定时策略是否开启
show variables like '%event_sche%';
- 开启定时策略：
set global event_scheduler = on;
二、创建存储过程
- 增加分区表存储过程
CREATE DEFINER=`test`@`%` PROCEDURE `p_partition_month`(in t_name VARCHAR(50))
BEGIN
DECLARE v_sysdate DATE;
DECLARE v_maxdate DATETIME;
DECLARE v_pt VARCHAR(20);
DECLARE v_maxval int;
DECLARE add_sql VARCHAR(256);
SELECT MAX(CAST(FROM_DAYS(REPLACE(partition_description,'''','')) AS DATE)) AS val INTO v_maxdate FROM information_schema.`PARTITIONS` WHERE table_name = t_name;
SET v_sysdate = SYSDATE();
select v_maxdate,v_maxdate+ INTERVAL 1 MONTH;#
WHILE v_maxdate<(v_sysdate+INTERVAL 1 MONTH) DO
SET v_pt = DATE_FORMAT(v_maxdate, '%Y%m%d');
SET v_maxval=TO_DAYS(v_maxdate+ INTERVAL 1 MONTH);
SET add_sql = CONCAT('alter table ', t_name,' add partition (partition p', v_pt, ' values less than(',v_maxval,'))');
SET @sql=add_sql; #存储于会话变量
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET v_maxdate = v_maxdate + INTERVAL 1 MONTH;
END WHILE;
END
三、创建定时任务
- 创建（任务名e_partition）定时任务事件，每月月底 23 点执行存储过程
CREATE EVENT e_partition
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(last_day(curdate()), INTERVAL 1 MONTH),INTERVAL 23 HOUR)
ON COMPLETION PRESERVE ENABLE
DO CALL p_partition_month();
- 删除定时事件
drop event e_partition;
四、定时任务相关操作
- 查看
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
- 开启
alter event run_event on completion preserve enable;
- 关闭
alter event run_event on completion preserve disable;
五、定时规则
周期执行(EVERY)
单位有：second、minute、hour、day、week(周)、quarter(季度)、month、year
on schedule every 1 month//每月执行1次
在具体某个时间执行(AT)
on schedule at current_timestamp()+interval 5 day //5天后执行
on schedule at ‘2023-01-01 00:00:00’ //在2023年1月1日，0点整执行
在某个时间段执行(STARTS ENDS)
on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month //5天后开始每天都执行执行到下个月底
on schedule every 1 day ends current_timestamp()+interval 5 day //从现在起每天执行，执行5天
六、扩展
1、分区操作
- 创建分区
ALTER TABLE `qfyu_chat` PARTITION BY RANGE(TO_DAYS(create_time)) (
PARTITION p20221101 VALUES LESS THAN (TO_DAYS('2022-12-01')) ENGINE = INNODB,
PARTITION p20221201 VALUES LESS THAN (TO_DAYS('2023-01-01')) ENGINE = INNODB,
);
- 修改分区
alter table qfyu_chatadd partition (partition p20221101 values less than(738641))
- 删除分区（包含数据）
alter table qfyu_chat drop partition p20221101
2、常用 mysql 日期函数
SELECT DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY); #当月1号
select last_day(curdate());#当月月底最后一天
select DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH); #下月1号
select DATE_ADD(last_day(curdate()), INTERVAL 1 MONTH); #下月1号
SELECT DATE_ADD(DATE_ADD(last_day(curdate()), INTERVAL 1 MONTH),INTERVAL 23 HOUR);#下月月底
