MySQL Writing timed task events

Original: MySQL Writing timed task events

Scene:

For example: A system, the user can only have one time a day of the lottery, after the day can not be smoked, but after 24:00 points after the user to re-own a lottery opportunity. a task like this, which requires the database to update a field on a timed basis, can be manipulated by the event mechanism of the database itself, and the server side does not need to perform such monitoring and timing operations with additional code.

Mysql:

This article discusses the MySQL database, which should be introduced in Mysql5.1, so please check your MySQL version when using the Scheduled Task Scheduler (Scheduler).

To create the syntax for an event dispatch:

/* Create Event Event_Name */ DELIMITER $$ CREATE IF  not EXISTS Event_Name  on  on completion Preservedo SQL Operation delimiter;

instance Scenario : Update the Statistics field every half hour count is 0,

/* Create a timed Timed Update Count field event */ DELIMITER $$ CREATE IF  not EXISTS Update_count  on

/*mysql Note Time zone setting, default non-Chinese time zone */
Starts TIMESTAMP ' 2009-12-18 02:58:00 ' on completion preservedo update tb_count set count=0 $ $DELIMITER; /c14>

as above, an event called Update_count is created, the execution condition is executed every 30 minutes, (every MINUTE), execute SQL is (update tb_count set count=0), of course you will be executed by day, So you can also write (every 1 day),

If the task of executing SQL is complex, we can write the trigger and look directly at the sample code:

 delimiter $$  drop  procedure  if  exists   update_count$$  create  procedure   Update_count ()     begin  update  tb_count set  Count =  0   end  

The above is to create a stored procedure update_count that performs an update to the Count field in Tb_count. The Do field in the code in the task is changed to (call Update_count () $$). Of course you may have more complex needs.

After adding the event, you also need to see if the Event_scheduler is turned on, otherwise the added event will not be executed and the following code:

/* See if event is turned on */  like'%sche%'; /* Open Event */ SET GLOBAL Event_scheduler=1;

To view and close the event that you added:

/**/ALTER on completion PRESERVE DISABLE; /*  */ALTER on completion PRESERVE ENABLE;

More MySQL syntax to view: http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html