MySQL timed task event, storage process (periodically delete data from the specified table 90 days before the specified time)

 MySQL timed task event, storage process (periodically delete data from the specified table 90 days before the specified time)Category: mysql5.x2014-06-23 15:16 1266 People read comments (0) favorite reports MySQL Database [SQL]View Plaincopy

1. <span style="font-family: ' Microsoft Yahei '; font-size:14px; " >mysql timed Task event</span>

Due to some business requirements, we may need to periodically purge the database of some obsolete data, which can be done using MySQL's stored procedures and events. The following example periodically clears the data in log table Tbl_base_count before the specified number of days 1. Create a log table Tbl_base_count: [SQL]View Plaincopy

2. CREATE TABLE ' Tbl_base_count ' (
4. ' ID ' int (one) not NULL auto_increment,
6. ' CAPTCHA ' varchar (COLLATE) utf8_bin not NULL,
8. ' PHONE ' varchar (COLLATE) utf8_bin not NULL,
10. ' Sendtime ' varchar (+) COLLATE utf8_bin not NULL,
12. PRIMARY KEY (' id ')
14. ) Engine=innodb auto_increment=15 DEFAULT Charset=utf8 collate=utf8_bin;

2. Create Event E_del_tbl_base_count: [SQL]View Plaincopy

1. CREATE EVENT ' E_del_tbl_base_count '
2. On SCHEDULE every 1 day starts ' 2013-06-23 17:33:43 '
3. On completion not PRESERVE ENABLE does call P_del_count (90);

The above code indicates that the p_del_count stored procedure is executed once every day from 17:33:43 2013-07-30 and takes the parameter 3. Create a stored procedure: [SQL]View Plaincopy

2. P_del_count
4. DELIMITER $$
8. --
10. --Stored procedures
12. --
14. CREATE PROCEDURE ' P_del_count ' (in' Date_inter ' INT)
16. BEGIN
18. DELETE from LOG WHERE (To_days (now ())-To_days (From_unixtime (<span style="Font-family:consolas, ' Courier New ', Courier, mono, serif; line-height:18px; "  >SENDTIME</span>)) >=date_inter;
20. end$$
22. DELIMITER;

By the event of the parameter 90, delete the operation time 90 days before the data so that MySQL will be customized to perform this task every day. 4. (a) to see if the event plan is currently turned on (scheduler) there are 3 ways: [SQL]View Plaincopy

1. SHOW VARIABLES like ' Event_scheduler ';
2. SELECT @ @event_scheduler;
3. SHOW processlist;

5. (ii) There are 4 ways to turn on the event Plan (scheduler) switch: [SQL]View Plaincopy

1. SET GLOBAL event_scheduler = 1;
2. SET @@ Global.event_scheduler= 1;
3. SET GLOBAL event_scheduler = on ;
4. SET @@ Global.event_scheduler= on ;

The key value 1 or on means open; 0 or off means close;
6. (iii) Event opening and closing: [SQL]View Plaincopy

1. Open an event:ALTER event e_del_logs on completion PRESERVE ENABLE;
2. Close an event:ALTER event e_del_logs on completion PRESERVE DISABLE;

MySQL timed task event, storage process (periodically delete data from the specified table 90 days before the specified time)