mysql动态分区表_mysql 动态新建以及删除分区表

drop procedure if existsgeneral_procedure;--general_procedure的作用：新建分区表及在table_fen_qu表中存储新建分区表时的相关参数--general_procedure的参数：表名，分区表之间的时间间隔(单位为小时)，要新增的分区表个数

create procedure general_procedure(in tablenamein varchar(50),in intervalHour int,in newIntervalNum int)

general_pro:begin

--参数：最大时间

declare maxMonitTime datetime defaultSYSDATE();--参数：最大时间对应的字符串

declare maxMonitTimeStr varchar(50);--参数：最小时间

declare minMonitTime datetime defaultSYSDATE();--参数：最大时间对应的字符串

declare minMonitTimeStr varchar(50);--参数：数据库记录数

declare recoidNum int default 0;--判断传入的表名是否为空

if tablenamein is null thenleave general_pro;end if;--判断传入的时间间隔

if intervalHour <= 0 then

set intervalHour = 6;end if;--判断新增分区表个数

if newIntervalNum <= 0 then

set newIntervalNum = 1;end if;--在该表中，查询符合条件的记录数，backupflag=0说明是未备份

select count(*) into recoidNum from table_fen_qu where tablename=tablenamein and backupflag=0;if recoidNum > 0 then

--查询该表在table_fen_qu表中的最大监测时间

select monittime into maxMonitTime from table_fen_qu where tablename=tablenamein and backupflag=0 order by monittime desc limit 1;--判断监测时间是否为null

if maxMonitTime is null then

set maxMonitTime =SYSDATE();end if;--比较最大时间减去72个小时之后的时间与系统时间的早晚

set recoidNum = timestampdiff(hour,SYSDATE(),DATE_SUB(maxMonitTime,INTERVAL 3 DAY));--如果recoidNum大于0，说明最大监测时间减去72小时之后的时间仍然在系统时间之后,

--说明不用建立新的分区表，反之，则建立最大监测时间之后newIntervalNum个以每intervalHour小时为间隔的分区表

if recoidNum <= 0 then

set recoidNum = 1;while recoidNum <=newIntervalNum doset maxMonitTime =ADDDATE(maxMonitTime,INTERVAL intervalHour HOUR);set maxMonitTimeStr = CONCAT('p',DATE_FORMAT(maxMonitTime,"%Y%m%d%H%i%s"));--开始添加分区表

--拼接分区表代码段

set @v_add_s = CONCAT('ALTER TABLE',tablenamein,'ADD PARTITION (PARTITION',maxMonitTimeStr,'VALUES LESS THAN (\'',maxMonitTime,'\') ENGINE = InnoDB )');--定义预处理语句

prepare stmt from @v_add_s;--执行预处理语句

executestmt;--释放预处理语句

deallocate preparestmt;--开始在table_fen_qu中添加记录

insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(maxMonitTimeStr,tablenamein,maxMonitTime,0);--记录数加1

set recoidNum = recoidNum + 1;end while;end if;else

set recoidNum = 2;--计算最小时间

set minMonitTimeStr = CONCAT(DATE_FORMAT(DATE_SUB(maxMonitTime,INTERVAL 60 DAY),'%Y-%m-%d'),'00:00:00');set minMonitTime = STR_TO_DATE(minMonitTimeStr,'%Y-%m-%d %H:%i:%s');--计算最大时间 hovertree.com

set maxMonitTimeStr = CONCAT(DATE_FORMAT(ADDDATE(maxMonitTime,INTERVAL 4 DAY),'%Y-%m-%d'),'00:00:00');set maxMonitTime = STR_TO_DATE(maxMonitTimeStr,'%Y-%m-%d %H:%i:%s');--计算新建表分区个数

set newIntervalNum = floor(timestampdiff(hour,minMonitTime,maxMonitTime) / intervalHour) + 1;if newIntervalNum < 12 then

set newIntervalNum = 12;end if;--删除所有表分区

set @v_del_s = CONCAT('ALTER TABLE',tablenamein,'remove partitioning');--定义预处理语句

prepare stmt from @v_del_s;--执行预处理语句

executestmt;--释放预处理语句

deallocate preparestmt;--删除所有数据

delete from table_fen_qu where tablename=tablenamein;--新建分区

--设置时间

set minMonitTimeStr = CONCAT('p',DATE_FORMAT(minMonitTime,"%Y%m%d%H%i%s"));--拼接添加分区表sql

set @v_add_s = CONCAT('ALTER TABLE',tablenamein,'PARTITION BY RANGE COLUMNS(moint_time) (PARTITION',minMonitTimeStr,'VALUES LESS THAN (\'',minMonitTime,'\') ENGINE = InnoDB,');--开始在table_fen_qu中添加记录

insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(minMonitTimeStr,tablenamein,minMonitTime,0);while recoidNum <=newIntervalNum do--设置时间

set minMonitTime =ADDDATE(minMonitTime,INTERVAL intervalHour HOUR);set minMonitTimeStr = CONCAT('p',DATE_FORMAT(minMonitTime,"%Y%m%d%H%i%s"));--拼接添加分区表sql

set @v_add_s = CONCAT(@v_add_s,'PARTITION',minMonitTimeStr,'VALUES LESS THAN (\'',minMonitTime,'\') ENGINE = InnoDB,');--开始在table_fen_qu中添加记录

insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(minMonitTimeStr,tablenamein,minMonitTime,0);--记录数加1

set recoidNum = recoidNum + 1;end while;set @v_add_s = left(@v_add_s,LENGTH(@v_add_s)-1);set @v_add_s = CONCAT(@v_add_s,');');--定义预处理语句

prepare stmt from @v_add_s;--执行预处理语句 http://www.cnblogs.com/roucheng/

executestmt;--释放预处理语句

deallocate preparestmt;end if;end general_pro;