MySQL scheduled task event and storage process (regularly delete the _ MySQL

MySQL scheduled task event and storage process (regularly delete data in the specified table 90 days before the specified time)

MySQL scheduled task event

Due to some business needs, we may need to regularly clear some discarded data in the database, which can be completed using mysql stored procedures and events. The following example regularly clears the data of the specified number of days in the tbl_base_count log table. 1. create the tbl_base_count log table:

CREATE TABLE `tbl_base_count` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `CAPTCHA` varchar(12) COLLATE utf8_bin NOT NULL,  `PHONE` varchar(12) COLLATE utf8_bin NOT NULL,  `SENDTIME` varchar(32) COLLATE utf8_bin NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2. create event e_del_tbl_base_count:

CREATE EVENT `e_del_tbl_base_count` ON SCHEDULE EVERY 1 DAY STARTS '2013-06-23 17:33:43' ON COMPLETION NOT PRESERVE ENABLE DO CALL p_del_count (90);

The code above indicates that the stored procedure p_del_count is executed every day from 17:33:43, with parameters

3. create a stored procedure:

P_del_count DELIMITER $ ---- stored PROCEDURE -- create procedure 'p _ del_count '(IN 'date _ inter' INT) begin delete from log where (TO_DAYS (NOW ()) -TO_DAYS (FROM_UNIXTIME (SENDTIME)> = date_inter; END $ DELIMITER;

According to the parameter 90 passed by the event, the deletion operation time is 90 days before the data so that mysql will customize to execute this task every day.
4. (1) There are three methods to check whether the event plan (scheduler) is currently enabled:

SHOW VARIABLES LIKE 'event_scheduler'; SELECT @@event_scheduler; SHOW PROCESSLIST;

5. (2) There are four ways to enable the event scheduler:

 SET GLOBAL event_scheduler = 1; SET @@global.event_scheduler = 1; SET GLOBAL event_scheduler = ON; SET @@global.event_scheduler = ON;

Key Value 1 or ON indicates enabled; 0 or OFF indicates disabled;
6. (3) enable and disable an event:

ENABLE an EVENT: alter event e_del_logs on completion preserve enable; DISABLE an EVENT: alter event e_del_logs on completion preserve disable;