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
一个刚升级为奶爸的码农的个人整理。