You can create, delete, or exchange partitions by creating scheduled tasks to implement automated management.

Background information

In some industries, a large amount of data may be periodically generated. You can also delete a large amount of data to save storage space. If the newly generated data is stored in the same table as the data to be deleted, periodic updates of lots of data may affect business continuity. If most tables are not partitioned, you must manually create tables for new data on a regular basis and delete the tables where unnecessary data is located.

This practice has disadvantages:
  • Periodic updates of lots of data may affect business continuity.
  • You must manually update data in tables, which increases O&M costs.

You can create scheduled tasks for automated management of partitions to avoid possible impacts on business continuity and reduce manual operations.

Prerequisites

The cluster must be of PolarDB for MySQL 8.0.2 and the revision version must be 8.0.2.2.0 or later. For information about how to view the kernel version of your cluster, see Query the engine version.

Syntax

Syntax
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;
Parameters
ParameterRequiredDescription
DEFINERNoGrants the permissions on the scheduled task to the specified user.
IF NOT EXISTSNoChecks whether the event to be created already exists.
event_nameYesThe name of the event. The event_name value can be up to 64 characters in length.
scheduleYesThe scheduling time of the event. You can specify the time in the following ways:
  • AT
    AT timestamp [+ INTERVAL interval] ...
  • EVERY
    EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
The units for the INTERVAL value:
{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] PRESERVENoSpecifies the processing method after the event is executed once. Valid values:
  • ON COMPLETION PRESERVE: The event is disabled after the event is executed. However, the event still exists.
  • ON COMPLETION NOT PRESERVE: The event is automatically deleted when the event is executed.
ENABLE, DISABLE, and DISABLE ON SLAVENoSpecifies a property of the event. Valid values:
  • ENABLE: indicates that the event is enabled. The scheduler checks whether the event must be scheduled.
  • DISABLE: Indicates that the event is disabled. The declaration of the event is stored in the directory, but the scheduler does not check whether the event must be scheduled.
  • DISABLE ON SLAVE: indicates that the event is disabled on the read-only node.
COMMENT 'comment'NoThe comment of the event.
DO event_bodyYesThe code to be executed when the event is enabled.
Note
  • It can be a valid SQL statement, a stored procedure, or a scheduled execution event.
  • If multiple statements are included, you can use the BEGIN…END syntax.

Add partitions

If you want to regularly add partitions to the orders partitioned table, you can create a scheduled task to trigger the operation. Example:
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 ;
If the maximum range of the partition is 2022-05-20 00:00:00 to 2022-05-20 23:59:59, the scheduled task can create a partition every day starting from 2022-05-20 22:00:00 to save the data of the next day.
If orders is an interval range partitioned table, you can also trigger the scheduled task to add partitions by using the INSERT clause. Example:
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));
If the maximum range of the partition is 2022-05-20 00:00:00 to 2022-05-20 23:59:59, the scheduled task can create a partition every day starting from 2022-05-20 00:00:00 to save the data of the next day.

Delete partitions

If you want to regularly clear unnecessary data from the orders partitioned table, you can create a scheduled task to delete the partitions. Example:
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 ;
If the O&M start time is 02:00, the scheduled task can delete the partitions created on the previous day at the 02:00 every day starting from 2022-05-21 02:00:00.

Exchange partitions

If you do not want to delete partitions from the orders partitioned table, you can use the exchange_partition parameter to exchange unnecessary partitions with a table which is completely independent of the orders partitioned table. You can decide how to process the data in this table. Example:
-- Create a non-partitioned table with the same structure to exchange the existing partitioned table. After the DDL statement is executed, the data in original partitions is exchanged and cleared. 
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 ;
Starting from 2022-05-21 02:00:00, the scheduled task exchanges the partitions created on the previous day into a new table at 02:00 every day. The data in the original partitions is stored in the new table.

Operation Video