Repeating and Scheduling MySQL Events

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 [code]EVERY[/code]. So now let’s write one simple event which executes on every 3 hours.

[cc lang=”mysql”]
DELIMITER $$
CREATE EVENT first_event
ON SCHEDULE EVERY 3 HOUR
DO
BEGIN
UPDATE table_name SET field_name = field_name + 1;
END;
$$;
[/cc]

Here you can see that what we have changed is just line of [code]ON SCHEDULE[/code], we just added [code]EVERY[/code] keyword before the interval. But can you guess the execution count of this event?? It is [code]INFINITE[/code], as we have not declared the [code]END[/code] 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 [code]STARTS[/code] and [code]END[/code] clause which defines the time period in which this event is valid to execute. Have a look at the below block of query.

[cc lang=”mysql”]
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;
$$;
[/cc]

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.