drop procedure if exists general_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 default SYSDATE();
-- 参数:最大时间对应的字符串
declare maxMonitTimeStr varchar(50);
-- 参数:最小时间
declare minMonitTime datetime default SYSDATE();
-- 参数:最大时间对应的字符串
declare minMonitTimeStr varchar(50);
-- 参数:数据库记录数
declare recoidNum int default 0;
-- 判断传入的表名是否为空
if tablenamein is null then
leave 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 do
set 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;
-- 执行预处理语句
execute stmt;
-- 释放预处理语句
deallocate prepare stmt;
-- 开始在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');
-- 计算最大时间
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;
-- 执行预处理语句
execute stmt;
-- 释放预处理语句
deallocate prepare stmt;
-- 删除所有数据
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;
-- 执行预处理语句
execute stmt;
-- 释放预处理语句
deallocate prepare stmt;
end if;
end general_pro;
-- 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 default SYSDATE();
-- 参数:最大时间对应的字符串
declare maxMonitTimeStr varchar(50);
-- 参数:最小时间
declare minMonitTime datetime default SYSDATE();
-- 参数:最大时间对应的字符串
declare minMonitTimeStr varchar(50);
-- 参数:数据库记录数
declare recoidNum int default 0;
-- 判断传入的表名是否为空
if tablenamein is null then
leave 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 do
set 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;
-- 执行预处理语句
execute stmt;
-- 释放预处理语句
deallocate prepare stmt;
-- 开始在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');
-- 计算最大时间
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;
-- 执行预处理语句
execute stmt;
-- 释放预处理语句
deallocate prepare stmt;
-- 删除所有数据
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;
-- 执行预处理语句
execute stmt;
-- 释放预处理语句
deallocate prepare stmt;
end if;
end general_pro;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23490154/viewspace-1063384/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23490154/viewspace-1063384/