mysql自动按天表分区

CREATE TABLE daily_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    message TEXT
)
PARTITION BY RANGE (TO_DAYS(log_time)) (
    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
);

-- 启用事件调度器
SET GLOBAL event_scheduler = ON;

-- 创建事件
CREATE EVENT auto_partition_daily_logs
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    DECLARE partition_name VARCHAR(255);
    DECLARE max_date DATE;
    SET max_date = DATE_ADD(CURDATE(), INTERVAL 1 DAY);

    SET partition_name = CONCAT('p', DAYOFYEAR(max_date) - DAYOFYEAR(CURDATE()) + 1);

    ALTER TABLE daily_logs ADD PARTITION (
        PARTITION partition_name VALUES LESS THAN (TO_DAYS(max_date))
    );

    -- 删除超过一年的分区
    IF YEAR(max_date) > YEAR(CURDATE()) + 1 THEN
        SET @drop_partition = CONCAT('ALTER TABLE daily_logs DROP PARTITION p', DAYOFYEAR(CURDATE()) - 365);
        PREPARE stmt FROM @drop_partition;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END;