In MySQL we can create scheduled events, which are tasks that run according to a specified schedule.
At any given time we can get a list of events for a given schema by using either of the following methods.
The SHOW EVENTS Statement
MySQL provides the SHOW EVENTS statement as a quick and easy way to list all the events in the current schema or another specified schema.
Example:
SHOW EVENTS;Result:
+------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ | test | Event5 | root@localhost | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2024-03-31 11:30:27 | NULL | DISABLED | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | test | Event6 | root@localhost | SYSTEM | RECURRING | NULL | 1 | HOUR | 2024-03-31 11:37:11 | NULL | DISABLED | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | test | Event7 | root@localhost | SYSTEM | ONE TIME | 2024-03-31 11:45:36 | NULL | NULL | NULL | NULL | DISABLED | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
By default SHOW EVENTS displays all events in the current schema.
We can use the FROM clause to specify a schema:
SHOW EVENTS FROM Test;Result:
+------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ | test | Event5 | root@localhost | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2024-03-31 11:30:27 | NULL | DISABLED | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | test | Event6 | root@localhost | SYSTEM | RECURRING | NULL | 1 | HOUR | 2024-03-31 11:37:11 | NULL | DISABLED | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | test | Event7 | root@localhost | SYSTEM | ONE TIME | 2024-03-31 11:45:36 | NULL | NULL | NULL | NULL | DISABLED | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
That example returned all events from the Test schema.
We can use the LIKE clause to narrow the results down if required:
SHOW EVENTS LIKE 'Event6%';Result:
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ | test | Event6 | root@localhost | SYSTEM | RECURRING | NULL | 1 | HOUR | 2024-03-31 11:37:11 | NULL | DISABLED | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
This narrowed the results down to just those events that start with Event6. Of course, there’s only one such event in my small list of events and so only one result is returned.
We can also use a WHERE clause:
SHOW EVENTS FROM Test
WHERE Type = 'Recurring';Result:
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+ | test | Event5 | root@localhost | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2024-03-31 11:30:27 | NULL | DISABLED | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | test | Event6 | root@localhost | SYSTEM | RECURRING | NULL | 1 | HOUR | 2024-03-31 11:37:11 | NULL | DISABLED | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
The EVENTS Information Schema Table
Another way of getting event information is with the information schema. The information schema contains a table called events, which stores information about scheduled events.
Example:
SELECT *
FROM information_schema.events
WHERE event_schema = 'test'
AND event_type = 'Recurring';Result:
+---------------+--------------+------------+----------------+-----------+------------+-------------------------------------------------------------------------------+------------+------------+----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+---------------------+------+----------+---------------+---------------------+---------------------+---------------------+-----------------------------+------------+----------------------+----------------------+--------------------+ | EVENT_CATALOG | EVENT_SCHEMA | EVENT_NAME | DEFINER | TIME_ZONE | EVENT_BODY | EVENT_DEFINITION | EVENT_TYPE | EXECUTE_AT | INTERVAL_VALUE | INTERVAL_FIELD | SQL_MODE | STARTS | ENDS | STATUS | ON_COMPLETION | CREATED | LAST_ALTERED | LAST_EXECUTED | EVENT_COMMENT | ORIGINATOR | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION | +---------------+--------------+------------+----------------+-----------+------------+-------------------------------------------------------------------------------+------------+------------+----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+---------------------+------+----------+---------------+---------------------+---------------------+---------------------+-----------------------------+------------+----------------------+----------------------+--------------------+ | def | test | Event5 | root@localhost | SYSTEM | SQL | INSERT INTO EventTest ( EventTimestamp, InsertedBy ) VALUES (NOW(), 'Event5') | RECURRING | NULL | 1 | MINUTE | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | 2024-03-31 11:30:27 | NULL | DISABLED | NOT PRESERVE | 2024-03-31 11:26:33 | 2024-03-31 11:30:27 | 2024-03-31 11:30:19 | | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | def | test | Event6 | root@localhost | SYSTEM | SQL | INSERT INTO EventTest ( EventTimestamp, InsertedBy ) VALUES (NOW(), 'Event6') | RECURRING | NULL | 1 | HOUR | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | 2024-03-31 11:37:11 | NULL | DISABLED | PRESERVE | 2024-03-31 11:22:41 | 2024-03-31 11:37:11 | 2024-03-31 11:22:51 | Insert timestamp into table | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +---------------+--------------+------------+----------------+-----------+------------+-------------------------------------------------------------------------------+------------+------------+----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+---------------------+------+----------+---------------+---------------------+---------------------+---------------------+-----------------------------+------------+----------------------+----------------------+--------------------+