你的位置:首页 > 数据库

[数据库]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 enabledo   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