Mysql implements regular clearing of old data in a table and retaining several pieces of data (recommended)

To achieve the following goals:

Mysql database will make a regular judgment on a table in a library at regular intervals (it can be 2 hours or a day, which can be customized). If the data in this table exceeds 20 records (this data is also customized, it can also be 200 records), the latest 10 records will be retained (this data can also be customized, but it must be less than or equal to the previous number of data records).

Let me briefly talk about the solution (derived from back to front):

1. Start a timer, which does two things:

⑴Set the time interval

⑵Call a stored procedure

2. Write a stored procedure that does two things:

⑴ Determine whether the number of data entries in the table exceeds 20. If it exceeds 20, perform the following steps.

⑵ Keep the latest 10 data and delete the rest of the old data. This requires that the table must have an increasing primary key id, so that the value of the latest data id is larger. Just find the largest id in the current table and subtract 10 to get a 'delete node', and then write where id <'delete node' in the delete statement. Although the result may not be accurate, it can roughly achieve the effect.

Suppose there is a datas table, which has a primary key id that is incremented. The data in this table will continue to increase. Now we need to keep the latest 10 data in the datas table every 5 seconds and delete the others.

The code process is as follows:

1. First, define a stored procedure named pro_clear_data. Note that the vertical bar (“|”) must not be missing.

DELIMITER |
 DROP PROCEDURE IF EXISTS pro_clear_data |
 CREATE PROCEDURE pro_clear_data()
  BEGIN 
  
   SET @datas_count=(SELECTCOUNT(id) FROM datas);
	   IF(@datas_count>20) THEN
	  
   SET @max_id=(SELECT MAX(id) FROM datas);  
	 SET @max_id = @max_id - 10;
    DELETE FROM `datas` WHERE id<@max_id;   
  END IF ;
  
  END
 |

2. Create a timer named event_time_clear_data

SET GLOBAL event_scheduler = 1; 
 CREATE EVENT IF NOT EXISTS event_time_clear_data
 
 ON SCHEDULE EVERY 5 SECOND
 
 ON COMPLETION PRESERVE 
 
DO CALL pro_clear_data();

3. 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;

Create stored procedures and create timer codes separately.

The data will be automatically cleared every 5 seconds, and the latest 10 records will be retained.

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

ALTER EVENT event_time_clear_data ON 
 
 COMPLETION PRESERVE DISABLE;

The code to delete the stored procedure is:

DROP PROCEDURE pro_clear_data;

About Event:

MySQL version 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.

Delete Event:

DROP EVENT IF EXISTS event_time_clear_data1

This is the end of this article about how to implement MySQL to periodically clear old data in a table and retain several pieces of data. For more relevant MySQL periodic data clearing content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!