Partition Management
Events can add new partitions for future date ranges and drop old ones, keeping partitioned tables manageable.
MySQL
Advanced
5 min read
Automated Partition Management
Example
DELIMITER //
CREATE EVENT add_monthly_partition
ON SCHEDULE EVERY 1 MONTH STARTS "2024-02-01 00:00:00"
DO
BEGIN
SET @next_month = DATE_FORMAT(NOW() + INTERVAL 1 MONTH, "%Y%m01");
SET @sql = CONCAT("ALTER TABLE orders ADD PARTITION (PARTITION p",
DATE_FORMAT(NOW() + INTERVAL 1 MONTH, "%Y%m"),
" VALUES LESS THAN (TO_DAYS(\"", @next_month, "\")))"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
END//
DELIMITER ; Pro Tip
Pre-create partitions 2-3 months ahead — MySQL will use MAX_PARTITION if no partition matches.