mysql动态新建以及删除分区表(二)
VAL 60 DAY),'%Y-%m-%d'),' 00:00:00');
066
set minMonitTime = STR_TO_DATE(minMonitTimeStr,'%Y-%m-%d %H:%i:%s');
067
-- 计算最大时间
068
set maxMonitTimeStr = CONCAT(DATE_FORMAT(ADDDATE(maxMonitTime,INTERVAL 4 DAY),'%Y-%m-%d'),' 00:00:00');
069
set maxMonitTime = STR_TO_DATE(maxMonitTimeStr,'%Y-%m-%d %H:%i:%s');
070
-- 计算新建表分区个数
071
set newIntervalNum = floor(timestampdiff(hour,minMonitTime,maxMonitTime) / intervalHour) + 1;
072
if newIntervalNum < 10 then
073
set newIntervalNum = 10;
074
end if;
075
-- 删除所有表分区
076
set @v_del_s = CONCAT('ALTER TABLE ',tablenamein,' remove partitioning');
077
/*定义预处理语句*/
078
prepare stmt from @v_del_s;
079
/*执行预处理语句*/
080
execute stmt;
081
/*释放预处理语句*/
082
deallocate prepare stmt;
083
-- 删除所有数据
084
delete from table_fen_qu where tablename= tablenamein;
085
-- 新建分区
086
while recoidNum <= newIntervalNum do
087
set minMonitTimeStr = CONCAT('p',DATE_FORMAT(minMonitTime,"%Y%m%d%H%i%s"));
088
-- 开始添加分区表
089
/*拼接分区表代码段*/
090
if recoidNum = 1 then
091
set @v_add_s = CONCAT('ALTER TABLE ',tablenamein,' PARTITION BY RANGE COLUMNS(moint_time) (PARTITION ',minMonitTimeStr,' VALUES LESS THAN (\'',minMonitTime,'\') ENGINE = InnoDB )');
092
else
093
set @v_add_s = CONCAT('ALTER TABLE ',tablenamein,' ADD PARTITION (PARTITION ',minMonitTimeStr,' VALUES LESS THAN (\'',minMonitTime,'\') ENGINE = InnoDB )');
094
end if;
095
/*定义预处理语句*/
096
prepare stmt from @v_add_s;
097
/*执行预处理语句*/
098
execute stmt;
099
/*释放预处理语句*/
100
deallocate prepare stmt;
101
-- 开始在table_fen_qu中添加记录
102
insert into table_fen_qu(fenquname,tablename,monittime,backupflag) VALUES(minMonitTimeStr,tablenamein,minMonitTime,0);
103
-- 记录数加1
104
set recoidNum = recoidNum + 1;
105
set minMonitTime = ADDDATE(minMonitTime,INTERVAL intervalHour HOUR);
106
end while;
107
end if;
108
end general_pro;
删除分区表的存储过程如下：
01
drop procedure if exists del_fenqu;
02
-- 删除'temp_data','no_energy_five_minute_data','energy_five_minute_data'及'energy_five_minute_data_summarize'共4个表中已备份的分区表
03
create procedure del_fenqu()
04
del_fq:begin
05
-- 参数：记录id
06
declare myrecid int;
07
-- 参数：分区表名称
08
declare myfenquname varchar(50);
09
-- 参数：表分区所在表的表名称
10
declare mytablename varchar(50);
11
-- 参数：数据库记录数
12
declare recoidNum int default 0;
13
-- 在该表中，查询符合条件的记录数，backupflag=1说明是已备份
14
select count(*) into recoidNum from table_fen_qu where tablename in ('temp_data','no_energy_five_minute_data','energy_five_minute_data','energy_five_minute_data_summarize') and backupflag=1;
15
-- 存在符合条件的记录则全部删除
16
while recoidNum > 0 do
17
-- 查询出一条
18
select recid,fenquname,tablename into myrecid,myfenquname,mytablename from table_fen_qu where tablename in ('temp_data','no_energy_five_minute_data','energy_five_minute_data','energy_five_minute_da