Introduction to the use of MYSQL event viewer
- 2020-05-13 03:40:14
- OfStack
To see if the event scheduler is currently enabled
SHOW VARIABLES LIKE 'event_scheduler';
Opens the event viewer
SET GLOBAL event_scheduler = 1;
Create an event
Grammar:
CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
Example:
CREATE EVENT updateInfoStatus
ON SCHEDULE EVERY 30 MINUTE
DO
UPDATE `jd_article` SET `status` = 0 WHERE `status` = 1 AND `date` < = now( );
Modify event
Grammar:
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]
Example:
ALTER EVENT updateInfoStatus
ON SCHEDULE EVERY 30 MINUTE
DO
UPDATE `jd_article` SET `status` = 0 WHERE `status` = 1 AND `date` < = now( );
Delete events
DROP EVENTS IF EXISTS updateInfoStatus
Temporary shutdown event
ALTER EVENTS updateInfoStatus DISABLE
Open the event
ALTER EVENTS updateInfoStatus ENABLE
View the event details
SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'updateInfoStatus' AND EVENT_SCHEMA = 'jdwc'
View all events
SHOW EVENTS
View event creation information
SHOW CREATE EVENT updateInfoStatus
The author Zhou Hr
SHOW VARIABLES LIKE 'event_scheduler';
Opens the event viewer
SET GLOBAL event_scheduler = 1;
Create an event
Grammar:
CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
Example:
CREATE EVENT updateInfoStatus
ON SCHEDULE EVERY 30 MINUTE
DO
UPDATE `jd_article` SET `status` = 0 WHERE `status` = 1 AND `date` < = now( );
Modify event
Grammar:
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]
Example:
ALTER EVENT updateInfoStatus
ON SCHEDULE EVERY 30 MINUTE
DO
UPDATE `jd_article` SET `status` = 0 WHERE `status` = 1 AND `date` < = now( );
Delete events
DROP EVENTS IF EXISTS updateInfoStatus
Temporary shutdown event
ALTER EVENTS updateInfoStatus DISABLE
Open the event
ALTER EVENTS updateInfoStatus ENABLE
View the event details
SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'updateInfoStatus' AND EVENT_SCHEMA = 'jdwc'
View all events
SHOW EVENTS
View event creation information
SHOW CREATE EVENT updateInfoStatus
The author Zhou Hr