James Blogs

参考1:https://www.cnblogs.com/lanceblog/p/5532068.html

参考2:https://www.cnblogs.com/ivictor/p/5032793.html

-- 创建测试表和第一个分区
CREATE TABLE if not exists hash_datetime_test
(
  id       INT,
  hiredate DATETIME
)
PARTITION BY range columns ( hiredate ) (
    PARTITION p20191201 values less than ('20191201')
);

-- 创建新增表分区的存储过程
CREATE PROCEDURE create_partition(in tablename varchar(50))
BEGIN
  /* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
  SELECT STR_TO_DATE(partition_name, 'p%Y%m%d') INTO @old_part_name
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE table_name = tablename
  ORDER BY partition_ordinal_position DESC
  LIMIT 1;

  SET @max_date = DATE_ADD(@old_part_name, INTERVAL 1 MONTH);

  /* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
  SET @s1 =
      CONCAT('ALTER TABLE ', tablename, ' ADD PARTITION (PARTITION ', date_format(@max_date, 'p%Y%m%d'),
             ' VALUES LESS THAN (''',
             date_format(@max_date, '%Y%m%d'), '''))');

  PREPARE stmt2 FROM @s1;
  EXECUTE stmt2;
  DEALLOCATE PREPARE stmt2;
END;

-- 创建定时事件每月调用存储过程新增分区
CREATE EVENT IF NOT EXISTS create_partition_on_monthly
  ON SCHEDULE EVERY 1 MONTH -- STARTS '2019-11-05 06:00:00'
  ON COMPLETION PRESERVE
  DO
  BEGIN

    CALL CREATE_PARTITION('hash_datetime_test');

  END;

-- 启动定时事件
ALTER EVENT create_partition_on_monthly ON COMPLETION PRESERVE ENABLE;

-- 关闭定时事件
ALTER EVENT create_partition_on_monthly ON COMPLETION PRESERVE DISABLE;

-- 啰嗦几句
/*
运行以下语句可查看是否支持表分区，支持会有这条记录
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
...
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
...
+----------------------------+----------+--------------------+---------+---------+
 */
show plugins;

/*
使用定时事件之前需要先配置，不然不会运行
*/
set global event_scheduler = 1;