mysql 自动新建分区

CREATE TABLE 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 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'))
);

DELIMITER //

CREATE TRIGGER trg_auto_partition
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    DECLARE partition_name VARCHAR(20);
    DECLARE max_date DATE;
    DECLARE new_date DATE;

    SET max_date = (SELECT MAX(sale_date) FROM sales);
    SET new_date = DATE_ADD(max_date, INTERVAL 1 MONTH);

    IF new_date > CURDATE() THEN
        SET partition_name = CONCAT('p', (SELECT COUNT(*) + 1 FROM information_schema.PARTITIONS WHERE TABLE_NAME = 'sales'));
        SET @sql = CONCAT('ALTER TABLE sales ADD PARTITION (PARTITION ', partition_name, ' VALUES LESS THAN (TO_DAYS(\'', new_date, '\')))');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END //

DELIMITER ;