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

This article will explain in detail the sample analysis of status viewing, opening commands and testing in mysql 5.1event Scheduler. I think it is very practical, so I hope you can get something after reading this article.

Since MySQL5.1.6, a very unique feature has been added-event Scheduler (Event Scheduler), which can be used to perform certain tasks on a regular basis (such as deleting records, summarizing data, and so on) to replace work that can only be performed by scheduled tasks of the operating system. What is more worth mentioning is that MYSQL's event scheduler can be accurate to execute one task per second, while the operating system's scheduled tasks (such as tasks under CRON or Windows) can only be executed exactly once per minute. It is very suitable for some applications that require high real-time data (such as stocks, odds, scores, etc.).

The event scheduler is sometimes referred to as a temporary trigger (temporal triggers) because the event scheduler performs certain tasks based on a specific time period, while the trigger (Triggers) is triggered based on events generated by a table, and that's the difference.

Before using this function, you must make sure that event_scheduler is enabled and executable.

SET GLOBAL event_scheduler = 1

Or we can add event_scheduler = 1 or event_scheduler to the configuration my.ini file

SET GLOBAL event_scheduler = ON

To enable it, you can also add "- event_scheduler=1" directly to the startup command, for example:

D...-- event_scheduler=1

To see if event Scheduler is currently enabled, execute the following SQL:

SHOW VARIABLES LIKE 'event_scheduler'

Or

SELECT @ @ event_scheduler

Or

An account with SUPER permission can see this thread by executing SHOW PROCESSLIST.

5.3. Timing service configuration

Let's first take a look at its 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}

5.3.1 insert a record into the data table per second

USE test

CREATE TABLE aaa (timeline TIMESTAMP)

CREATE EVENT e_test_insert

ON SCHEDULE EVERY 1 SECOND

DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP)

Wait 3 seconds, then execute the query to see:

Mysql > SELECT * FROM aaa

+-+

| | timeline |

+-+

| | 2007-07-18 20:44:26 |

| | 2007-07-18 20:44:27 |

| | 2007-07-18 20:44:28 |

+-+

5.3.25 seconds (days) to clear the test table

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

5.3.3 May 23, 2008 09:39:20 completely empty the test table

CREATE EVENT e_test

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

DO TRUNCATE TABLE test.aaa

There's something wrong with this test. I don't quite understand why.

5.3.4 clear the test table regularly every day

CREATE EVENT e_test

ON SCHEDULE EVERY 1 DAY

DO TRUNCATE TABLE test.aaa

5.3.5 start emptying the test table regularly every day after 5 days

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.

5.3.6 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.

5.3.7 after 5 days, start emptying the test table regularly every day, and stop execution 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.

5.3.8 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.

5.4. Daily maintenance test of timing service

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 EVENTS\ G

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_test\ G

This is the end of this article on "sample analysis of status viewing, opening commands and tests in mysql 5.1event scheduler". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please 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.