mysql动态新建以及删除分区表
因为项目需要，最近研究了一下在mysql数据库下如何动态新建以及删除分区表。如果全部借助存储过程的话，新建以及删除分区表在逻辑上比较死板、不灵活，而且还容易出错。因此，我新建了一个数据表table_fen_qu，借助这个表可以很(相对)灵活的对分区表进行管理。以下是操作过程，不足之处请各位看官指正。
第一步：建立存储过程。
建立新建分区表的存储过程代码如下：
001
drop procedure if exists general_procedure;
002
-- general_procedure的作用：新建分区表及在table_fen_qu表中存储新建分区表时的相关参数
003
-- general_procedure的参数：表名，分区表之间的时间间隔(单位为小时)，要新增的分区表个数
004
create procedure general_procedure(in tablenamein varchar(50),in intervalhour int,in newintervalnum int)
005
general_pro:begin
006
-- 参数：最大时间
007
declare maxmonittime datetime default sysdate();
008
-- 参数：最大时间对应的字符串
009
declare maxmonittimestr varchar(50);
010
-- 参数：最小时间
011
declare minmonittime datetime default sysdate();
012
-- 参数：最大时间对应的字符串
013
declare minmonittimestr varchar(50);
014
-- 参数：数据库记录数
015
declare recoidnum int default 0;
016
-- 判断传入的表名是否为空
017
if tablenamein is null then
018
leave general_pro;
019
end if;
020
-- 判断传入的时间间隔
021
if intervalhour
022
set intervalhour = 6;
023
end if;
024
-- 判断新增分区表个数
025
if newintervalnum
026
set newintervalnum = 1;
027
end if;
028
029
-- 在该表中，查询符合条件的记录数，backupflag=0说明是未备份
030
select count(*) into recoidnum from table_fen_qu where tablename=tablenamein and backupflag=0;
031
if recoidnum > 0 then
032
-- 查询该表在table_fen_qu表中的最大监测时间
033
select monittime into maxmonittime from table_fen_qu where tablename=tablenamein and backupflag=0 order by monittime desc limit 1;
034
-- 判断监测时间是否为null
035
if maxmonittime is null then
036
set maxmonittime = sysdate();
037
end if;
038
-- 比较最大时间减去72个小时之后的时间与系统时间的早晚
039
set recoidnum = timestampdiff(hour,sysdate(),date_sub(maxmonittime,interval 3 day));
040
-- 如果recoidnum大于0，说明最大监测时间减去72小时之后的时间仍然在系统时间之后,
041
-- 说明不用建立新的分区表，反之，则建立最大监测时间之后newintervalnum个以每intervalhour小时为间隔的分区表
042
if recoidnum
043
set recoidnum = 1;
044
while recoidnum
045
set maxmonittime = adddate(maxmonittime,interval intervalhour hour);
046
set maxmonittimestr = concat('p',date_format(maxmonittime,%y%m%d%h%i%s));
047
-- 开始添加分区表
048
/*拼接分区表代码段*/
049
set @v_add_s = concat('alter table ',tablenamein,' add partition (partition ',maxmonittimestr,' values less than (/'',maxmonittime,'/') engine = innodb )');
050
/*定义预处理语句*/
051
prepare stmt from @v_add_s;
052
/*执行预处理语句*/
053
execute stmt;
054
/*释放预处理语句*/
055
deallocate prepare stmt;
056
-- 开始在table_fen_qu中添加记录
057
insert into table_fen_qu(fenquname,tablename,monittime,backupflag) values(maxmonittimestr,tablenamein,maxmonittime,0);
058
-- 记录数加1
059
set recoidnum = recoidnum + 1;
060
end while;
061
end if;
062
else
063
set recoidnum = 1;
064
-- 计算最小时间
065
set minmonittimestr = concat(date_format(date_sub(maxmonittime,interval 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
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
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_data_summarize') and backupflag=1 order by monittime desc limit 1;
19
-- 删除数据记录
20
delete from table_fen_qu where recid = myrecid;
21
-- 删除表分区
22
/*拼接分区表代码段*/
23
set @v_drop_d=concat('alter table ',mytablename,' drop partition ',myfenquname);
24
/*定义预处理语句*/
25
prepare stmt from @v_drop_d;
26
/*执行预处理语句*/
27
execute stmt;
28
/*释放预处理语句*/
29
deallocate prepare stmt;
30
-- 在该表中，查询符合条件的记录数，backupflag=1说明是已备份
31
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;
32
end while;
33
end del_fq;
第二步：建立事件计划，定时执行事件。
事件如下：
01
-- 打开事件计划
02
set global event_scheduler = on;
03
/*创建从开始时间每隔1天定时执行*/
04
drop event if exists eachdayevent;
05
delimiter ||
06
create event eachdayevent
07
on schedule every 1 day starts '2013-05-01 00:00:00'
08
on completion preserve enable
09
do
10
begin
11
-- general_procedure的参数：表名，分区表之间的时间间隔(单位为小时)，要新增的分区表个数
12
-- 非能耗5分钟表-间隔6小时-6h/分区表
13
call general_procedure('no_energy_five_minute_data',6,8);
14
-- 原始数据表-间隔6小时-6h/分区表
15
call general_procedure('temp_data',6,8);
16
-- 能耗五分钟-间隔天-24h/分区表
17
call general_procedure('energy_five_minute_data',24,4);
18
-- 能耗五分钟汇总-间隔天-24h/分区表
19
call general_procedure('energy_five_minute_data_summarize',24,4);
20
-- 能耗小时表调用-间隔周-7*24h/分区表
21
call general_procedure('energy_hour_data_summarize',168,4);
22
-- 能耗分类分项5分钟表-间隔周7*24h/分区表
23
call general_procedure('energy_item_five_minute_data',168,4);
24
-- 能耗分类分项小时表-间隔季度-90*24h/分区表
25
call general_procedure('energy_item_hour_data',2160,4);
26
-- 能耗天汇总表-间隔半年-4380h/分区表
27
call general_procedure('energy_day_data_summarize',4380,4);
28
-- 删除已备份的分区表
29
call del_fenqu();
30
end ||
31
delimiter ;
32
bitscn.com