MySQL - SHOW EVENTS Statement

Table of content

MySQL SHOW EVENTS 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 EVENTS statement lists (information about) the events in the specified schema.

Syntax

Following is the syntax of the MySQL SHOW EVENTS statement −

SHOW EVENTS
   [{FROM | IN} schema_name]
   [LIKE 'pattern' | WHERE expr]

Example

Assume we have created a table with name data and create an event which inserts records into it.

CREATE TABLE Data (Name VARCHAR(255), age INT);
CREATE EVENT sample_event ON SCHEDULE EVERY 1 MONTH DO TRUNCATE TABLE data;

Following query lists the events in the current database −

SHOW EVENTS\G;

Output

Once the query is executed, it will produce the output shown below −

************* 1. row *************
                  Db: test
                Name: event_hourly
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MONTH
              Starts: 2023-12-05 14:51:03
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
************* 2. row *************
                  Db: test
                Name: new_event_name
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MONTH
              Starts: 2023-12-04 14:08:03
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
************* 3. row *************
                  Db: test
                Name: sample_event
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MONTH
              Starts: 2023-12-13 12:01:05
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci

FROM or IN clause

You can retrieve the description of events from a specific database using the FROM clause.

Example

Assume we have created a database named demo using the CREATE DATABASE statement −

CREATE DATABASE demo;

Now, let us create a table in it, using the CREATE TABLE statement −

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

Let us create two events with names example_event1 and example_event2 it inserts a record in the above created table one minute after the execution −

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

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

Following query lists out all the (upcoming) events in the database named demo −

SHOW EVENTS FROM demo\G;

Output

Following is the output of the above query −

************* 1. row *************
                  Db: demo
                Name: event_hourly
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MONTH
              Starts: 2021-05-12 22:13:53
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
************* 2. row *************
                  Db: demo
                Name: example_event
             Definer: root@localhost
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2021-05-12 23:13:10
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
2 rows in set (0.26 sec)

You can also use the IN clause instead of FROM as −

SHOW EVENTS IN demo\G;

Output

After executing the above query, it generates the output shown below −

************** 1. row **************
                  Db: demo
                Name: event_hourly
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MONTH
              Starts: 2023-12-13 12:04:41
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
************** 2. row **************
                  Db: demo
                Name: example_event
             Definer: root@localhost
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2023-12-13 13:04:35
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci

The LIKE clause

Using the LIKE clause, you can specify a pattern to retrieve specific tables. Following query retrieves the information about the events whose name starts with the letter "e".

SHOW EVENTS LIKE 'e%'\G;

Output

Following is the output of the above query −

************** 1. row ************** 
                  Db: demo
                Name: event_hourly
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MONTH
              Starts: 2023-12-13 12:04:41
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
**************  2. row ************** 
                  Db: demo
                Name: example_event
             Definer: root@localhost
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2023-12-13 13:04:35
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci

The WHERE clause

You can use the WHERE clause of the SHOW EVENTS statements to retrieve names of the tables which match the specified condition.

Example

Following query lists out event which is of type recurring from the database named demo. Here we are using the SHOW EVENTS statement with WHERE clause.

SHOW EVENTS FROM demo WHERE Type = 'RECURRING'\G;

Output

After executing the above query, it will generate the following output −

*************************** 1. row ***************************
                  Db: demo
                Name: event_hourly
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MONTH
              Starts: 2023-12-13 12:04:41
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
Advertisements