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 <= 0 then
022
set intervalHour = 6;
023
end if;
024
-- 判斷新增分區表個數
025
if newIntervalNum <= 0 then
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 <= 0 then
043
set recoidNum = 1;
044
while recoidNum <= newIntervalNum do
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 < 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_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
