mysql定时表分区

CREATE TABLE IF NOT EXISTS `log_table` (
    `id` INT AUTO_INCREMENT,
    `message` TEXT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p0 VALUES LESS THAN (TO_DAYS('2023-01-01')),
    PARTITION p1 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p2 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

DELIMITER //

CREATE EVENT IF NOT EXISTS `add_monthly_partition`
ON SCHEDULE EVERY 1 MONTH
STARTS CURRENT_DATE + INTERVAL 1 MONTH
DO
BEGIN
    DECLARE partition_name VARCHAR(255);
    SET partition_name = CONCAT('p', (SELECT MAX(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'log_table') + 1);
    SET @sql = CONCAT('ALTER TABLE log_table ADD PARTITION (PARTITION ', partition_name, ' VALUES LESS THAN (TO_DAYS(\'', DATE_ADD(CURRENT_DATE, INTERVAL 1 MONTH), '\')))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

SET GLOBAL event_scheduler = ON;