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

最新推荐文章于 2024-06-13 09:01:19 发布

weixin_39794385

最新推荐文章于 2024-06-13 09:01:19 发布

阅读量219

点赞数

文章标签： mysql动态分区表

本文链接：https://blog.csdn.net/weixin_39794385/article/details/113155834

版权

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