delimiter $$
use
`db_orbit`$$
drop procedure
if
exists `create_partition_by_month`$$
create procedure `create_partition_by_month`(in_schemaname varchar(64), in_tablename varchar(64))
begin
# 用于判断需要创建的表分区是否已经存在
declare
rows_cnt int unsigned;
# 要创建表分区的时间
declare
target_date timestamp;
#分区的名称,格式为p201811
declare
partition_name varchar(8);
#要创建的分区时间为下个月
set target_date = date_add(now(), interval 1 month);
set partition_name = date_format( target_date,
'p%Y%m'
);
# 判断要创建的分区是否存在
select
count
(1) into rows_cnt from information_schema.partitions t where table_schema = in_schemaname
and
table_name = in_tablename
and
ifnull(t.partition_name,
''
) = partition_name;
if
rows_cnt = 0 then
set @sql = concat(
'alter table `'
,
in_schemaname,
'`.`'
,
in_tablename,
'`'
,
' add partition (partition '
,
partition_name,
" values less than (to_days('"
,
date_format(DATE_ADD(target_date, INTERVAL 1 month),
'%Y-%m-01'
),
"')) engine = innodb);"
);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
else
select concat(
"partition `"
, partition_name,
"` for table `"
,in_schemaname,
"."
, in_tablename,
"` already exists"
)
as
result;
end
if
;
end
$$
delimiter ;