因为项目需要，最近研究了一下在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

最新文章

  1. DevExpress 在使用Ribbon皮肤时标题栏不变化的原因
  2. ABAP程序运行锁定
  3. 新增了个job
  4. AC日记——计算多项式的导函数 openjudge 1.5 38
  5. 【Android学习】数据传递三种方式
  6. hql语句查询实体类采用list方法的返回结果集
  7. 【转】Asp.Net MVC3 简单入门详解过滤器Filter
  8. Android IOS WebRTC 音视频开发总结（二四）-- p2p调用堆栈
  9. SQL语句基本概念
  10. c++应用程序文件的编译过程
  11. 三种Join方法
  12. 201521123069 《Java程序设计》 第13周学习总结
  13. win下搭建python3+PyQt5+eric6环境
  14. django项目实现中文检索
  15. vue 在全局设置cookie main.js文件
  16. Redis sentinel之集群搭建
  17. CentOS下双网卡双IP不同IP段配置
  18. springboot RestTemplate httpclient
  19. Java IO流及应用（一）
  20. 转:CMake快速入门教程-实战

热门文章

  1. No.020：Valid Parentheses
  2. append,appendTo和prepend #1daae2
  3. Git远程和分支管理
  4. 高性能 Windows Socket 组件 HP-Socket v3.0.2 正式发布
  5. Criteria查询初学者的采纳点哦
  6. 删除 QQ 最新版右键菜单 通过QQ发送文件到手机
  7. (原) 1.1 Zookeeper单机安装
  8. js改变css样式的三种方法
  9. 超酷HTML5 Canvas图表应用Chart.js自定义提示折线图
  10. Mondrian – 开源的矢量图形 Web 应用程序