我要评论mysql分区创建与删除
实例代码
create procedure `add_table_partition`()
comment '增加表分区'
begin
/******************************************************************
* creator: donne
* create date: 2019-05-29
* description: 增加所有表的分区，分区类型为p_date_20190529
每月最后一天执行，生成下一个月所有分区
******************************************************************/
declare v_table_name varchar(50);
declare v_par_name varchar(20);
declare i int default 0;
declare no_more int default 0;
##将表名和分区名放入游标
declare cursor_employee cursor for
select table_name,max(partition_name)
from information_schema.partitions
where table_schema='test' and partition_name is not null and partition_name<>'p_date_min'
group by table_name;
declare continue handler for not found set no_more= 1;
##打开游标
open cursor_employee;
fetch cursor_employee into v_table_name,v_par_name;
##循环 1:ture, 0:false,!0:ture,!1:false
##select 1 from where !0
while !no_more do
my_loop: loop
##从当前开始
set @j:= date_add(curdate(),interval i day);
##下个月最后一天
set @end_date:= last_day(date_add(last_day(curdate()),interval 1 day));
if @j > @end_date then
leave my_loop;
end if;
#计数
set i=i+1;
#分区使用values less than,所以+1
set @par_value:= unix_timestamp(date_add(curdate(),interval i day));
set @par_name:= concat('p_date_',date_format(@j,'%y%m%d'));
#如果新建分区大于已有分区，则创建
if @par_name>v_par_name then
set @add_par:= concat('alter table ',v_table_name,' add partition(partition ',@par_name,' values less than (',@par_value,'));');
prepare stmt from @add_par;
execute stmt;
deallocate prepare stmt;
end if;
end loop;
#重新计数
set i= 0;
#从游标中取出下一条数据
fetch cursor_employee into v_table_name,v_par_name;
end while;
close cursor_employee;
end
create procedure `drop table partition`(
in start_date date ,
in end_date date
)
comment '删除分区'
begin
/******************************************************************
* creator: donne
* create date: 2019-05-29
* description: 删除表分区
******************************************************************/
declare v_table_name varchar(50);
declare v_par_name varchar(20);
declare i int default 0;
declare no_more int default 0;
##将表名和分区名放入游标
declare cursor_employee cursor for
select table_name,partition_name
from information_schema.partitions
where table_schema='test' and partition_name is not null and partition_name<>'p_date_min'
and partition_name>=concat('p_date_',date_format(end_date,'%y%m%d'))
and partition_name<=concat('p_date_',date_format(end_date,'%y%m%d'));
declare continue handler for not found set no_more= 1;
##打开游标
open cursor_employee;
fetch cursor_employee into v_table_name,v_par_name;
##循环
while !no_more do
set @drop_par:= concat('alter table ',v_table_name,' drop partition ',v_par_name,';');
prepare stmt from @drop_par;
execute stmt;
deallocate prepare stmt;
fetch cursor_employee into v_table_name,v_par_name;
end while;
close cursor_employee;
end
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论