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

最新推荐文章于 2024-04-15 14:56:11 发布

weixin_33853827

最新推荐文章于 2024-04-15 14:56:11 发布

阅读量198

点赞数

文章标签：数据库大数据 python

原文链接：https://my.oschina.net/u/617773/blog/135963

版权

2019独角兽企业重金招聘Python工程师标准>>>

因为项目需要，最近研究了一下在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');
		-- 计算最大时间
		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;
		-- 执行预处理语句
		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/分区表
		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);
		-- 删除已备份的分区表
		call del_fenqu();
	end ||
DELIMITER ;

转载于:https://my.oschina.net/u/617773/blog/135963