Operation method of automatically deleting records before specified time under Mysql

About Event: mysql 5.1 began to introduce the concept of event. event is a "time trigger", which is different from the event trigger of triggers. event is similar to linux crontab planning task, which is used for time trigger. Triggers the associated SQL statement or stored procedure at a specific point in time, either individually or by calling a stored procedure.

First delete the SQL statement recorded 2 days ago (webserver_monitormemory is the table name, time is the time field):

delete From webserver_monitormemory where DATE(time) <= DATE(DATE_SUB(NOW(),INTERVAL 2 day));

To create a stored procedure:

DELIMITER //
CREATE PROCEDURE autodel()
  -> BEGIN
  -> delete From webserver_monitormemory where DATE(time) <= DATE(DATE_SUB(NOW(),INTERVAL 2 day));
  -> END
  -> //
DELIMITER ;

Create events to customize stored procedures that execute autodel every day:

CREATE EVENT `event_auto_del_memorydata`  
ON SCHEDULE EVERY 1 DAY STARTS '2017-11-20 00:00:00'  
ON COMPLETION NOT PRESERVE ENABLE DO CALL autodel();

There are 4 ways to start the event plan (scheduler). The key value 1 or ON indicates on; 0 or OFF for off:

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

There are three ways to see if the event plan (scheduler) is currently turned on:

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

Event on and off:

ALTER EVENT event_auto_del_memorydata ON COMPLETION PRESERVE ENABLE; // Open an event 
ALTER EVENT event_auto_del_memorydata ON COMPLETION PRESERVE DISABLE; // Close an event 

This is the simplest but most important thing. We have to start this timer manually, otherwise it won't work.

ALTER EVENT event_time_clear_data ON 
COMPLETION PRESERVE ENABLE;

In addition, the code to turn off the timer is:

ALTER EVENT event_time_clear_data ON 
COMPLETION PRESERVE DISABLE;

Delete stored procedure:

DROP PROCEDURE pro_clear_data;

Delete Event:

DROP EVENT IF EXISTS event_time_clear_data1

Summarize