Welcome back to our series on Database Events! Part 1 outlined the difference between Database Events and Scheduled Tasks, as well as how to configure the Event Scheduler Thread in MySQL. In today's blog, we'll explore how to create MySQL events using CREATE EVENT syntax.

Creating a New MySQL Event

Creating an event is similar to creating other database objects such as stored procedures or functions. Like those objects, an event is a named database object that contains SQL statements. Here's the basic syntax:

CREATE EVENT [IF NOT EXIST] event_name
ON SCHEDULE schedule
DO
event_body

A few things to note:

• The event name must be unique within a database schema.
• If you have multiple SQL statements within the event body, you can wrap them in a BEGIN END block.

Let's create an actual event to put the above syntax to use. We'll define and schedule a one-time event that inserts a message into a table called messages.

First, either find a suitable test database or create a new one. Then create a new table named "messages" by using the CREATE TABLE statement like so:

	CREATE TABLE IF NOT EXISTS messages (
		id INT PRIMARY KEY AUTO_INCREMENT,
		message VARCHAR(255) NOT NULL,
		created_at DATETIME NOT NULL
	);
	



• Now it's time to create our event, using the CREATE EVENT statement:
  

  		CREATE EVENT IF NOT EXISTS test_event
  		ON SCHEDULE AT CURRENT_TIMESTAMP
  		DO
  		  INSERT INTO messages(message,created_at)
  		  VALUES('Test MySQL Event 1',NOW());
  		 



• That should add our message to the messages table immediately. Let's check the messages table by issuing a SELECT ALL on the messages table:
  
  

Preserving Events on Completion

Events are automatically dropped when they expire. In the case of a one-time event like the one we created, it expired when it finished executing.

We can view all events of a database schema by issuing the following statement at the MySQL command prompt:

mysql> SHOW EVENTS FROM test;
Empty set

To have events persist after they expire, we can use the ON COMPLETION PRESERVE clause. Here's a statement that creates another one-time event that is executed 30 seconds after its creation and not dropped after execution:

CREATE EVENT test_event_2
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 SECOND
ON COMPLETION PRESERVE
DO
   INSERT INTO messages(message,created_at)
   VALUES('Test MySQL Event 2',NOW());

Wait for at least 30 seconds and check the messages table. Another record should be added:

Let's execute the SHOW EVENTS statement again. The event is there (albeit in a DISABLED state) because the effect of the ON COMPLETION PRESERVE clause:

mysql> SHOW EVENTS FROM test;
+------+--------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| Db   | Name         | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status   | Originator | character_set_client | collation_connection | Database Collation |
+------+--------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| test | test_event_2 | root@localhost | SYSTEM    | ONE TIME | 2018-06-07 15:08:00 | NULL           | NULL           | NULL   | NULL | DISABLED |          1 | utf8mb4              | utf8mb4_general_ci   | utf8_general_ci    |
+------+--------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
1 row in set (0.02 sec)