Repeating and Scheduling MySQL Events

  • Avinash
  • 1
  • Jan 15, 2012
  • Database

In earlier two posts we have learned to check for the Events status in MySQL server and then after we have learned to create a simple event, edit existing events, drop and rename evants.

Also in the second article we have checked that normal events will gets deleted once executed, these events also called as one time events. So in this case I have also explain the trick to preserve the Event in MySQL Server. But do you think is it good to keep the MySQL Event in MySQL Server even if it will not used more? So the question is when to preserve events?

Preserving of events will be useful when you have event which is supposed to execute several times on specific interval. This is the article which going to cover this topic for this series of articles.

In this article we will see for creating a scheduled events which executes several times and also we can define the start and end time for the event. So we can have such events which runs on specific interval for specific period of time ( i.e. one week or one month ).

Create a Scheduled Events in MySQL

To create a event which executes on specific interval we have to use keyword EVERY. So now let’s write one simple event which executes on every 3 hours.

DELIMITER $$ CREATE EVENT first_event ON SCHEDULE EVERY 3 HOUR DO BEGIN UPDATE table_name SET field_name = field_name + 1; END; $$;
  DELIMITER $$
  CREATE EVENT first_event
    ON SCHEDULE EVERY 3 HOUR
    DO
      BEGIN
        UPDATE table_name SET field_name = field_name + 1;
      END;
  $$;

Here you can see that what we have changed is just line of ON SCHEDULE, we just added EVERY keyword before the interval. But can you guess the execution count of this event?? It is INFINITE, as we have not declared the END clause.

So now let’s create an event which starts after specific time and stops execution after defined time.

Start and End Events in MySQL

Here we will use STARTS and END clause which defines the time period in which this event is valid to execute. Have a look at the below block of query.

DELIMITER $$ CREATE EVENT first_event ON SCHEDULE EVERY 3 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH DO BEGIN UPDATE table_name SET field_name = field_name + 1; END; $$;
  DELIMITER $$
  CREATE EVENT first_event
    ON SCHEDULE EVERY 3 HOUR
    STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
    DO
      BEGIN
        UPDATE table_name SET field_name = field_name + 1;
      END;
  $$;

Here you can see the our event is scheduled on every 3 hours, which starts after 1 hour of creating an event and will continue to execute upto one month.

Conclusion

So now we have completed with the all functionality of the MySQL events. Hope you are now able to check the status of the events in MySQL server, creating basic events, editing the existing events, deleting and renaming the events. Also you would be able to create repeating events, defines the start and end time for the events in MySQL.

I hope you have enjoyed this series of article. Share your thoughts via comments here.

Also We will have good upcoming series for this blog. Subscribe to our RSS feed if you don’t want to miss any article.

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by: