mysql Simple Method of Deleting Expired Data Record Timely

  • 2021-12-11 19:20:27
  • OfStack

1. After connecting and logging in to MySQL, first check whether MySQL has turned on the event function:

Command: show variables like '% sc%';

It is found that event_sheduler is turned off by OFF.

2. Open event_scheuler:

Temporary start (mysql service fails after restarting)


SET GLOBAL event_scheduler = ON; SET GLOBAL event_scheduler = 1;  -  0 Representative closed 


Permanently open

Add the following in the [mysqld] section of my. cnf, and then restart mysql (mysql Restart Command: service mysqld restart)


event_scheduler=ON


3. Create an event, for example, to delete the data in the wififlows table that is 2 minutes out of date every 5 seconds:


create event e_delete_wififlows on schedule every 5 second do delete from wififlows where timestamp < (CURRENT_TIMESTAMP() + INTERVAL -2 MINUTE);


If this event exists in advance, it can be deleted by using the following command:


drop event if exists e_delete_wififlows;


Then use show events; View Existing Events

4. Open events:


alter event e_del_wififlows on completion preserve enable;


5. Shutdown events:


alter event e_del_wififlowa on completion preserve disable;



Related articles: