如何在MySQL中实现动态分区？自动分区表的创建与管理方法！

RANGE

LIST

ALTER TABLE ... ADD PARTITION

ALTER TABLE ... DROP PARTITION

CREATE TABLE sales_data (
    id INT AUTO_INCREMENT,
    sale_date DATETIME NOT NULL,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, sale_date) -- 分区键也应是主键的一部分
)
PARTITION BY RANGE (TO_DAYS(sale_date)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    -- ... 预先创建几个分区
    PARTITION p_future VALUES LESS THAN (MAXVALUE) -- 这是一个兜底分区，所有超出预设范围的数据会进入这里
);

MAXVALUE

sale_date

TO_DAYS()

UNIX_TIMESTAMP()

MAXVALUE

MAXVALUE

MAXVALUE

MAXVALUE

WHERE

WHERE sale_date BETWEEN '2023-03-01' AND '2023-03-31'

p202303

WHERE amount > 1000

amount

SHOW VARIABLES LIKE 'event_scheduler';

OFF

SET GLOBAL event_scheduler = ON;

my.cnf

my.ini

event_scheduler = ON

DELIMITER //

CREATE EVENT IF NOT EXISTS `prune_and_add_partitions`
ON SCHEDULE EVERY 1 DAY -- 每天运行一次
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY -- 从明天开始运行
DO
BEGIN
    -- 声明变量
    DECLARE next_month_start DATE;
    DECLARE old_month_end DATE;
    DECLARE partition_name_add VARCHAR(20);
    DECLARE partition_name_drop VARCHAR(20);
    DECLARE sql_stmt VARCHAR(1000);

    -- 计算下个月的起始日期
    SET next_month_start = DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY);
    -- 计算需要删除的分区结束日期（例如，保留3个月数据）
    SET old_month_end = DATE_SUB(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 4 MONTH)), INTERVAL -1 DAY); -- 比如删除4个月前的数据

    -- 构造要添加的分区名和值
    SET partition_name_add = CONCAT('p', DATE_FORMAT(next_month_start, '%Y%m'));
    SET sql_stmt = CONCAT('ALTER TABLE sales_data ADD PARTITION (PARTITION ', partition_name_add, ' VALUES LESS THAN (TO_DAYS(\'', DATE_FORMAT(DATE_ADD(next_month_start, INTERVAL 1 MONTH), '%Y-%m-%d'), '\')));');

    -- 检查分区是否存在，避免重复添加
    -- 实际生产中，这里需要更复杂的逻辑来查询information_schema.PARTITIONS表
    -- 简化示例：直接尝试添加，如果已存在会报错（可以忽略错误或捕获）
    -- SELECT COUNT(*) INTO @partition_exists FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sales_data' AND PARTITION_NAME = partition_name_add;
    -- IF @partition_exists = 0 THEN
        PREPARE stmt FROM sql_stmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    -- END IF;

    -- 构造要删除的分区名和值
    SET partition_name_drop = CONCAT('p', DATE_FORMAT(old_month_end, '%Y%m'));
    SET sql_stmt = CONCAT('ALTER TABLE sales_data DROP PARTITION ', partition_name_drop, ';');

    -- 同样，需要检查分区是否存在，避免删除不存在的分区
    -- SELECT COUNT(*) INTO @partition_exists FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sales_data' AND PARTITION_NAME = partition_name_drop;
    -- IF @partition_exists > 0 THEN
        PREPARE stmt FROM sql_stmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    -- END IF;

END //

DELIMITER ;

ALTER TABLE

information_schema.PARTITIONS

VARCHAR

MAXVALUE

MAXVALUE

MAXVALUE

MAXVALUE

MAXVALUE

ALTER TABLE

ADD PARTITION

DROP PARTITION

REORGANIZE PARTITION

REORGANIZE

pt-online-schema-change

ALTER TABLE

my.cnf

event_scheduler = ON

BEGIN...END

UDF