使用MySQL定时任务 动态增加分区

一、查看定时策略是否开启

show variables like '%event_sche%';

• 开启定时策略：

set global event_scheduler = on;

二、创建存储过程

• 增加分区表存储过程

• t_name 传入增加分区的数据表

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);#下月月底