MySQL timed task configuration

Doing projects sometimes run into some states that need to be maintained on a daily basis, and my plan is to write SQL statements or stored procedures, and then configure the MySQL scheduler to let the database execute every day. Don't say much nonsense, go directly into the text:

If the current time is 2016-12-28 12:05:00, you execute the following statement, until 12:10, the system automatically executes the dispatch task to insert a record into the AAA table.

Select @ @event_scheduler; #查看事件调度程序, if off, executes the next statement
SET GLOBAL event_scheduler = on; #设置时间调度程序为ON
#每天的2016-12-28 12:10:00 execute INSERT INTO TEST.AAA VALUES (current_timestamp);
Use test;
CREATE TABLE aaa (Timeline TIMESTAMP); CREATE EVENT E_test
On SCHEDULE every 1 day starts ' 2016-12-28 12:10:00 '
Do INSERT into Test.aaa VALUES (current_timestamp);

The above content simple change, can meet the daily task scheduling, the following information for reference.

The default EVENT support is not started, and you can view the status by using the following command:

Select @ @event_scheduler;

If you return OFF, you need to execute the following command to start:

SET GLOBAL event_scheduler = on;

OK, although the event is started on the above, and the event does not start automatically after each restart of MySQL, how do I get it to start automatically?

Method One: Locate the currently used. cnf file and add the following line under [Mysqld]

Event_scheduler=1

Method Two: Increase the--event_scheduler=1 when you start MySQL

MySQL start--event_scheduler=1

viewing events

Mysql> Show Events

Mysql> Show Full Events

Mysql> SELECT * from Mysql.event;

Mysql> SELECT * from information_schema.events;

2> modifying events (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]

1) Temporary Shutdown event

ALTER EVENT e_test DISABLE;

2) Turn on event

ALTER EVENT e_test ENABLE;

3) Change the daily empty test table to 5 days to clear:

ALTER EVENT E_test

On SCHEDULE every 5 day;

3> Delete events (drop event)

The syntax is simple, as follows:

DROP EVENT [IF EXISTS] Event_Name

For example, delete the previously created E_test event

DROP EVENT e_test;

Of course the premise is that this event exists, otherwise it will generate error 1513 (HY000): Unknown event error, so it is best to add IF EXISTS

DROP EVENT IF EXISTS e_test;

First 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}

Let's look at a simple example to demonstrate inserting a record into a 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;

Re-create July 20, 2007 12 O'Clock full empty test table:

CREATE EVENT E_test

On SCHEDULE at TIMESTAMP ' 2007-07-20 12:00:00 '

Do TRUNCATE TABLE test.aaa;

The above data for the completion of complex task scheduling, the following is the example of scheduling:

2) Empty the test table after 5 days:

CREATE EVENT E_test

On SCHEDULE @ current_timestamp + INTERVAL 5 Day

Do TRUNCATE TABLE test.aaa;

3) July 20, 2007 12 O'Clock full empty test table:

CREATE EVENT E_test

On SCHEDULE at TIMESTAMP ' 2007-07-20 12:00:00 '

Do TRUNCATE TABLE test.aaa;

4) Clear the test table periodically every day:

CREATE EVENT E_test

On SCHEDULE every 1 day

Do TRUNCATE TABLE test.aaa;

5) After 5 days, open the test table at regular intervals every day:

CREATE EVENT E_test

On SCHEDULE every 1 day

Starts Current_timestamp + INTERVAL 5 Day

Do TRUNCATE TABLE test.aaa;

6) Clear the test table 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;

7) After 5 days, open the test table periodically, and stop execution after one months:

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] can set whether this event is executed once or persisted, default to not PRESERVE.

8) Clear the test table every day (only once and terminate the event when the task is completed):

CREATE EVENT E_test

On SCHEDULE every 1 day

On completion not PRESERVE

Do TRUNCATE TABLE test.aaa;

[ENABLE | DISABLE] But sets whether the state is turned on or off after the event is created, and the default is ENABLE.

[COMMENT ' COMMENT '] can add comments to the event.

The above content from the website or teaching materials, such as related to copyright issues, please leave a message with this.

MySQL timed task configuration