Creating Simple Event with MySQL

Creating a First Event

Creating an Event is somewhat like creating stored procedure or user defined function in MySQL. You will have DELIMITER, BEGIN, DO and END 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 executes after 5 minutes of creating event. Have a look at the below block of queries for the same.

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;

$$;

Now once you have created the event you must make sure that event is created or not. So to make sure you will have to execute a single query to get all events which your MySQL server has.

SHOW EVENTS;

You can also write multiple SQL queries between BEGIN and END block of event. And yes you will have to separate multiple queries separated with ‘;’

So this is the basic step to create a simple event in MySQL. This is the basic and one-time event, which 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 that this event will get removed once it is executed unless you have mentioned anything on ON COMPLETION block. If you write ON COMPLETION PRESERVE then event will not get deleted after execution is completed. Let’s create one event which will not get deleted after completion.

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;

$$;
Edit the Existing Events

You can edit events normally with the ALTER EVENT clause. Have a look at the below query block which shows the editing of the existing event in 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;

$$;
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 and I think you will not require any explanation,

ALTER EVENT first_event

RENAME TO first_event_edited;
Delete Events in MySQL

Deleting events are simple as renaming the event.

DROP EVENT first_event;