How to automatically delete records before a specified time in 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 the Linux crontab scheduled task and is used for time triggering. By using it alone or calling a stored procedure, the related SQL statement or stored procedure is triggered at a specific point in time.

First, delete the SQL statements 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));

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 an event and customize the stored procedure that executes 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 means on; 0 or OFF means 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 check whether the event plan (scheduler) is currently enabled:

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

Event opening and closing:

ALTER EVENT event_auto_del_memorydata ON COMPLETION PRESERVE ENABLE; //Enable an event ALTER EVENT event_auto_del_memorydata ON COMPLETION PRESERVE DISABLE; //Disable an event

This is the simplest but also the most important. We have to start the timer manually, otherwise it will not 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 the stored procedure:

DROP PROCEDURE pro_clear_data;

Delete Event:

DROP EVENT IF EXISTS event_time_clear_data1

Summarize

The above is the operation method of automatically deleting records before a specified time under MySQL introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!