MYSQL 定时定时删除表分区

以下实现创建定时事件定时删除表分区, 删除距今最早的七个分区

1、 创建存储过程

CREATE PROCEDURE del_partition()
BEGIN
set @table_num = 7;
set @table_idx = 0;
WHERE @table_idx < @table_num
DO
select partition_name into @p_name from information_schema.partitions where table_name = “https” and table_schema = “test” order by partition_ordinal_position asc limit 1;
set @del_partition_sql =concat('alter table https drop partition ', @p_name);
prepare pre_sql from @del_partition_sql ;
execute pre_sql;
SET @table_idx = @table_idx + 1;
END WHILE
END

2、创建定时事件, 定时调用上面创建的del_partition函数

CREATE event auto_del_partition
on schedule
every 1 day starts ‘2020-04-23 00:00:00’
do
call del_partition；

以上这段代码需要你修改的部分有
1、table_name = ‘你自己的表名’
2、table_schema = ‘你自己的表所属的数据库名’

***注意要将时间戳字段设置为key