Summary: in this tutorial, you will learn how to use the MySQL SHOW EVENTS statement to list events in a specific database.
Introduction to MySQL SHOW EVENTS statement
The SHOW EVENTS statement allows you to retrieve information about scheduled events within a database.
Here’s the basic syntax of the SHOW EVENTS statement:
SHOW EVENTS [FROM db_name]
[LIKE 'pattern' | WHERE expr];Code language: SQL (Structured Query Language) (sql)In this syntax:
FROMdb_name(optional): specify the database name after theSHOW EVENTkeywords to instruct from which database you want to show the events. If you omit theFROMclause, the statement shows events from the current database.LIKE 'pattern'(optional): allow you to filter the events based on a pattern. You can include wildcards (% and _) in the pattern.WHERE expr(optional): allow you to form a more complex condition to filter the events, including event status.
The SHOW EVENTS statement returns the output that includes the following fields:
| Field Name | Meaning |
|---|---|
| Db | The timestamp indicates when the event was created or started. |
| Name | The name of the event. This is the identifier you assigned. |
| Definer | The MySQL account that defined the event (username@host). |
| Time Zone | The time zone associated with the event’s schedule. |
| Type | Indicates whether the event is one-time or recurring. |
| Execute At | For recurring events, the next execution time; for one-time events, the scheduled execution time. |
| Interval Value | The timestamp indicates when the event was created or started. |
| Interval Field | The unit of time for the interval (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND). |
| Starts | The timestamp indicating when the event was created or started. |
| Ends | For recurring events, when the event is scheduled to end; for one-time events, it is NULL. |
| Status | The current status of the event (ENABLED or DISABLED). |
| Originator | The server ID of the MySQL server on which the event was created; used in replication. |
| Character_set_client | The value of the character_set_client system variable at the time the event was created. |
| Collation_connection | The value of the collation_connection system variable at the time the event was created. |
| Database Collation | The collation of the database with which the event is associated. |
MySQL SHOW EVENTS statement example
Let’s explore some examples of using the SHOW EVENTS statement.
1) Show all events in the current database
The following command displays a list of events in the current database:
SHOW EVENTS;Code language: SQL (Structured Query Language) (sql)2) Show all events from a specific database
To list all events from a specific database, you specify the database name after the FROM keyword:
SHOW EVENTS FROM database_name;Code language: SQL (Structured Query Language) (sql)3) Show all events matching a pattern
To display all events whose names match a pattern, you use the LIKE operator:
SHOW EVENTS LIKE 'daily%';Code language: SQL (Structured Query Language) (sql)In this example, we list all events whose names start with daily in the current database.
4) Show all events based on a condition
To show all events from a specific database that are currently enabled, you can use the WHERE clause:
SHOW EVENTS
WHERE db = 'database_name' AND status = 'ENABLED';Code language: SQL (Structured Query Language) (sql)5) Show detailed information for a specific event
To display detailed information for a specific event, you specify the event name in the LIKE clause:
SHOW EVENTS LIKE 'your_event_name'\G;Code language: SQL (Structured Query Language) (sql)Please note that the \G modifier formats the output vertically for better readability in the mysql client tool.
6) Show events with a specific status
To display events in the current database that are currently disabled or enabled, you use a condition in the WHERE clause:
SHOW EVENTS WHERE status = 'DISABLED';Code language: SQL (Structured Query Language) (sql)Summary
- Use the
SHOW EVENTSstatement to retrieve information about scheduled events
