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.
- 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
SyntaxCREATE
[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;
ParametersParameter | Required | Description |
---|---|---|
DEFINER | No | Grants the permissions on the scheduled task to the specified user. |
IF NOT EXISTS | No | Checks whether the event to be created already exists. |
event_name | Yes | The name of the event. The event_name value can be up to 64 characters in length. |
schedule | Yes | The scheduling time of the event. You can specify the time in the following ways:
INTERVAL value:
|
ON COMPLETION [NOT] PRESERVE | No | Specifies the processing method after the event is executed once. Valid values:
|
ENABLE, DISABLE, and DISABLE ON SLAVE | No | Specifies a property of the event. Valid values:
|
COMMENT 'comment' | No | The comment of the event. |
DO event_body | Yes | The code to be executed when the event is enabled. Note
|
Add partitions
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. 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
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
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.