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

This article mainly explains "MySQL event scheduler usage introduction," interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let Xiaobian take you to learn "Introduction to MySQL Event Scheduler Usage"!

Event schedulers are sometimes referred to as temporary triggers, because event schedulers perform certain tasks based on triggers for a specific time period, whereas triggers are triggered based on events generated by a table, and that's where the difference lies.

Before using this feature, you must ensure that event_scheduler is turned on and executable

SET GLOBAL event_scheduler = 1;

Or we can add event_scheduler = 1 or

SET GLOBAL event_scheduler = ON;

To open, you can also add "-event_scheduler=1" directly to the start command, for example:

d ... --event_scheduler=1

To see if the event dispatcher is currently turned on, execute the following SQL:

SHOW VARIABLES LIKE 'event_scheduler';

or

SELECT @@event_scheduler;

or

Accounts with SUPER permissions execute SHOW PROCESSLIST to see this thread.

5.3 Timing service configuration

Let's look at its syntax:

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 one record per second into the data table

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 and 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.2 Clear test table after 5 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;

5.3.3 May 23, 2008 9:39:20 clear test table

CREATE EVENT e_test

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

DO TRUNCATE TABLE test.aaa;

There is something wrong with this test. I don't know 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 Open 5 days later 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, then open the empty table

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 is greater than 1, otherwise an error is reported. And it didn't work.

5.3.7 5 days after the start of regular daily emptying test table, a month after the stop execution

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 inserting data into events every second, and it feels like mysql is still having problems with this.

5.3.8 Clear the test table regularly every day (only execute 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] can be used to set whether the status is enabled or disabled after the event is created. The default is ENABLE.

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

5.4 Regular service routine maintenance test

5.4.1 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 closure event

ALTER EVENT e_test DISABLE;

b. Opening events

ALTER EVENT e_test ENABLE;

c. Change the test table to be cleared once every 5 days:

ALTER EVENT e_test

ON SCHEDULE EVERY 5 DAY;

d. Renaming and annotating events

alter event test.new_e_test rename to e_test comment 'e_test_cm';

5.4.2 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 e_test; If this event exists, then ERROR 1513 (HY000): Unknown event error is generated.

DROP EVENT IF EXISTS e_test;

5.4.3 view event

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

SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'test_insert' AND EVENT_SCHEMA = 'test'\G;

b. Briefly list all events: show events

Grammar:

SHOW EVENTS [FROM schema_name]

[LIKE 'pattern' | WHERE expr]

Format all events

SHOW EVENTS\G

Format events that show test users

show events FROM test;

c. View event creation information

SHOW CREATE EVENT event_name

show create event test.e_test\G

5.5 Conclusion

This feature is really useful as a timed purge of data tables, monitoring of master and slave servers, aggregation of data to another table, etc., and can be accurate to the second, real-time can also be guaranteed.

However, if two events conflict when they target the same object, it is not clear whether this is what I understand or whether it is true, such as insertion and deletion per second. In addition to scheduling SQL statements, MYSQL's scheduler can also schedule stored procedures.

5.6 Disadvantages

SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'test_insert' AND EVENT_SCHEMA = 'troaudit_db'\G;

*************************** 1. row ***************************

EVENT_CATALOG: NULL

EVENT_SCHEMA: troaudit_db

EVENT_NAME: event_session_table

DEFINER: egilance@%

TIME_ZONE: SYSTEM

EVENT_BODY: SQL

EVENT_DEFINITION: BEGIN

CALL create_table_process;

END

EVENT_TYPE: RECURRING

EXECUTE_AT: NULL

INTERVAL_VALUE: 1800

INTERVAL_FIELD: SECOND

SQL_MODE:

STARTS: 2011-08-23 10:51:28

ENDS: NULL

STATUS: ENABLED

ON_COMPLETION: PRESERVE

CREATED: 2011-08-23 10:51:28

LAST_ALTERED: 2011-08-23 10:51:28

LAST_EXECUTED: 2011-08-23 17:55:51

EVENT_COMMENT:

ORIGINATOR: 0

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: utf8_unicode_ci

1 row in set (0.00 sec)

MySQL only records the time of the last dispatch, and if the time is adjusted forward and less than the most recent execution time, the event dispatch will not be executed.

At this point, I believe that everyone has a deeper understanding of the "MySQL event scheduler usage introduction", may wish to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

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.