MySQL scheduled task event

MySQL scheduled task event due to some business needs, we may need to regularly clear some discarded data in the database, you can use mysql stored procedures and events to complete. The following example regularly clears the data of a specified number of days in a log table. 1. Creates a log table.

Create table if not exists 'log' ('Log _ id' int (11) not null AUTO_INCREMENT COMMENT 'record id', 'user _ id' int (11) default null comment 'user id', 'op' varchar (128) not null comment' operation type ', 'model' varchar (32) default null comment 'Operation module ', 'activity _ time' int (10) not null default '0' COMMENT 'Operation time', 'data' text COMMENT 'data', primary key ('Log _ id ')) ENGINE = MyISAM default charset = utf8 COMMENT = 'user log table' AUTO_INCREMENT = 1;

 

2. Create event e_del_logs

CREATE EVENT `e_del_logs` ON SCHEDULE EVERY 1 DAY STARTS '2013-07-30 17:33:43' ON COMPLETION NOT PRESERVE ENABLE DO call p_del_logs(90);

 

// The code above indicates that the Stored Procedure p_del_logs is executed every day from 17:33:43, and parameters are included. 3. The stored procedure is created.

P_del_logsDELIMITER $ ---- stored PROCEDURE -- create procedure 'P _ del_logs '(IN 'date _ inter' int) BEGIN delete from log where (to_days (now ()) -to_days (FROM_UNIXTIME (activity_time)> = date_inter; END $ DELIMITER;

 

// According to the parameter 90 passed by the event, the data deleted 90 days ago will be customized for mysql to execute this task every day. There are three ways to check whether an event plan (Scheduler) Is Enabled:

1)     SHOW VARIABLES LIKE 'event_scheduler';2)     SELECT @@event_scheduler;3)     SHOW PROCESSLIST;

 

(2) There are four ways to enable the event Scheduler:

1)     SET GLOBAL event_scheduler = 1;2)     SET @@global.event_scheduler = 1;3)     SET GLOBAL event_scheduler = ON;4)     SET @@global.event_scheduler = ON;

 

Key value 1 or ON indicates enabled; 0 or OFF indicates disabled; (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;