How to automatically delete records before a specified time in Mysql

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!

You may also be interested in:
  • MySQL scheduled task example tutorial
  • How to implement Mysql scheduled tasks under Linux
  • Using MySQL in Windows: Implementing Automatic Scheduled Backups
  • Detailed explanation of MySQL database Event scheduled execution tasks
  • Commonplace talk about MySQL event scheduler (must read)
  • Detailed explanation of the usage of event in MySQL
  • Detailed explanation of mysql scheduled tasks (event events)

<<:  Nginx proxy axios request and precautions

>>:  Detailed explanation of Linux curl form login or submission and cookie usage

Recommend

Solution to the problem of failure to insert emoji expressions into MySQL

Preface I always thought that UTF-8 was a univers...

Apache Log4j2 reports a nuclear-level vulnerability and a quick fix

Apache Log4j2 reported a nuclear-level vulnerabil...

Vue simple implementation of turntable lottery

This article shares the specific code of Vue to s...

Detailed process of zabbix monitoring process and port through agent

Environment Introduction Operating system: centos...

Vue ElementUI implements asynchronous loading tree

This article example shares the specific code of ...

Essential Handbook for Web Design 216 Web Safe Colors

The color presentation on a web page will be affec...

JavaScript design pattern learning adapter pattern

Table of contents Overview Code Implementation Su...

Detailed tutorial on compiling and installing MySQL 8.0.20 from source code

In the previous article, we introduced: MySQL8.0....

Solution to the MySQL server has gone away error

MySQL server has gone away issue in PHP 1. Backgr...

Detailed explanation of how to solve the problem of too long content in CSS

When we write CSS, we sometimes forget about the ...

Vue project implements left swipe delete function (complete code)

Achieve results The code is as follows html <t...

Example code for implementing image adaptive container with CSS

There is often a scenario where the image needs t...

How to install Oracle on Windows Server 2016

1. Install Oracle There are too many Oracle insta...

Detailed explanation of the wonderful CSS attribute MASK

This article will introduce a very interesting at...