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 PROCEDURE add_monthly_partition()
BEGIN
    DECLARE current_month DATE;
    SET current_month = CURDATE();
    SET current_month = DATE_FORMAT(current_month, '%Y-%m-01');

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

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 创建一个事件来每月调用存储过程
CREATE EVENT monthly_partition_event
ON SCHEDULE EVERY 1 MONTH
STARTS CURDATE()
DO
CALL add_monthly_partition();