How to write timed tasks for MySQL

To see if it is turned on:

Show variables like ' Event_scheduler ';

If off is displayed, enter the following statement to open:

Set global Event_scheduler = on;

Note: Although the event is turned on using the set global Event_scheduler = ON statement, each time you restart the computer. or restart the MySQL service, you will find that the event automatically shuts down (Event_scheduler=off), so you want to keep the event open, it is best to modify the configuration file, so that the MySQL service startup time, only in the my.ini configuration file [mysqld] section plus Event_scheduler=on , as follows:

Examples of custom events

First, create a new user table:

--------------------------------table structure for ' user '------------------------------DROP table IF EXISTS ' user '; CREATE TABLE ' user ' (  ' id ' bigint) NOT null auto_increment,  ' name ' varchar ($) NOT NULL,  ' address ' varcha R (+) not NULL,  ' Addtime ' datetime is NOT NULL,  PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8;

Then, a new event, there are two types of events, one is the interval trigger and the other is a specific event trigger .

Type one: Insert a piece of data every minute:

Parameter description:

Definer: Creator;

On completion [NOT] PRESERVE: Indicates that the event is deleted when the event no longer occurs (note the event executed at a specific time, if the parameter is set, the event will be deleted when the execution is complete, and it can be set to on if it does not want to delete it completion PRESERVE);

ENABLE: Indicates that the system will execute this event;

BEGIN    INSERT into USER (name, address,addtime) VALUES (' test1 ', ' test1 ', now ());    INSERT into USER (name, address,addtime) VALUES (' test2 ', ' test2 ', now ()); END;;D Elimiter;

type two: Inserts a piece of data at a specific time, such as 2016-01-17 15:30:00

Parameter description:

Definer: Creator;

On completion [NOT] PRESERVE: Indicates that the event is deleted when the event no longer occurs (note the event executed at a specific time, if the parameter is set, the event will be deleted when the execution is complete, and it can be set to on if it does not want to delete it completion PRESERVE);

ENABLE: Indicates that the system will execute this event;

BEGIN    INSERT into USER (name, address,addtime) VALUES (' at ', ' @ ', now ()); END;;D Elimiter;

To view the results, the results are correct:

MySQL's timed task is usually done with an event, and the trigger cannot be completed.
One, through the MySQL command line client to complete
1, set global Event_scheduler = 1; Open Event_scheduler
Execute this statement if it appears, you can set the [Mysqld] segment in the MySQL configuration document to add Event_scheduler=on
If you restart MySQL, this situation still goes wrong, error code:1290. The MySQL server is running with the--event-scheduler=disabled
or--skip-grant-tables option so it cannot the execute this statement, the error is that if you specify the--skip-grant-tables option when you start the server, Event_ The scheduler is automatically set to disabled. The settings for the command line or configuration file will be overwritten. It is recommended to reproduce the installation of MySQL or modify the startup parameters (as specified in the system service).

View Event_scheduler status: Show status like '%event% '; OrSELECT @@event_scheduler;
2, create PROCEDURE Mypro ()//Create stored procedure

BEGIN
Update UserInfo SET endtime = Now () WHERE id = ' 155 ';
END;
3. Create event My_enevt, execute once every 30 seconds
Create event if not exists e_test
On schedule every second
On completion Preserve
Do call Mypro ();
4. Closing events
Alter event e_test on completion PRESERVE DISABLE;
5. Open Event
Alter event e_test on completion PRESERVE ENABLE;
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:

1) First look at a simple example to demonstrate inserting a record into a data table per second
CREATE EVENT E_test_insert on SCHEDULE every 1 SECOND does insert into TEST.AAA VALUES (current_timestamp);

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, 2013 12 O'Clock full empty test table:
CREATE EVENT e_test on SCHEDULE at TIMESTAMP ' 2013-07-20 12:00:00 ' dotruncate 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 performed once or persisted, default to not PRESERVE
8)
Empty the test table periodically 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;

[COMMENT
Comment] can annotate the event

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;

Second, the use of third-party tools to create, recommend the use of Heidisql website: http://www.heidisql.com/, free open source client, by the German programmer Ansgar Becker Development, the interface of the software has a Chinese interface.
Open Heidisql, right-click the table name, choose to create a new menu, then select the event, it is convenient, we can edit the event time settings and code. This defines the person to fill out [email protected].

How to write timed tasks for MySQL