存储过程

1、查看表的分区情况：

select partition_name, FROM_UNIXTIME(partition_description) from information_schema.partitions
    where table_schema = [databasename] and table_name = [tablename] order by partition_description desc;

2、查看定时任务功能是否开启：

show variables like 'event_scheduler';

3、查看事件

show events;

4、定义事件

DELIMITER ||
drop event if exists event_name||
create event event_name
on schedule 
every 1 SECOND
starts CURRENT_TIME
do
BEGIN
    -- 执行事件
END ||
DELIMITER ;

5、demo

-- README!!!
-- 1、打开定时任务功能
-- 2、分区的表一定要有手动分表
-- 3、设置 databasename
-- 4、设置 tablename

SET GLOBAL event_scheduler = ON;

ALTER TABLE tableName PARTITION BY RANGE(UNIX_TIMESTAMP(DAY))
(
    PARTITION p20200315 VALUES LESS THAN (UNIX_TIMESTAMP('2020-03-16 00:00:00')),
    PARTITION p20200316 VALUES LESS THAN (UNIX_TIMESTAMP('2020-03-17 00:00:00')),
    PARTITION p_max VALUES LESS THAN MAXVALUE
)


-- 分区类型[0按天分区，1按月分区，2按年分区]

DELIMITER ||
drop procedure if exists auto_set_partitions ||
create procedure auto_set_partitions ()
L_END:
begin
    declare databasename varchar(50) default 'databaseName';
    declare tablename varchar(50) default 'tableName';
    declare partition_number int default 1;
    declare partitiontype int default 0;
    declare gaps int default 1;
    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_UNIXTIME(max_partition_description), interval i*gaps day);
                 elseif (partitiontype = 1) then
                     set p_description = DATE_ADD(FROM_UNIXTIME(max_partition_description), interval i*gaps month);
                 else
                     set p_description = DATE_ADD(FROM_UNIXTIME(max_partition_description), interval i*gaps year);
                 end if;

                 set p_name = FROM_UNIXTIME(max_partition_description);
                 set p_name = REPLACE(p_name, '-', '');
                 set p_name = REPLACE(p_name, ':', '');
                 set p_name = LEFT(p_name, 8);
                 set p_description = DATE_ADD(p_description, interval 0 day);

                 set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (UNIX_TIMESTAMP(\'', p_description ,'\')))');
                 select @sql;
                 PREPARE stmt from @sql;
                 EXECUTE stmt;
                 DEALLOCATE PREPARE stmt;
                 set i = (i + 1) ;
    end while;
end ||
DELIMITER ;

DELIMITER ||
drop event if exists auto_set_partitions  ||
create event auto_set_partitions
on schedule every 1 day
starts '2020-03-19 00:00:00'
do
BEGIN
    call auto_set_partitions();
END ||
DELIMITER ;