Mysql regularly deletes data using stored procedures and Event Events

The entire process is divided into three steps:

1. Write the Stored Procedure

2. Write Event Events

3. Set the Event to Enable and open the Event task.

1. Stored Procedure

First define a stored procedure, del_data. Input an int parameter is the number of days of log Content Retained, the content of the process is to delete the data from the t_xxx table for one week and the data from the days that the t_log table is retained.

create procedure del_data(IN `date_inter` int)begindelete from t_xxx where date < date_sub(curdate(),interval 7 day);delete from t_log where date < date_sub(curdate(),interval date_inter day);end 

2. Write Event Events

Define del_event, define the start Event and frequency of Event execution in Event Events, set the Event to Enable, and execute the del_data stored procedure at the point (the number of days passed in is 30 days ).

create event del_event  on schedule EVERY 1 day  STARTS '2014-01-01 03:00:00'  ON COMPLETION  PRESERVE ENABLE  do  call del_data(30)

You can also enable or disable events:

Close event task: alter event del_event on completion preserve disable;
Account Opening event task: alter event del_event on completion preserve enable;

You can also use show events to check the newly created event.

3. Open an event task

Check whether the event is Enabled: show variables like '% sche % ';
Enable the event plan: set global event_scheduler = ON;

Of course, you can also write stored procedures in Event events, which is not standardized:

delimiter |CREATE EVENT e_daily    ON SCHEDULE      EVERY 1 DAY    COMMENT 'Saves total number of sessions then clears the table each day'    DO      BEGIN        INSERT INTO site_activity.totals (time, total)          SELECT CURRENT_TIMESTAMP, COUNT(*)            FROM site_activity.sessions;        DELETE FROM site_activity.sessions;      END |delimiter ;