mysql动态新建以及删除分区表_MySQL

bitsCN.com

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