MySQL - SHOW CREATE EVENT Statement

---

---

MySQL SHOW 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 SHOW CREATE EVENT statement displays the query used to create the specified event.

Syntax

Following is the syntax of the MySQL SHOW CREATE EVENT statement −

SHOW CREATE EVENT event_name

Where, event_name is the name of the event for which you need the CREATE statement.

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 which 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);

Following query displays the CREATE EVENT used above −

SHOW CREATE EVENT example_event1\G;

Output

Following is the output of the above query −

********** 1. row **********
               Event: example_event1
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
			          NO_ZERO_IN_DATE, NO_ZERO_DATE,
					  ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT 
		              `example_event1` ON SCHEDULE AT '2023-12-12 16:04:57'
					  ON COMPLETION NOT PRESERVE ENABLE DO INSERT 
					  INTO new.Data VALUES('Rahman', 25)
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci

Example

Assume we have created another event as shown below −

CREATE EVENT example_event2 ON SCHEDULE AT ADDTIME(now(), "00:1:00") 
DO INSERT INTO new.Data VALUES('Raju', 30);

Following is the show create statement −

SHOW CREATE EVENT example_event2\G;

Output

The above query produces the following output −

********** 1. row **********
               Event: example_event2
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
			          NO_ZERO_IN_DATE,NO_ZERO_DATE,
					  ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT 
		             `example_event2` ON SCHEDULE AT 
					 '2023-12-12 16:05:48' ON COMPLETION 
					  NOT PRESERVE ENABLE DO INSERT INTO 
					  new.Data VALUES('Raju', 30)
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci