MySQL自动增加和删除分区

1、准备工作

查看MySQL是否开启了事件

show variables like 'event_scheduler';

若没有开启事件,则执行以下命令开启事件:

SET GLOBAL event_scheduler = ON;

2、创建示例表

新建一个表,主要用于演示。

CREATE TABLE dm_test.tt_msg_info (
  id BIGINT(20) NOT NULL,
  msg_date DATE NOT NULL,
  msg_key VARCHAR(32) NULL,
  msg_content LONGTEXT NULL,
  create_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id, msg_date))
    PARTITION BY RANGE(to_days(msg_date)) PARTITIONS 3
    (PARTITION p20190101 VALUES LESS THAN (to_days('2019-01-02')),
     PARTITION p20190102 VALUES LESS THAN (to_days('2019-01-03')),
     PARTITION p20190103 VALUES LESS THAN (to_days('2019-01-04')));

3、创建自动删除分区的存储过程

以数据库dm_test为例:

USE dm_test;
DROP procedure IF EXISTS auto_del_partitions;

DELIMITER $$
USE dm_test$$
CREATE  PROCEDURE auto_del_partitions(IN c_schema VARCHAR(64), IN c_table VARCHAR(64), IN c_min_partition DATE)
BEGIN

    #用于存储需要删除的分区名字
    DECLARE p_need_del_partition_name VARCHAR(64);

    # 定义循环标识,默认值为FALSE
    DECLARE done INT DEFAULT FALSE;

    # 定义游标(查询需要删除的分区名称)
    DECLARE need_del_partition_cur CURSOR FOR
        SELECT PARTITION_NAME
        FROM INFORMATION_SCHEMA.PARTITIONS 
        WHERE TABLE_SCHEMA = c_schema
          AND TABLE_NAME = c_table
          AND PARTITION_DESCRIPTION < TO_DAYS(c_min_partition);

    # 将结束标识绑定到游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    # 打开游标
    OPEN need_del_partition_cur;

    #循环开始
    REPEAT
        FETCH need_del_partition_cur INTO p_need_del_partition_name;
        IF NOT done THEN
            SET @p_sql_drop_partition = CONCAT('ALTER TABLE ', c_schema, '.', c_table, ' DROP PARTITION ', p_need_del_partition_name);
            SELECT @p_sql_drop_partition;
            PREPARE drop_partion FROM @p_sql_drop_partition; 
            EXECUTE drop_partion;
        END IF;
    UNTIL done END REPEAT;
    #循环结束

    #关闭游标
    CLOSE need_del_partition_cur;
END$$

DELIMITER ;

4、创建自动新增分区的存储过程

以数据库dm_test为例:

USE dm_test;
DROP procedure IF EXISTS auto_add_partitions;

DELIMITER $$
USE dm_test $$
CREATE PROCEDURE auto_add_partitions(IN c_schema VARCHAR(64), IN c_table VARCHAR(64), 
                            IN c_partition_reserve_num INT, IN c_partition_add_num INT, 
                            IN c_partition_type INT, IN c_interval INT)
LABEL_END:
BEGIN
    DECLARE p_next_day DATE DEFAULT NULL;
    DECLARE p_next_month DATE DEFAULT NULL;
    DECLARE p_next_year DATE DEFAULT NULL;
    DECLARE p_exsit_partition_num INT DEFAULT NULL;
    DECLARE p_add_max_partition_description DATE DEFAULT NULL;
    DECLARE p_add_min_partition_description DATE DEFAULT NULL;
    DECLARE p_del_max_partition_description DATE DEFAULT NULL;
    DECLARE p_cur_max_partition_description DATE DEFAULT NULL;
    DECLARE p_add_partition_description DATE DEFAULT NULL;
    DECLARE p_prev_partition_description DATE DEFAULT NULL;
    DECLARE p_add_partition_name VARCHAR(255) DEFAULT NULL;    

    # 查看表是否有分区
    SELECT COUNT(PARTITION_NAME) INTO p_exsit_partition_num FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA = c_schema 
        AND TABLE_NAME = c_table;

    # 如果表分区不存在,则打印错误信息并且退出存储过程
    IF (p_exsit_partition_num < 1) THEN
        SELECT "partition table not is exist" AS "ERROR";
        LEAVE LABEL_END;
    END IF;

    # 从当前时间往前推,要保留的分区数量,默认最少留3个分区
    IF (c_partition_reserve_num < 3) THEN
        set c_partition_reserve_num = 3;
    END IF;

    # 从当前时间往后推,要增加的分区数量,默认最少增加3个分区
    IF (c_partition_add_num < 3) THEN
        SET c_partition_add_num = 3;
    END IF;

    # 每次增加分区的间隔,默认最少为1
    IF (c_interval < 1) THEN
        SET c_interval = 1;
    END IF;

    # 获取当前表的最大的分区描述值
    SELECT FROM_DAYS(partition_description) INTO p_cur_max_partition_description
    FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE TABLE_SCHEMA = c_schema
        AND TABLE_NAME = c_table
    ORDER BY partition_description DESC 
    LIMIT 1;

    # 从当前时间开始,按天分区模式的最近的1天
    SET p_next_day = DATE_ADD(CURDATE(), INTERVAL c_interval DAY);
    # 从当前时间开始,按月分区模式的最近的1月
    SET p_next_month = STR_TO_DATE(DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL c_interval MONTH), '%Y-%m-%d');
    # 从当前时间开始,按年分区模式的最近的1年
    SET p_next_year = STR_TO_DATE(DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-01-01'), INTERVAL c_interval YEAR), '%Y-%m-%d');

   IF (c_partition_type = 0) THEN
        # 按天分区模式,需要自动增加的最大日期
        SET p_add_max_partition_description = DATE_ADD(p_next_day, INTERVAL c_interval*c_partition_add_num DAY);
        # 按天分区模式,需要自动增加的最小日期
        SET p_add_min_partition_description = p_next_day;
        # 按天分区模式,需要自动删除的最大日期
        SET p_del_max_partition_description = DATE_SUB(p_next_day, INTERVAL c_interval*c_partition_reserve_num DAY);
    ELSEIF (c_partition_type = 1) THEN
        # 按月分区模式,需要自动增加的最大日期
        SET p_add_max_partition_description = DATE_ADD(p_next_month, INTERVAL c_interval*c_partition_add_num MONTH);
        # 按月分区模式,需要自动增加的最小日期
        SET p_add_min_partition_description = p_next_month;
        # 按月分区模式,需要自动删除的最大日期
        SET p_del_max_partition_description = DATE_SUB(p_next_month, INTERVAL c_interval*c_partition_reserve_num MONTH);
    ELSE
        # 按年分区模式,需要自动增加的最大日期
        SET p_add_max_partition_description = DATE_ADD(p_next_year, INTERVAL c_interval*c_partition_add_num YEAR);
        # 按年分区模式,需要自动增加的最小日期
        SET p_add_min_partition_description = p_next_year;
        # 按年分区模式,需要自动删除的最大日期
        SET p_del_max_partition_description = DATE_SUB(p_next_year, INTERVAL c_interval*c_partition_reserve_num YEAR);
    END IF;

    IF (p_cur_max_partition_description < p_add_min_partition_description) THEN
        # 当前表分区的最大日期比应该自动增加的最小日期还小,则自动增加的开始日期以自动增加的最小日期为准
        SET p_add_partition_description = p_add_min_partition_description;
    ELSE
        # 当前表分区的最大日期比应该自动增加的最小日期还大或者相等,则自动增加的开始日期以当前表分区的最大日期为准
        IF (c_partition_type = 0) THEN
            SET p_add_partition_description = DATE_ADD(p_cur_max_partition_description, INTERVAL c_interval DAY);
        ELSEIF (c_partition_type = 1) THEN
            SET p_add_partition_description = STR_TO_DATE(DATE_ADD(DATE_FORMAT(p_cur_max_partition_description, '%Y-%m-01'), INTERVAL c_interval MONTH), '%Y-%m-%d');
        ELSE
            SET p_add_partition_description = STR_TO_DATE(DATE_ADD(DATE_FORMAT(p_cur_max_partition_description, '%Y-01-01'), INTERVAL c_interval YEAR), '%Y-%m-%d');
        END IF;
    END IF;


    # 循环自动增加分区
    WHILE (p_add_partition_description <= p_add_max_partition_description) DO
        IF (c_partition_type = 0) THEN
            SET p_prev_partition_description = DATE_SUB(p_add_partition_description, INTERVAL c_interval DAY);
            SET p_add_partition_name = concat('p', DATE_FORMAT(p_prev_partition_description, '%Y%m%d'));
        ELSEIF (c_partition_type = 1) THEN
            SET p_prev_partition_description = DATE_SUB(p_add_partition_description, INTERVAL c_interval MONTH);
            SET p_add_partition_name = concat('p', DATE_FORMAT(p_prev_partition_description, '%Y%m'));
        ELSE
            SET p_prev_partition_description = DATE_SUB(p_add_partition_description, INTERVAL c_interval YEAR);
            SET p_add_partition_name = concat('p', DATE_FORMAT(p_prev_partition_description, '%Y'));
        END IF;

        SET @p_sql_add_partition = concat('ALTER TABLE ', c_schema, '.', c_table, ' ADD PARTITION (PARTITION ', p_add_partition_name, ' VALUES LESS THAN (TO_DAYS("', p_add_partition_description, '")))');
        SELECT @p_sql_add_partition;
        PREPARE add_partion FROM @p_sql_add_partition;
        EXECUTE add_partion;

        IF (c_partition_type = 0) THEN
            SET p_add_partition_description = DATE_ADD(p_add_partition_description, INTERVAL c_interval DAY);
        ELSEIF (c_partition_type = 1) THEN
            SET p_add_partition_description = DATE_ADD(p_add_partition_description, INTERVAL c_interval MONTH);
        ELSE
            SET p_add_partition_description = DATE_ADD(p_add_partition_description, INTERVAL c_interval YEAR);
        END IF;

    END WHILE;

    # 删除过期的分区
    CALL auto_del_partitions(c_schema, c_table, p_del_max_partition_description);
END$$

DELIMITER ;

5、创建每天定时执行的事件

DELIMITER $$
drop event if exists auto_partitions_event  $$
create event auto_partitions_event 
on schedule every 1 day
starts '2019-01-01 00:00:00'
on COMPLETION PRESERVE
do
BEGIN
    CALL auto_add_partitions('dm_test', 'tt_msg_info', 7, 7, 0, 1);
END $$
DELIMITER ;

未经允许不得转载:君子如兰 » MySQL自动增加和删除分区

赞 (0)