【MySQL】用存储过程删除历史数据

创建存储过程：

DELIMITER //
CREATE PROCEDURE autodelete()
    -> BEGIN
    -> delete From amap_log where DATE(time) <= DATE(DATE_SUB(NOW(),INTERVAL 30 day));
    -> END
    -> //
DELIMITER ;

创建事件，定制每天执行autodel的存储过程：

CREATE EVENT `event_auto_delete_amap_log`   
ON SCHEDULE EVERY 1 DAY STARTS '2019-06-14 00:00:00'   
ON COMPLETION NOT PRESERVE ENABLE DO CALL autodelete();  

开启事件计划（调度器）4种方法。键值1或者ON表示开启；0或者OFF表示关闭：

SET GLOBAL event_scheduler = 1;  
SET @@global.event_scheduler = 1;  
SET GLOBAL event_scheduler = ON;  
SET @@global.event_scheduler = ON; 

查看当前是否已开启事件计划（调度器）3种方法：

SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
SHOW PROCESSLIST;​

事件开启与关闭：

ALTER EVENT event_auto_delete_amap_log ON COMPLETION PRESERVE ENABLE; 
ALTER EVENT event_auto_delete_amap_log ON COMPLETION PRESERVE DISABLE;