每月自动创建表分区

慢慢走，看着

已于 2023-09-13 14:36:49 修改

阅读量704

点赞数 1

文章标签： mysql 数据库

于 2023-09-13 14:26:45 首次发布

本文链接：https://blog.csdn.net/hkk233/article/details/132852543

版权

创建存储过程

CREATE PROCEDURE table_partition_month1(in partition_table_name varchar(50))
BEGIN
  DECLARE v_sysdate DATE;   #当前日期
  DECLARE v_mindate DATETIME; #最小日期
  DECLARE v_maxdate DATETIME; #最大日期
  DECLARE v_pt VARCHAR(20); #分区名称
  DECLARE v_maxval int; # 分区最大日期天数
  DECLARE add_sql VARCHAR(256); # 新增分区sql
  SELECT MAX(CAST(FROM_DAYS(REPLACE(partition_description,'''','')) AS DATE)) AS val
  INTO  v_maxdate
  FROM  information_schema.`PARTITIONS`
  WHERE table_name=partition_table_name;  # 读取数据库实例表当前分区名称中最大值
  SET v_sysdate = SYSDATE(); #当前日期
  WHILE v_maxdate<(v_sysdate+INTERVAL 1 MONTH) DO
      SET v_pt = DATE_FORMAT(v_maxdate, '%Y%m');#将要创建的分区名称时间后缀
      SET v_maxval=TO_DAYS(v_maxdate+ INTERVAL 1 MONTH);# 设置分区最大值
      SET add_sql =  CONCAT('alter table ',partition_table_name,' add partition (partition ',partition_table_name,'_', 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

必须是表已经使用分区才能使用此存储过程，否则请先给现有表创建分区再使用
存储过程的使用方法

call table_partition_month('表名');

为了实现每月自动运行，创建定时器
首先检查mysql定时器是否正常开启

show VARIABLES like 'event_scheduler'

value为ON为开启，如为OFF则需要执行操作开启，此处不再多说
然后创建定时器

CREATE EVENT table_partition_event
ON schedule every 1 month #此处为轮询时间，可以自由设置
#此处为开始时间，为每月1号凌晨1点
starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour)
DO  call table_partition_month('表名');

至此创建成功