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

因为项目需要,最近研究了一下在mysql数据库下如何动态新建以及删除分区表。如果全部借助存储过程的话,新建以及删除分区表在逻辑上比较死板、不灵活,而且还容易出错。因此,我新建了一个数据表table_f

大家好,又见面了,我是全栈君,今天给大家准备了Idea注册码。

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

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/120682.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 数据仓库ods层设计_数据仓库建模的流程有几个

    数据仓库ods层设计_数据仓库建模的流程有几个当我们的数据采集到hdfs层上之后,我们就开开始对数据进行建模以便后来分析,那么我们整体的架构先放在每个建模层级的最前面所以项目1的将行为数据和业务数据导入到hdfs中我们已经完成了,现在需要的是将hdfs的数据通过ODS层数据建模,初步的分析以及改变,那么我们首先介绍下ODS层的作用因为我们的数据刚落到hdfs上,他还只是单纯的数据,并没有能让我们直接操作。所以我们需要将这些数据放入到能够对数据进行操作的框架中,如我们这个项目采取了使用hive的方法。所以我们此次在ODS层需要做到的就是将hdfs

  • ViewPager的addOnPageChangeListener方法详解

    ViewPager的addOnPageChangeListener方法详解关于上篇博客中写道了ViewPager的拖动状态监听的方法有网友私信问我不太明白里面的逻辑,我估摸着还是对ViewPager不太熟悉,所以就没一一回复在此开篇帖子写下关于ViewPager的具体用法,ViewPage使用时,最关键的代码就是addOnPageChangeListener,传入一个ViewPager.OnPageChangeListener对象。下面做先给大家贴上google的官方源

  • 华硕怎么安装linux系统教程,华硕笔记本系统如何安装win10和linux 双系统[通俗易懂]

    华硕怎么安装linux系统教程,华硕笔记本系统如何安装win10和linux 双系统[通俗易懂]稍微了整理了一下win10和linux双系统的安装教程,第一个选项是进入U盘linuxlive,等等)第一点设置boot挂载点。设置BIOS。然后点击试用用UBUNTU,但是windows上面的数据又删除不得,使用Ultraiso把LINUXUBUNTU14.X这个iso文件【写入硬盘影像】到U盘。第三个自己看,去正规的网站现在,6设置完毕,我这里设置了5GB其余的空间我全部分给/us…

  • Faster R-CNN算法

    Faster R-CNN算法  FasterR-CNN算法是作者RossGirshick对FastR-CNN算法的一种改进。FastR-CNN在速度和精度上都有了不错的结果,但仍有一些不足之处。FasterR-CNN算法同样使用VGG-16网络结构,检测速度在GPU上达到5fps(包括候选区域的生成),准确率也有进一步的提升。在ILSVRC和COCO2015竞赛中获得多个项目的第一名。在FasterR-CNN中提出了区域生成网络(RegionProposalNetwork,RPN),将候选区域的提取和FastR-C

  • mysql 创建存储过程语法_mysql存储过程语法及实例[通俗易懂]

    mysql 创建存储过程语法_mysql存储过程语法及实例[通俗易懂]存储过程如同一门程序设计语言,同样包含了数据类型、流程控制、输入和输出和它自己的函数库。——————–基本语法——————–一.创建存储过程createproceduresp_name()begin………end二.调用存储过程1.基本语法:callsp_name()注意:存储过程名称后面必须加括号,哪怕该存储过程没有参数传递三….

  • oidc auth2.0_使用Spring Security 5.0和OIDC轻松构建身份验证「建议收藏」

    oidc auth2.0_使用Spring Security 5.0和OIDC轻松构建身份验证「建议收藏」oidcauth2.0“我喜欢编写身份验证和授权代码。”〜从来没有Java开发人员。厌倦了一次又一次地建立相同的登录屏幕?尝试使用OktaAPI进行托管身份验证,授权和多因素身份验证。SpringSecurity不仅是一个功能强大且可高度自定义的身份验证和访问控制框架,它还是保护基于Spring的应用程序的实际标准。从前,SpringSecurity需要使用大量的XML来…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号