MySQL Writing timed task events

Source: Internet
Author: User


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.


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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.