定期生成分区和合并分区

delimiter //

create procedure req_add_partition(spec_date DateTime)
COMMENT '生成requestMessage表spec_date这天的分区'
begin
declare dateInt varchar(10);
declare dateStr varchar(10);
declare sqlStr varchar(400);
select DATE_FORMAT(spec_date,'%y%m%d') into dateInt;
select spec_date+INTERVAL 1 DAY into dateStr;
set sqlStr =concat("alter table requestMessage add partition ( partition p",dateInt," VALUES LESS THAN (to_days('",dateStr,"')))");
set @v_sql=sqlStr;
prepare stmt from @v_sql;
execute stmt;
insert into SYS_Logs(evt_type,content) values('req_add_partition',concat('执行req_add_partition,添加',dateInt,'成功'));

end //
delimiter ;


delimiter //
create procedure req_addnextmonth_partitions(spec_date DateTime)
COMMENT '每个月底前7天生成requestMessage表spec_date的下个月的分区'
begin
declare cur_lastDay DateTime;
declare firstDay DateTime;
declare endDay DateTime;

set cur_lastDay=LAST_DAY(spec_date);
set firstDay=cur_lastDay + INTERVAL 1 DAY;
set endDay =LAST_DAY(firstDay);

if DATEDIFF(cur_lastDay,spec_date)<=31 then
set @cur_day=firstDay;
dayloop:loop
if @cur_day >endDay then
leave dayloop;
end if;
call req_add_partition(@cur_day);
set @cur_day=@cur_day+INTERVAL 1 DAY;
end loop;
insert into SYS_Logs(evt_type,content) values('req_addnextmonth_partitions',concat('执行req_addnextmonth_partitions,生成下月分区成功'));
end if ;
insert into SYS_Logs(evt_type,content) values('req_addnextmonth_partitions',concat('执行req_addnextmonth_partitions成功'));
end //
delimiter ;

delimiter //
CREATE EVENT evt_req_addpartition
ON SCHEDULE EVERY 1 day
ON COMPLETION PRESERVE
COMMENT '每天定期执行 req_addnextmonth_partitions存储过程'
do call req_addnextmonth_partitions(curdate());
//
delimiter ;


delimiter //
create procedure req_merge_partition(spec_time DateTime)
COMMENT '每个月8号合并requestMessage表spec_date上个月的所有分区'
begin
declare firstDay DateTime; #上个月第一天
declare endDay DateTime; #上个月最后一天
declare sqlStr varchar(600); #执行的SQL
declare tmpDay DateTime; #临时日期
declare partsStr varchar(600); #partitions列表串
declare dateInt varchar(10); #tmpDay的'091001'形式
declare dateStr varchar(10); #这个月第一天的'2009-10-01'形式
declare mergeName varchar(10); #合并后的分区名称'p0910'


select date_sub(date_sub(date_format(spec_time,'%y-%m-%d'),interval extract(day from spec_time)-1 day),interval 1 month) into firstDay;
select date_sub(date_sub(date_format(spec_time,'%y-%m-%d'),interval extract(day from spec_time) day),interval 0 month) into endDay;
set mergeName=concat('p',DATE_FORMAT(firstDay,'%y%m'));
select endDay+INTERVAL 1 DAY into dateStr;
set tmpDay =firstDay;


if DATEDIFF(spec_time,endDay+INTERVAL 1 DAY)>=13 then
set partsStr='';
dayLoop:loop
if tmpDay > endDay then
leave dayLoop;
end if;
select DATE_FORMAT(tmpDay,'%y%m%d') into dateInt;
set partsStr=concat(partsStr,'p',dateInt);
if tmpDay < endDay then
set partsStr=concat(partsStr,',');
end if;
set tmpDay=tmpDay+INTERVAL 1 DAY;
end loop;
set sqlStr=concat("alter table requestMessage reorganize partition ",partsStr," into (partition ",mergeName," values less than (to_days('",dateStr,"')))");
insert into SYS_Logs(evt_type,content) values('req_merge_partition ',concat(sqlStr,'执行完成'));
set @v_sql=sqlStr;
prepare stmt from @v_sql;
execute stmt;
end if;
insert into SYS_Logs(evt_type,content) values('req_merge_partition ','req_merge_partition执行完成');
end
//

delimiter ;

delimiter //
CREATE EVENT evt_req_mergepartition
ON SCHEDULE EVERY 1 day
ON COMPLETION PRESERVE
COMMENT '每天定期执行 req_merge_partition存储过程'
do call req_merge_partition(curdate());
//
delimiter ;