您可以通过创建定时任务的方式来创建、删除或交换分区，以实现分区自动化管理。

背景信息

在某些行业中，固定周期内可能会产生大量的数据，同时也会通过删除大量数据的方式来节省存储空间。如果新产生的数据和需要删除的数据保存在同一张表中，周期性的大批量数据更新，极有可能影响业务的连续性。且在分区表使用不够普遍的情况下，通常的做法是，由DBA在运维时间内定期手动创建新表来承载新的数据，并且需要删除无用数据所在的表。

这种场景存在一系列痛点：
  • 周期性的大批量更新表，可能会对业务的连续性有影响。
  • DBA在运维时间内手动更新表数据，会增加运维成本。

通过创建定时任务的方式来实现自动化管理分区，既可以避免影响业务连续性，也可以减少不必要的人工操作。

前提条件

集群版本需为PolarDB MySQL版8.0.2版本且Revision version为8.0.2.2.0及以上。您可以通过查询版本号确认集群版本。

定时任务语法

语法
CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;
参数说明
参数是否必选说明
DEFINER否给指定用户添加该定时任务的使用权限。
IF NOT EXISTS否判断需要创建的事件是否已存在。
event_name是指定事件名称。event_name最大长度为64个字符。
schedule是指定该事件的调度时间。调度时间语法支持以下两种：
  • AT
    AT timestamp [+ INTERVAL interval] ...
  • EVERY
    EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
其中，INTERVAL包含的时间单位如下：
{YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
 WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
 DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
ON COMPLETION [NOT] PRESERVE否指定事件执行完一次后的处理方式。
  • 如果为ON COMPLETION PRESERVE，当event执行完成后，该event会被关闭，但event还是会存在。
  • 如果为ON COMPLETION NOT PRESERVE，当event执行完成后，该event会被自动删除。
ENABLE、DISABLE、DISABLE ON SLAVE否指定事件的一种属性。取值如下：
  • ENABLE（默认）：表示该事件是开启的，即调度器检查事件是否必需调用。
  • DISABLE：表示该事件是关闭的，即事件的声明存储到目录中，但是调度器不会检查它是否应该被调用。
  • DISABLE ON SLAVE：表示该事件在从节点中是关闭的。
COMMENT ‘comment’否指定事件的注释。
DO event_body是指定事件启动时需要执行的代码。
说明
  • 可以是任何有效的SQL语句、存储过程或者一个计划执行的事件。
  • 如果包含多条语句，可以使用BEGIN…END复合结构。

定时新增分区

如果您需要为orders分区表定时新增分区，可以通过创建定时任务的方式定时触发新增分区，示例如下：
DELIMITER ||
CREATE EVENT IF NOT EXISTS add_partition ON SCHEDULE
EVERY 1 DAY STARTS '2022-05-20 22:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
set @pname = concat("alter table orders add partition (partition p",date_format(date_add(curdate(), interval 2 day), '%Y%m%d'), " values less than('", date_add(curdate(), interval 2 day), "'))");
prepare stmt_add_partition from @pname;
execute stmt_add_partition;
deallocate prepare stmt_add_partition;
END ||
DELIMITER ;
假设当前已经存在的最大的分区范围为：2022-05-20 00:00:00~2022-05-20 23:59:59，该定时任务可以从2022-05-20 22:00:00开始，每天创建一个新的分区，来保存第二天的数据。
如果分区表orders为INTERVAL RANGE分区，也可以通过INSERT方式定时触发新增分区，示例如下：
CREATE EVENT IF NOT EXISTS add_partition ON SCHEDULE
EVERY 1 DAY STARTS '2022-05-20 00:00:00'
ON COMPLETION PRESERVE
DO INSERT INTO orders VALUES(id, DATE_ADD(NOW(), INTERVAL 1 DAY));
假设当前最大的分区范围是2022-05-20 00:00:00~2022-05-20 23:59:59，定时任务会从2022-05-20 00:00:00开始执行，每天新增一个新分区，而且是提前一天新增下一天的分区。

定时删除分区

如果业务上需要定期清理orders表中无用的数据，可以创建一个定时任务删除对应的分区。示例如下：
DELIMITER ||
CREATE EVENT IF NOT EXISTS drop_partition ON SCHEDULE
EVERY 1 DAY STARTS '2022-05-21 02:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
set @pname = concat('alter table orders drop partition p', date_format(curdate(), '%Y%m%d'));
prepare stmt_drop_partition from @pname;
execute stmt_drop_partition;
deallocate prepare stmt_drop_partition;
END ||
DELIMITER ;
假设运维时间从02:00开始，该定时任务会从2022-05-21 02:00:00开始，在每天的02:00删除前一天创建的分区。

定时转换分区

如果您不想直接删除orders表中的分区，也可以通过exchange_partition将不再需要的分区转成一张表，这张表与orders分区表完全独立，您可以自行决定如何处理这张表中的数据。示例如下：
-- 创建一个与分区表相同表结构的非分区表来做exchange, DDL结束后分区数据会被交换出去，原来的分区会变为空分区。
DELIMITER ||
CREATE EVENT IF NOT EXISTS exchange_partition ON SCHEDULE
EVERY 1 DAY STARTS '2022-05-21 02:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
set @pname = concat('create table orders_', date_format(curdate(), '%Y%m%d'), '(id int, ordertime datetime)');
prepare stmt_create_table from @pname;
execute stmt_create_table;
deallocate prepare stmt_create_table;

set @pname = concat('alter table orders exchange partition p', date_format(curdate(), '%Y%m%d'), ' with table orders_', date_format(curdate(), '%Y%m%d'));
prepare stmt_exchange_partition from @pname;
execute stmt_exchange_partition;
deallocate prepare stmt_exchange_partition;
END ||
DELIMITER ;
该任务会从2022-05-21 02:00:00开始，在每天的02:00，将前一天创建的分区与一个空表进行交换，分区中原有的数据都会保存在被交换的表中。