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! You may also be interested in:
|
<<: Nginx proxy axios request and precautions
>>: Detailed explanation of Linux curl form login or submission and cookie usage
Preface I always thought that UTF-8 was a univers...
Apache Log4j2 reported a nuclear-level vulnerabil...
This article shares the specific code of Vue to s...
Sometimes you need to use links, but you don't...
Environment Introduction Operating system: centos...
This article example shares the specific code of ...
The color presentation on a web page will be affec...
Table of contents Overview Code Implementation Su...
In the previous article, we introduced: MySQL8.0....
MySQL server has gone away issue in PHP 1. Backgr...
When we write CSS, we sometimes forget about the ...
Achieve results The code is as follows html <t...
There is often a scenario where the image needs t...
1. Install Oracle There are too many Oracle insta...
This article will introduce a very interesting at...