mariadb：分区自动创建与删除

参考文章：https://blog.csdn.net/xlxxcc/article/details/52486426

1.以日自动创建与删除分区

调用示例：CALL proc_day_partition('t_base_log_abnormal',180,1);

删除180天之前的分区

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_day_partition`(
    IN `v_tablename` VARCHAR(50),
    IN `v_drop_interval` INT,
    IN `v_add_interval` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
    BEGIN
        DECLARE v_add_interval_1 int;
        DECLARE flag int default 0;
        SET v_add_interval_1=v_add_interval+1;
        START TRANSACTION;
        -- 自动创建今日分区
        select count(0) into flag from INFORMATION_SCHEMA.partitions 
            WHERE TABLE_SCHEMA = schema() AND TABLE_Name=v_tablename
            and partition_name=CONCAT('p',DATE_FORMAT(NOW(),'%Y%m%d'));
        if flag = 0 then
             SET @t=CONCAT('alter table ',v_tablename,' add partition ','(','partition ',
                 CONCAT('p',DATE_FORMAT(now(),'%Y%m%d')), 
                 ' VALUES LESS THAN (TO_DAYS (''',DATE_FORMAT(date_add(now(),interval v_add_interval day),'%Y%m%d'),''')))');
            SELECT @t;
           PREPARE stmt FROM @t;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;    
        end if;
        -- 删除过期分区
        if v_drop_interval > 0 then
            select count(0) into flag from INFORMATION_SCHEMA.partitions 
                WHERE TABLE_SCHEMA = schema() AND TABLE_Name=v_tablename
                and partition_name=CONCAT('p',DATE_FORMAT(DATE_SUB(NOW(),INTERVAL v_drop_interval DAY),'%Y%m%d'));
             if flag = 1 then
                  SET @s=CONCAT('alter table ',v_tablename,' drop partition ', CONCAT('p',DATE_FORMAT(DATE_SUB(NOW(),INTERVAL v_drop_interval DAY),'%Y%m%d')));
                     SELECT @s;
                PREPARE stmt FROM @s;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            end IF;
       end if;
       -- 自动创建明天分区
       select count(0) into flag from INFORMATION_SCHEMA.partitions 
            WHERE TABLE_SCHEMA = schema() AND TABLE_Name=v_tablename
            and partition_name=CONCAT('p',DATE_FORMAT(DATE_ADD(NOW(),INTERVAL v_add_interval DAY),'%Y%m%d'));  
        if flag = 0 then
             SET @t=CONCAT('alter table ',v_tablename,' add partition ','(','partition ',
                 CONCAT('p',DATE_FORMAT(DATE_ADD(NOW(),INTERVAL v_add_interval DAY),'%Y%m%d')), 
                 ' VALUES LESS THAN (TO_DAYS (''',DATE_FORMAT(date_add(now(),interval v_add_interval_1 day),'%Y%m%d'),''')))');
            SELECT @t;
           PREPARE stmt FROM @t;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
       end IF;
       COMMIT;
    END

上述脚本，在原文的基础上做了优化，主要是判断分区是否存在，删除一个不存在的分区会发生错误；自动创建今日的分区；判断是否要删除分区等等。

CREATE DEFINER=`root`@`localhost` EVENT `event_partition`
    ON SCHEDULE
        EVERY 1 DAY STARTS '2018-09-17'
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT ''
    DO BEGIN
    CALL proc_add_drop_partition('t_base_log',0,1);
    CALL proc_add_drop_partition('t_gps',60,1);
END

创建事件，执行存储过程。

2.以月自动创建和删除分区

调用示例：CALL proc_month_partition('t_base_log_abnormal',0,1);

不删除分区

CREATE DEFINER=`root`@`%` PROCEDURE `proc_month_partition`(
    IN `v_tablename` VARCHAR(50),
    IN `v_drop_interval` INT,
    IN `v_add_interval` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    -- 创建月分区
    -- v_tablename：分区表名
    -- v_drop_interval：过期天数，为0表示不删除分区
    -- v_add_interval ：创建分区间隔，以月单位

    DECLARE v_add_interval_1 int;
    DECLARE flag int default 0;
    SET v_add_interval_1=v_add_interval+1;
    START TRANSACTION;
    -- 自动创建当月分区
    select count(0) into flag from INFORMATION_SCHEMA.partitions 
        WHERE TABLE_SCHEMA = schema() AND TABLE_Name=v_tablename
        and partition_name=CONCAT('p',DATE_FORMAT(NOW(),'%Y%m'));
    if flag = 0 then
         SET @t=CONCAT('alter table ',v_tablename,' add partition ','(','partition ',
             CONCAT('p',DATE_FORMAT(now(),'%Y%m')), 
             ' VALUES LESS THAN (TO_DAYS (''',DATE_FORMAT(date_add(now(),interval v_add_interval MONTH),'%Y%m%d'),''')))');
        SELECT @t;
       PREPARE stmt FROM @t;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;    
    end if;
    -- 删除过期分区
    if v_drop_interval > 0 then
        select count(0) into flag from INFORMATION_SCHEMA.partitions 
            WHERE TABLE_SCHEMA = schema() AND TABLE_Name=v_tablename
            and partition_name=CONCAT('p',DATE_FORMAT(DATE_SUB(NOW(),INTERVAL v_drop_interval DAY),'%Y%m01'));
         if flag = 1 then
              SET @s=CONCAT('alter table ',v_tablename,' drop partition ', CONCAT('p',DATE_FORMAT(DATE_SUB(NOW(),INTERVAL v_drop_interval DAY),'%Y%m')));
                 SELECT @s;
            PREPARE stmt FROM @s;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        end IF;
   end if;
   -- 自动创建下月分区
   select count(0) into flag from INFORMATION_SCHEMA.partitions 
        WHERE TABLE_SCHEMA = schema() AND TABLE_Name=v_tablename
        and partition_name=CONCAT('p',DATE_FORMAT(DATE_ADD(NOW(),INTERVAL v_add_interval MONTH),'%Y%m'));  
    if flag = 0 then
         SET @t=CONCAT('alter table ',v_tablename,' add partition ','(','partition ',
             CONCAT('p',DATE_FORMAT(DATE_ADD(NOW(),INTERVAL v_add_interval DAY),'%Y%m')), 
             ' VALUES LESS THAN (TO_DAYS (''',DATE_FORMAT(date_add(now(),interval v_add_interval_1 MONTH),'%Y%m01'),''')))');
        SELECT @t;
       PREPARE stmt FROM @t;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
   end IF;
   COMMIT;
END