mysql按周自动分区

CREATE TABLE logs (
    id INT AUTO_INCREMENT,
    log_time TIMESTAMP NOT NULL,
    message TEXT,
    PRIMARY KEY (id, log_time)
) 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-01-08')),
    PARTITION p2 VALUES LESS THAN (TO_DAYS('2023-01-15')),
    PARTITION p3 VALUES LESS THAN (TO_DAYS('2023-01-22')),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

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

-- 创建事件
CREATE EVENT weekly_partition
ON SCHEDULE EVERY 1 WEEK
DO
BEGIN
    DECLARE current_week INT;
    SET current_week = TO_DAYS(CURDATE()) DIV 7;

    SET @sql = CONCAT('ALTER TABLE logs ADD PARTITION (PARTITION p', current_week, ' VALUES LESS THAN (TO_DAYS(\'', DATE_ADD(CURDATE(), INTERVAL 7 WEEK), '\')))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 删除旧的分区（假设保留最近8周的数据）
    IF current_week > 8 THEN
        SET @sql = CONCAT('ALTER TABLE logs DROP PARTITION p', current_week - 8);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END;