Just like a cron on a Linux server, MySQL has EVENTS to handle the scheduled tasks. MySQL uses a special thread called the event schedule thread to execute all scheduled events. By default, the event scheduler thread is not enabled (version < 8.0.3), to enable it, execute:
mysql> SETGLOBALevent_scheduler=ON;
Suppose you no longer need to keep salary audit records that are more than a month old, you can schedule an event that runs daily and deletes the records from the salary_audit table that are a month old.
mysql> DROP EVENT IF EXISTS purge_salary_audit;
DELIMITER $$
CREATE EVENT IF NOT EXISTS purge_salary_audit
ON SCHEDULE
EVERY 1 WEEK
STARTS CURRENT_DATE
DO BEGIN
DELETE FROM salary_audit WHERE date_modified < DATE_ADD(CURDATE(), INTERVAL -7 day);
END $$
DELIMITER ;Once the event is created, it will automatically do the job of purging the salary audit records.
- To check the events, execute:
mysql> SHOW EVENTS\G *********************...