mysql 動態新建以及刪除分區表，mysql分區表

mysql 動態新建以及刪除分區表，mysql分區表

因為項目需要，最近研究了一下在mysql數據庫下如何動態新建以及刪除分區表。如果全部借助存儲過程的話，新建以及刪除分區表在邏輯上比較死板、不靈活，而且還容易出錯。因此，我新建了一個數據表table_fen_qu，借助這個表可以很(相對)靈活的對分區表進行管理。

在首次創建分區表時，若單獨一條分區表數據一條分區表數據的添加，此時rang的列值大於該表中此列的最大值時，可以創建，否則失敗。在這裡，我是把創建分區表的代碼放到一起執行的(見general_procedure中的else語句段)。以下是操作過程，不足之處請各位看官指正。

第一步：建立存儲過程。
建立新建分區表的存儲過程代碼如下：

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');
        -- 計算最大時間 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;
        -- 執行預處理語句
        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;
        -- 執行預處理語句 http://www.cnblogs.com/roucheng/
        execute stmt; 
        -- 釋放預處理語句 
        deallocate prepare stmt;
    end if;
end general_pro;

第二步：建立事件計劃，定時執行事件。
事件如下：

-- 打開事件計劃
SET GLOBAL event_scheduler = ON;
/*創建從開始時間每隔1天定時執行*/
drop event if exists eachDayEvent;
DELIMITER ||
create event eachDayEvent
    on schedule every 1 day  starts '2013-05-01 00:00:00'
    on completion preserve enable
do 
    begin
        -- general_procedure的參數：表名，分區表之間的時間間隔(單位為小時)，要新增的分區表個數
        -- 非能耗5分鐘表-間隔6小時-6h/分區表 hovertree.com
        call general_procedure('no_energy_five_minute_data',6,8);
        -- 原始數據表-間隔6小時-6h/分區表
        call general_procedure('temp_data',6,8);
        -- 能耗五分鐘-間隔天-24h/分區表
        call general_procedure('energy_five_minute_data',24,4);
        -- 能耗五分鐘匯總-間隔天-24h/分區表
        call general_procedure('energy_five_minute_data_summarize',24,4);
        -- 能耗小時表調用-間隔周-7*24h/分區表 何問起
        call general_procedure('energy_hour_data_summarize',168,4);
        -- 能耗分類分項5分鐘表-間隔周7*24h/分區表
        call general_procedure('energy_item_five_minute_data',168,4);
        -- 能耗分類分項小時表-間隔季度-90*24h/分區表
        call general_procedure('energy_item_hour_data',2160,4);
        -- 能耗天匯總表-間隔半年-4380h/分區表
        call general_procedure('energy_day_data_summarize',4380,4);
        -- 刪除已備份的分區表 http://www.cnblogs.com/roucheng/
        call del_fenqu();
    end ||
DELIMITER ; 

http://www.cnblogs.com/roucheng/p/mysqlfenqu.html