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;