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

页面导航:首页 > 数据库 > Mysql > mysql动态新建以及删除分区表

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

来源: 作者: 时间:2016-02-21 09:46 【

mysql动态新建以及删除分区表因为项目需要,最近研究了一下在mysql数据库下如何动态新建以及删除分区表。如果全部借助存储过程的话,新建以及删除分区表在逻辑上比较死板、不灵活
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
 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<