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)


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: