编程知识 cdmana.com

MySQL can clear the old data of a table regularly and keep several pieces of data

To achieve the following goals :

Mysql The database goes on every once in a while ( It can be 2 Hours , It can be a day , This can be customized ), Make a judgment on a table in a library regularly , If the data in this table exceeds 20 strip ( This data is also custom , It can also be 200 strip ), Just keep the latest 10 Data ( This data can also be customized , But it should be less than or equal to the previous number of more than the number of data ).

Let's talk about the solution ( Deduce from back to front ):

1、 Turn on a timer , This timer does two things :

⑴ Set the time interval

⑵ Call a stored procedure

2、 Write a stored procedure , This stored procedure does two things :

⑴ Determine whether the number of data in the table exceeds 20, If exceeded 20 Just do the following steps .

⑵ Keep it up to date 10 Data , Delete other old data . This requires the table to have an incremental primary key id, So the latest data id The greater the value of . Just find the largest one in the current table id Then subtract 10 Get one ' Delete node ', Then write in the delete statement  where id < ' Delete node '   that will do . In this way, although the results may not be accurate , But the effect can be roughly achieved .

Suppose there's a datas surface , There is a primary key in this table id Is increasing . The data in this table will continue to increase , Now every 5 Seconds to keep datas The latest version of the table 10 Data , Delete everything else .

The code process is as follows :

1. First define a stored procedure named pro_clear_data, Pay attention to the vertical line (“|”) You must not lose

 1 DELIMITER |
 2 DROP PROCEDURE IF EXISTS pro_clear_data |
 3 CREATE PROCEDURE pro_clear_data()
 4     BEGIN  
 5       
 6        SET @datas_count=(SELECTCOUNT(id) FROM datas);
7 IF(@datas_count>20) THEN
8 9 SET @max_id=(SELECT MAX(id) FROM datas);
10 SET @max_id = @max_id - 10; 11 DELETE FROM `datas` WHERE id<@max_id;
12 13 END IF ; 14 15 END 16 |

2. Create a timer called event_time_clear_data

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

3. This is the simplest, but also the most important , We need to start the timer manually , Or you can't work .

1 ALTER EVENT event_time_clear_data ON 
2  
3 COMPLETION PRESERVE ENABLE;

Creating stored procedure and timer code should be executed separately

every other 5 The data will be automatically cleared once every second , Keep the latest 10 strip .

 

in addition , The code to turn off the timer is :

1 ALTER EVENT event_time_clear_data ON 
2  
3 COMPLETION PRESERVE DISABLE;

The code to delete a stored procedure is :

1 DROP PROCEDURE pro_clear_data;

About Event:

mysql5.1 The version began to be introduced event Concept .event Both “ Time trigger ”, And triggers The event triggers are different ,event Similar to linux crontab Planning tasks , For time triggered . Use by itself or by calling a stored procedure , At a certain point in time , Trigger related SQL Statement or stored procedure .

Delete Event:

1 DROP EVENT IF EXISTS event_time_clear_data1

 

版权声明
本文为[Huahua_ New World]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224140210540y.html

Scroll to Top