mysql 自动按月分区表

CREATE TABLE monthly_sales (
    id INT AUTO_INCREMENT,
    sale_date DATE,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (TO_DAYS(sale_date)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')),
    PARTITION p202305 VALUES LESS THAN (TO_DAYS('2023-06-01')),
    PARTITION p202306 VALUES LESS THAN (TO_DAYS('2023-07-01')),
    PARTITION p202307 VALUES LESS THAN (TO_DAYS('2023-08-01')),
    PARTITION p202308 VALUES LESS THAN (TO_DAYS('2023-09-01')),
    PARTITION p202309 VALUES LESS THAN (TO_DAYS('2023-10-01')),
    PARTITION p202310 VALUES LESS THAN (TO_DAYS('2023-11-01')),
    PARTITION p202311 VALUES LESS THAN (TO_DAYS('2023-12-01')),
    PARTITION p202312 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

DELIMITER $$

CREATE EVENT create_monthly_partitions
ON SCHEDULE EVERY 1 MONTH
STARTS CURRENT_DATE + INTERVAL 1 MONTH
DO
BEGIN
    DECLARE current_month DATE;
    SET current_month = DATE_FORMAT(NOW(), '%Y-%m-01');

    SET @sql = CONCAT('ALTER TABLE monthly_sales ADD PARTITION (PARTITION p', 
                        YEAR(current_month) * 100 + MONTH(current_month), 
                        ' VALUES LESS THAN (TO_DAYS(\'', 
                        DATE_ADD(current_month, INTERVAL 1 MONTH), 
                        '\')));');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;