Mysql定时器定时删除表数据

 查看mysql是否开启定时任务开关

SHOW VARIABLES LIKE 'event_scheduler';

 Value为ON则已打开，OFF则关闭

如果是OFF，就先打开：

#  SET GLOBAL event_scheduler = ON;

然后创建我们想要的定时器

mysql> DELIMITER $$  
DROP EVENT IF EXISTS DELETELOG;  
CREATE EVENT  DELETELOG  
ON SCHEDULE EVERY 300 SECOND  
ON COMPLETION PRESERVE  
DO BEGIN  
  DELETE FROM CPS_RENT_BILLDETAILS;  
  DELETE FROM CPS_RENT_BILLS; 
  DELETE FROM CPS_RENT_ITEM; 
END$$  
DELIMITER ; 

该脚本的意思是：每300秒执行一次计划

ON SCHEDULE EVERY 1 DAY（每1天执行一次）

ON SCHEDULE EVERY 300 SECOND（每5分钟执行一次）

创建完成后，查看定时器

select * from  mysql.event;
或者
SHOW EVENTS;

--开启某事件：

ALTER EVENT `DELETELOG` ON COMPLETION PRESERVE ENABLE;

--关闭某事件：

ALTER EVENT `DELETELOG` ON COMPLETION PRESERVE DISABLE;

--删除

drop  EVENT `DELETELOG`;

--查询事件是否启用

SHOW VARIABLES LIKE 'event_scheduler';