mysql通过存储过程对表分区进行重新分区

mysql通过存储过程对表分区进行重新分区

关于存储过程创建表分区，请看我的 mysql存储过程创建表分区

这一篇主要是对按时间进行月表分区再次进行分区（全部重新分区，部分月分区数据重新分区）
1.全部重新分区

 --重新分区存储过程
create PROCEDURE pro_sys_MonthlogToWeek (IN tableName VARCHAR(20),IN timeColName VARCHAR(20))
COMMENT '把原来按月分区的数据全部重新按周进行分区'
BEGIN
    DECLARE num int DEFAULT 0;
    DECLARE startTime date;
    DECLARE maxTime date;
    set @v_max=CONCAT('select MIN(',timeColName,'),MAX(',timeColName,') into @minTime, @maxTime from ',tableName) ;
    PREPARE stm from @v_max;
    EXECUTE stm;
    DEALLOCATE PREPARE stm;
    set startTime=@minTime;
    set maxTime=@maxTime;
    WHILE startTime<=maxTime DO
        SELECT DATE_FORMAT(DATE_ADD(startTime,INTERVAL 1 WEEK),'%Y-%m-%d') INTO startTime from DUAL;
        IF num=0 then
            set @v_add=CONCAT('ALTER table ',tableName,' PARTITION by range COLUMNS(',timeColName,') (partition ',CONCAT('parW',num),' values less than (\'',startTime,'\'),PARTITION paewMax values less than MAXVALUE)');
        ELSE
            set @v_add=CONCAT('alter table ',tableName,' REORGANIZE PARTITION paewMax INTO (partition ',CONCAT('parW',num),' values less than (\'',startTime,'\'),PARTITION paewMax values less than MAXVALUE)');
        end IF;
        set num=num+1;
        PREPARE stmt from @v_add;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    end WHILE;
END

--运行
call pro_sys_MonthlogToWeek('employees','separated');

2.部分数据重新分区（未写完）

-- 某个分区数据的进行重新分区存储过程
create PROCEDURE pro_dateAgainParByDate (IN tableName VARCHAR(20),
                                    IN timeColName VARCHAR(20),IN startDate date,IN endDate,IN dateFormat VARCHAR(20))
COMMENT '对某个时间段的数据进行重新分区'
BEGIN
    DECLARE num int DEFAULT 0;
    DECLARE startTime date;
    DECLARE maxTime date;
    DECLARE proNames VARCHAR(100);
    set startTime=startDate;    
    -- 查看开始时间所在的分区信息
    SELECT partition_name part,partition_description,partition_ordinal_position into startParId,startParNum
        FROM INFORMATION_SCHEMA.partitions 
        WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME='employees' 
        and partition_description>=TO_DAYS(startDate) ORDER BY partition_ordinal_position limit 1;
    -- 查看结束时间所在的分区信息
    SELECT partition_name part,partition_description,partition_ordinal_position into endParId,endPar,endParNum
        FROM INFORMATION_SCHEMA.partitions 
        WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME='employees' 
        and partition_description>=TO_DAYS(endDate) ORDER BY partition_ordinal_position limit 1;
    IF startParNum <> endParNum THEN
        call resultDemo(tableName,startParId,endParId,proNames);
        #通过proNames 将分区合并成一个大分区，然后按条件拆分成新分区#
    ELSE
        #对分区按条件进行拆分#
    END IF;

通过游标函数查询到所有包含的原分区名称

create PROCEDURE resultDemo(IN tableName VARCHAR(20),IN startParNum INT,
                                                            IN endParNum INT,OUT pronames VARCHAR(100))
COMMENT '获取两个分区直接的所有分区名称，拼接成A,B,C 格式'
BEGIN
    declare v_sql varchar(500);  
    DECLARE nameStr VARCHAR(100) DEFAULT '' ;
    DECLARE res_name VARCHAR(20);
    -- 定义游标遍历时，作为判断是否遍历完全部记录的标记
    declare no_more_departments integer DEFAULT 0;     
    -- 定义游标名字为C_RESULT 
    DECLARE C_RESULT CURSOR FOR SELECT partition_name part from INFORMATION_SCHEMA.partitions 
                                WHERE TABLE_SCHEMA = SCHEMA() 
                                AND TABLE_NAME=tableName and partition_ordinal_position >=startParNum 
                                AND partition_ordinal_position<=endParNum;
    -- 声明当游标遍历完全部记录后将标志变量置成某个值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

    OPEN C_RESULT;       -- 打开之前定义的游标
    REPEAT                      -- 循环语句的关键词
             FETCH C_RESULT INTO res_name;   -- 取出每条记录并赋值给相关变量，注意顺序
             IF NOT no_more_departments THEN
                    set nameStr=CONCAT(nameStr,',',res_name);
                    select nameStr;
             end IF;
    UNTIL no_more_departments  END REPEAT;    -- 循环语句结束
    CLOSE C_RESULT;
    set pronames=nameStr;
END

一个刚升级为奶爸的码农的个人整理。