mysql定时任务删除表

mysql定时任务删除表

查看event是否开启 : SHOW VARIABLES LIKE ‘%event_sche%’;
将事件计划开启 : SET GLOBAL event_scheduler = 1;
将事件计划关闭 : SET GLOBAL event_scheduler = 0;
关闭事件任务 : ALTER EVENT eventName ON COMPLETION PRESERVE DISABLE;
开启事件任务 : ALTER EVENT eventName ON COMPLETION PRESERVE ENABLE;
查看事件任务 : SHOW EVENTS ;
查看存错过程 show procedure status;

– 创建一个存储过程
DROP PROCEDURE IF EXISTS proc_first; – 如果proc_first存在就删除
create procedure proc_first()-- 创建存储过程
begin
– 定义变量
DECLARE tableName VARCHAR(100);
DECLARE isTable VARCHAR(100);
DECLARE sql_text VARCHAR(200);
– 赋值
set tableName = (select CONCAT(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 31 DAY),’%Y%m%d’),’_action’));
set isTable=(SELECT table_name FROM information_schema.TABLES WHERE table_name =tableName);
– select tableName;
IF isTable is not null THEN
SET sql_text=CONCAT('DROP TABLE ', tableName);
– select sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
end;

– 每天凌晨一点执行

create event if not exists e_test
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
– on schedule every 10 SECOND
on completion preserve
do call proc_first();

– 开启事件任务

ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE;

– 关闭事件任务
ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE;

– 查看事件是否开启
SHOW EVENTS ;