MySQL - CREATE EVENT Statement



MySQL CREATE EVENT Statement

A MySQL Event is nothing but a task that execute at a particular schedule. An event can contain one or more MySQL statements these statements are stored in the databases and gets executed at the specified schedule.

The CREATE EVENT statement is used to create and schedule an MYSQL event.

Syntax

Following is the syntax of the MySQL CREATE EVENT statement −

CREATE EVENT [IF NOT EXISTS] event_name
   ON SCHEDULE time_stamp
   DO event_body;

Where, event_name is the name of the event you need to create, time_stamp is the time at which the statements should be executed and event_body is the set of statement to be executed.

Example

Assume we have created a table with name data using the CREATE TABLE statement as shown below −

CREATE TABLE Data (Name VARCHAR(255), age INT);

Following query creates an event with name test_event, it inserts a record in the above created table one minute after the execution −

CREATE EVENT example_event1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 
1 Minute DO INSERT INTO new.Data VALUES('Rahman', 25);

You can also pass the exact time to execute the event. First of all let us retrieve the current time using the Now() function as shown below −

Select Now();

Output

Following is the output of the above query −

Now()
2021-02-10 12:09:30

Following query creates another event to which we pass the current time stamp as schedule −

CREATE EVENT example_event2 ON SCHEDULE AT '2021-02-10 12:09:30' 
DO INSERT INTO new.Data VALUES('Raju', 30);

Verification

After one minute if you verify the contents of the table data using the SELECT statement you can observe the inserted records as shown below −

SELECT * from data;

The above query produces the following output −

Name age
Rahman 25
Raju 30

The IF NOT EXISTS clause

If you use the IF NOT EXISTS clause along with the CREATE EVENT statement as shown below a new event will be created and if an event with the given name, already exists the query will be ignored.

CREATE EVENT IF NOT EXISTS example_event2 ON SCHEDULE AT 
CURRENT_TIMESTAMP DO DROP TABLE Data;

The EVERY clause

Using EVERY clause, you can create an event with an interval as a schedule following is the syntax to do so −

CREATE EVENT event_name ON SCHEDULE EVERY interval quantity DO event_body;

Following query creates an event which truncates the table data every month −

CREATE EVENT event_hourly ON SCHEDULE EVERY 1 MONTH DO TRUNCATE TABLE data;

The COMMENT clause

You can add a comment while creating an event using this clause Following is the syntax to do so −

CREATE EVENT event_name ON SCHEDULE schedule COMMENT 
'comment_string' DO event_body;

Where comment_string is the comment you need to add. Following query adds a comment while creating an event −

CREATE EVENT
event_hourly1
ON SCHEDULE
EVERY 1 MONTH
COMMENT 'This truncates the data table each month'
DO
TRUNCATE TABLE data;
mysql_statements_reference.htm
Advertisements