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

Use CSS content attr to achieve mouse hover prompt (tooltip) effect

Why do we achieve this effect? ​​In fact, this ef...

Play with the connect function with timeout in Linux

In the previous article, we played with timeouts ...

Why should the number of rows in a single MySQL table not exceed 5 million?

Today, let’s discuss an interesting topic: How mu...

How to build a tomcat image based on Dockerfile

Dockerfile is a file used to build a docker image...

Detailed explanation of docker command to backup linux system

tar backup system sudo tar cvpzf backup.tgz --exc...

20 excellent foreign web page color matching cases sharing

This article collects 20 excellent web page color ...

Detailed explanation of how Tomcat implements asynchronous Servlet

Preface Through my previous Tomcat series of arti...

Implementation of building Kubernetes cluster with VirtualBox+Ubuntu16

Table of contents About Kubernetes Basic environm...

Detailed explanation of the functions and usage of MySQL common storage engines

This article uses examples to illustrate the func...

Write a React-like framework from scratch

Recently I saw the article Build your own React o...

Linux system file sharing samba configuration tutorial

Table of contents Uninstall and install samba Cre...

Detailed explanation of the four transaction isolation levels in MySQL

The test environment of this experiment: Windows ...

JavaScript function call, apply and bind method case study

Summarize 1. Similarities Both can change the int...