Creating Simple Event with MySQL

This is the second article of this series and I am going to cover creation of basic mysql events and check the status of the same. If you are not sure what was covered in this series yet then you can have a look at the first article about MySQL Events.

Creating a First Event

Creating an Event is somewhat like creating stored procedure or user defined function in MySQL. You will have [code]DELIMITER[/code], [code]BEGIN[/code], [code]DO[/code] and [code]END[/code] keywords.

As mentioned earlier we can define the execution time for the event while creating it. So now let’s create a basic event which excutes after 5 minutes of creating event. Have a look at the below block of queries for the same.

[cc lang=”mysql”]
DELIMITER $$
CREATE EVENT first_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
DO
BEGIN
UPDATE table_name SET field_name = field_name + 1;
END;
$$;
[/cc]

Now once you have created the event you must make sure that event created or not. So to make sure you just need to execute one single query to get all events which your mysql server has.

[cc lang=”mysql”]
SHOW EVENTS;
[/cc]

You can write multiple SQL queries between [code]BEGIN[/code] and [code]END[/code] block of event. Yes you can separate multiple queries separated with [code];[/code].

So this is the basic step to create a simple event in MySQL. This is the basic and one time events, means this will be scheduled only once and after the execution is completed this event will be deleted from the server.

As I have already mentioned this events will get removed once its executed unless you have mentioned anything on [code]ON COMPLETION[/code] block. If you write ON COMPLETION PRESERVE then event will not delete after execution completed. Let’s create one event which will not delete after completion.

[cc lang=”mysql”]
DELIMITER $$
CREATE EVENT first_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
ON COMPLETION PRESERVE
DO
BEGIN
UPDATE table_name SET field_name = field_name + 1;
END;
$$;
[/cc]

Edit the Exiting Events

You can edit events normally with the [code]ALTER EVENT[/code] clause. Have a look at the below query block which show the editing of the existing event in MySQL.

[cc lang=”mysql”]

DELIMITER $$
ALTER EVENT first_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 MINUTE
ON COMPLETION PRESERVE
DO
BEGIN
UPDATE table_name SET field_name = field_name + 1;
END;
$$;
[/cc]

After executing above query your event will be set to execute after 2 minute from current time.

Rename Events in MySQL

Renaming event is very easy here. Have a look at below query you will get all things yourself, i think no need of explanation over here.

[cc lang=”mysql”]
ALTER EVENT first_event
RENAME TO first_event_edited;
[/cc]

Delete Events in MySQL

Deleting events are simple as renaming the event.

[cc lang=”mysql”]
DROP EVENT first_event;
[/cc]

What’s Next?

So after this article I hope you will be effeciently able to create a one time event, edit event, rename events, drop events and also preserve the events. Now on next article I will going to cover about creating the repeating events in MySQL. So these types of events keeps running untill dropped.

To get notified about the next article in this series, subscribe to our RSS feed via email, so you will not miss out any article from us.