支持年月日不同间隔分区
代码语言：javascript
复制DELIMITER ||
drop procedure if exists auto_create_partitions ||
create procedure auto_create_partitions_tsec (in databasename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,in tablename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, in partition_number int, in partitiontype int, in gaps int)
L_END:
begin
declare max_partition_description varchar(255) default '';
declare p_name varchar(255) default 0;
declare p_description varchar(255) default 0;
declare isexist_partition varchar(255) default 0;
declare i int default 1;
select partition_name into isexist_partition from information_schema.partitions where table_schema = databasename and table_name = tablename limit 1;
if isexist_partition <=> "" then
select "partition table not is exist" as "ERROR";
leave L_END;
end if;
select partition_description into max_partition_description from information_schema.partitions where table_schema = databasename and table_name = tablename order by partition_description desc limit 1;
if max_partition_description <=> "" then
select "partition table is error" as "ERROR";
leave L_END;
end if;
while (i <= partition_number) do
if (partitiontype = 0) then
set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps day);
elseif (partitiontype = 1) then
set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps month);
else
set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps year);
end if;
set p_name = REPLACE(p_description, ' ', '');
set p_name = REPLACE(p_name, '-', '');
set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS('', p_description ,'')))');
select @sql;
PREPARE stmt from @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
set i = (i 1);
end while;
end; ||
调用示例
代码语言：javascript
复制-- 按照天分区
-- 参数说明
-- 第一个参数：数据库；
-- 第二个参数：分区表名；
-- 第三个参数：分区数；第四个参数：0天，1月，其他：年；
-- 第四个参数间隔天数，月数，年数
call auto_create_partitions('database', 'tablename', 100, 0, 7);
事件创建
代码语言：javascript
复制DELIMITER ||
drop event if exists auto_create_partitions_event ||
create event auto_create_partitions_event
on schedule every 1 day
starts '2021-05-20 13:00:00'
do
BEGIN
call auto_create_partitions('database', 'tablename', 100, 0, 7);
END; ||
