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 ;