Shulou(Shulou.com)05/31 Report--

This article introduces you how to achieve an event scheduler in MYSQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. Check to see if it is on:

Mysql > show variables like 'event_scheduler'

+-+ +

| | Variable_name | Value |

+-+ +

| | event_scheduler | ON |

+-+ +

1 row in set (0.01 sec)

Mysql > select @ @ event_scheduler

+-+

| | @ @ event_scheduler |

+-+

| | ON |

+-+

1 row in set (0.00 sec)

Mysql > show processlist

+-

-- +

| | Id | User | Host | db | Command | Time | State |

| | Info |

+-

-- +

| | 1 | event_scheduler | localhost | NULL | Daemon | 24 | Waitin |

G for next activation | NULL |

two。 Grammar

CREATE EVENT [IF NOT EXISTS] event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE]

[COMMENT 'comment']

DO sql_statement

Schedule:

AT TIMESTAMP [+ INTERVAL INTERVAL]

| | EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP] |

INTERVAL:

Quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Example:

Insert a record into the data table every second

USE test

CREATE TABLE aaa (timeline TIMESTAMP)

CREATE EVENT e_test_insert

Clear the test table after ON SCHEDULE EVERY 1 SECOND5 seconds (days)

CREATE EVENT e_test

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 SECOND

DO TRUNCATE TABLE test.aaa

CREATE EVENT e_test

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY

DO TRUNCATE TABLE test.aaa

Clear the test table at 09:39:20 on May 23rd, 2008

CREATE EVENT e_test

ON SCHEDULE AT TIMESTAMP '2008-05-23 9 purl 39 purl 20'

DO TRUNCATE TABLE test.aaa

Clear the test table at a fixed time every day

CREATE EVENT e_test

ON SCHEDULE EVERY 1 DAY

DO TRUNCATE TABLE test.aaa

Open after 5 days and clear the test table regularly every day.

CREATE EVENT e_test

ON SCHEDULE EVERY 1 DAY

STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY

DO TRUNCATE TABLE test.aaa

Here 5 days can also be 0 days, and the emptying table is opened at that time.

Clear the test table regularly every day, and stop execution after 5 days

CREATE EVENT e_test

ON SCHEDULE EVERY 1 DAY

ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY

DO TRUNCATE TABLE test.aaa

This setting requires that the number of days be greater than 1, otherwise an error will be reported. And the creation was not successful.

Five days later, the test table is emptied regularly every day, and the execution stops after one month.

CREATE EVENT e_test

ON SCHEDULE EVERY 1 DAY

STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY

ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH

DO TRUNCATE TABLE test.aaa; [ON COMPLETION [NOT] PRESERVE]

You can set whether this event is executed once or persistently. The default is NOT PRESERVE.

This event stops events that insert data every other second, and it feels like there's something wrong with what mysql is doing.

Clear the test table regularly every day (execute only once, and terminate the event after the task is completed)

CREATE EVENT e_test

ON SCHEDULE EVERY 1 DAY

ON COMPLETION NOT PRESERVE

DO TRUNCATE TABLE test.aaa

[ENABLE | DISABLE] sets whether the status of the event is enabled or disabled after it is created. The default is ENABLE.

[COMMENT 'comment'] you can comment on the event.

3 regular service daily maintenance test

5.4.1 Modification event (ALTER EVENT)

ALTER EVENT event_name

[ON SCHEDULE schedule]

[RENAME TO new_event_name]

[ON COMPLETION [NOT] PRESERVE]

[COMMENT 'comment']

[ENABLE | DISABLE]

[DO sql_statement]

A. Temporary shutdown event

ALTER EVENT e_test DISABLE

B. Open event

ALTER EVENT e_test ENABLE

C. Change the daily emptying of the test table to once every 5 days:

ALTER EVENT e_test

ON SCHEDULE EVERY 5 DAY

D. Rename the event and comment it

Alter event test.new_e_test rename to e_test comment 'estrangtestroomcm'

5.4.2 Delete event (DROP EVENT)

The syntax is simple, as follows:

DROP EVENT [IF EXISTS] event_name, for example, delete the e_test event created earlier

DROP EVENT eject; of course, if this event exists, otherwise it will generate an ERROR 1513 (HY000): Unknown event error, so it is best to add IF EXISTS

DROP EVENT IF EXISTS e_test

5.4.3 View events

A. To view the details of an event, you can use the following view:

SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'estrangtestworthy insert' AND EVENT_SCHEMA = 'test'G

B. A brief list of all event:show events

Syntax:

SHOW EVENTS [FROM schema_name]

[LIKE 'pattern' | WHERE expr]

Format to show all event

SHOW EVENTSG

Format the event of the test user

Show events FROM test

C. View the creation information of event

SHOW CREATE EVENT event_name

Show create event test.e_testG

On how to achieve an event scheduler in MYSQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.