Creating Simple Event with MySQL

  • Avinash
  • 5
  • Jan 12, 2012
  • Database

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 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 excutes 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; $$;
  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 created or not. So to make sure you just need to execute one single query to get all events which your mysql server has.

SHOW EVENTS;
  SHOW EVENTS;

You can write multiple SQL queries between BEGIN and END block of event. Yes you can 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 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 ON COMPLETION 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.

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; $$;
  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 Exiting Events

You can edit events normally with the ALTER EVENT clause. Have a look at the below query block which show 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; $$;
  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 you will get all things yourself, i think no need of explanation over here.

ALTER EVENT first_event RENAME TO first_event_edited;
  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;
  DROP EVENT first_event;

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.

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: