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

192次阅读

共计 5572 个字符，预计需要花费 14 分钟才能阅读完成。

因为项目需要，最近研究了一下在 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.linuxidc.com
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.linuxidc.com
call del_fenqu();
end ||
DELIMITER ;