In order to delete data in a fixed time automatically, use mysql event;
- Login Mysql
- Check event_scheduler
SHOW VARIABLES LIKE ‘event_scheduler’; - If event_scheduler is OFF
临时:
SET GLOBAL event_scheduler = 1
永久:
vim /etc/my.cnf
event_scheduler=ON - Create event
create event xxx
ON SCHEDULE
every 1 day starts ‘2019-03-18 12:00:00’
DO
delete from nightly_status
where STR_TO_DATE(forecast_day, ‘%Y-%m-%d’) < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY);
- Check event
show events; - Delete event
DROP EVENT [IF EXISTS] my_event_name; - Open & Close event
alter event my_test_event on completion preserve enable;
alter event my_test_event on completion preserve disable;