Creating an Event in MySQL

Event in MySQL – Part 1

Foreword: In this part of the series I explain to you what an event is and how to create it in MySQL.

By: Chrysanthus Date Published: 13 Apr 2016

Introduction

This is part 1 of my series, Event in MySQL. In this part of the series I explain to you what an event is and how to create it in MySQL. An event in software including MySQL, is the execution of special code triggered by a particular instant or instants at regular time gaps (intervals). So, when a particular time occurs, the special code is executed. In MySQL, the code is mainly to insert a row or delete a row or update a row in a table. The code may also do some other thing like to display the result of a query. In this article, I explain the MySQL Create Event syntax, in a top-down approach. Note: an event is a piece of code whose

Prerequisite
This series is part of my volume called, The Must Know in Database Design and MySQL Server. The link to the volume is given below. There you will see what you should have studied before beginning this series.

The Create Event Syntax Simplified
A simplified syntax to create an event is:

CREATE EVENT event_name
    ON SCHEDULE schedule
    [COMMENT 'comment']
    DO event_body;

So to create an event, type CREATE EVENT, then the name you want to give to the event in place of event_name. Next you type ON SCHEDULE. After that you type when the event should start and if and how often the event will repeat and stop in place of schedule; I will give the details of this shortly. In MySQL anything in square brackets of a syntax is optional. However, if you want to type a comment for the Create Event statement, you type it at the position in the syntax. You begin by typing COMMENT, then actual comment in quotes. Next you type, DO. After this you type the code that is to be executed in place of event_body. The code is either a single SQL statement or a compound SQL statement (a statement consisting of other statements). Do not forget to end everything with a semicolon.

Event from MySQL’s Point of View
So far as MySQL is concerned, an event is code, which is either a single SQL statement or a compound statement that can execute once at a time you set or execute at a particular time you set and repeat continuously, each repeat after a time gap (interval) you set. You can also set the event to repeat and stop. All the timing setting is done in the schedule clause. The DO clause is where you have the code, which is either one statement or a compound statement.

The event_name
As for the event_name, give a name that you can easily remember as you have given other names in MySQL.

The ON SCHEDULE Clause
The syntax for the ON SCHEDULE is:

AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]

With this clause, you can either type the “AT timestamp” sub-clause or the “EVERY interval” sub-clause. The “AT timestamp” sub-clause is used when you want the event to occur once at any time from now. The “EVERY interval” sub-clause is used when you want the event to start anytime from now, and then repeat forever or repeat and stops. You may wonder what kind of event should repeat forever. Many Internet online events repeat forever (as a rule).

In one of my previous tutorials titled, “MySQL Interval for Date and Time”, I explained what an Interval is, and how to code it in MySQL. So, if you would have problems with that part of the coding, just type the title of the tutorial without the quotes, and my name, Chrys in the Search Box of this page and click Search.

An example for the “AT timestamp” code is:

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 HOUR

Note: you can type the code in lowercase. After typing ON SCHEDULE you type AT. Next, you do not type “timestamp”, you type but the constant, CURRENT_TIMESTAMP, to mean, now. The + is obligatory to type. Then you type the reserved word, INTERVAL and then the figure for how long to wait and then the unit of the time, e.g. HOUR (without s). So, with this code, the event will execute (occur) after 2 hours from now, once.

An example for the “EVERY interval” sub-clause that causes the event to repeat forever is:

ON SCHEDULE EVERY 24 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 5 HOUR

In this case, you begin with the word, EVERY. Then you type the figure and the time unit for the repetition time gap. Next you type the reserved word, STARTS. After that, the constant, CURRENT_TIMESTAMP. The addition sign is obligatory. Then the reserved word indicator, INTERVAL, and then the figure and time unit for the interval from now until the event executes for the first time. So, this piece of code will cause the event to start executing after 5 hours from now and repeat every day (24 hours) forever. In other words, the event executes every 24 hours starting 5 hours from now.

In MySQL, Interval is not the repetition time gap for the event; it is the time from now when the event is created, to the first time it is executed.

If you want the event to execute now, use the following piece of code:

ON SCHEDULE AT CURRENT_TIMESTAMP

This code is the same as the previous one above, but it ends at CURRENT_TIMESTAMP, which means, now. This code is from the “AT timestamp” sub-clause. If you want the event to repeat starting from now, you would type something like:

ON SCHEDULE EVERY 24 HOUR STARTS CURRENT_TIMESTAMP

You can replace 24 HOUR with the figure and unit of your choice. In this case the START portion of the code is still omitted. This code is from the “EVERY interval” sub-clause.

Interval
The time unit typed after the figure in combination with the figure gives the repetition gap or interval. Possible values for the time units are: YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND. The meanings of these units are found in the tutorial I wrote titled, MySQL Interval for Date and Time. To reach the tutorial, just type the title and my name Chrys in the Search Box of this page and click, Search.

The DO event_body
When they talk about event, they are referring to a piece of code that will execute at some time, execution caused by the time. For MySQL, this code is the content of the DO clause. The rest of the code of the Create Event statement plus the content of the DO clause, is controlled by what is known as the Event Scheduler (see later). The content of the DO clause is either one SQL statement or a compound statement. An example of one SQL statement is:

        UPDATE myTable SET myColumn = myColumn + 1 WHERE ID = 3;

Note: in the above statements, if the constant, CURRENT_TIMESTAMP is omitted, it would still have its effects.

Event Examples
An example with one SQL statement for the DO clause is:

CREATE EVENT myEvent
    ON SCHEDULE
        EVERY 24 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 5 HOUR
    DO
        UPDATE myTable SET myColumn = myColumn + 1 WHERE ID = 3;

An example with a compound statement is:

delimiter //

CREATE EVENT eTpageViews
    ON SCHEDULE
     EVERY 1 DAY
    COMMENT 'Saves total number of page views then clears the table each day'
    DO
     BEGIN
        INSERT INTO totals (time, total)
         SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM sessions;
        DELETE FROM sessions;
     END;//

delimiter ;

For the case of the compound statement, the whole event is in a pair of delimiters. The start delimiter is “delimiter |” and the end delimiter is “| delimiter”. Every SQL construct or statement must end with a semicolon. Here in the actual compound statement of BEGIN and END, the independent statements have semicolons. There is also a semicolon just after the end delimiter.

Well, you have learned how to create an Event. If you go ahead and create an event correctly, it may not work. This is because an event created is controlled by what is called, the Event Scheduler. The Event Scheduler has to be ON before any event created will work as expected.

Note: MySQL events work very well with MySQL 5.5 and above. It might work with some lower versions, but I cannot guarantee that.

That is it for this part of the series. We stop here and continue in the next part.

Chrys

Broad Network

Related Articles

Creating an Event in MySQL

Event in MySQL – Part 1

Introduction

Related Links

Comments