MySQL Event learning

MySQL Event learning

In System management or database management, it is often imperative to execute a command or SQL statement periodically. Anyone familiar with the Linux system knows the Cron scheduling task of Linux, and it is easy to implement the functions of running the specified commands on a regular basis. MySQL launched the event Scheduler after 5.1, which, like the cron features of Linux, makes it easy to Scheduler the MySQL database, and it can be accurate to seconds. Very simple and convenient to use.

Because of the recent need to use the function of the event, so learning a bit, feel very good, summed up, convenient for later use, but also hope that other beginners can help.

First, if the event is turned on

In using event this feature, first make sure that your MySQL version is above 5.1 and then also check whether the events on your MySQL server are turned on.

To see if the event is turned on, use the following command to view:

SHOW VARIABLES like ' Event_scheduler ';

SELECT @ @event_scheduler;

SHOW processlist;

If you see a message stating that Event_scheduler is on or processlist displays Event_scheduler, the event is turned on. If it is displayed as off or if Event_scheduler is not seen in Processlist, then the event is not turned on and we need to turn it on.

The MySQL event is turned on in the following three ways:

• modifying with Dynamic parameters

SET GLOBAL event_scheduler = on;

This parameter will take effect immediately after you change it.

Note: You still want to add Event_scheduler=on in MY.CNF. Because if not added, the MySQL restart event will return to its original state.

• Change the configuration file and restart

In the [mysqld] section of MY.CNF, add the following, and then restart MySQL.

Event_scheduler=on

• Start by setting event parameters

Mysqld ...--event_scheduler=on

Second, the MySQL event syntax introduction

1. Syntax for creating events

CREATE

[definer = {User | Current_User}]

EVENT

[IF not EXISTS]

Event_Name

On SCHEDULE SCHEDULE

[on completion [NOT] PRESERVE]

[ENABLE | DISABLE | DISABLE on SLAVE]

[COMMENT ' COMMENT ']

Do event_body;

Schedule

at timestamp [+ INTERVAL INTERVAL] ...

| Every interval

[Starts timestamp [+ INTERVAL INTERVAL] ...]

[ENDS timestamp [+ INTERVAL INTERVAL] ...]

Interval

Quantity {Year | QUARTER | MONTH | Day | HOUR | MINUTE |

WEEK | SECOND | Year_month | Day_hour |

Day_minute | Day_second | Hour_minute |

Hour_second | Minute_second}

Parameters Detailed Description:

Definer: Defines the user who checks permissions when an event executes.

On SCHEDULE SCHEDULE: Defines the time and time interval for execution.

On completion [NOT] PRESERVE: Defines whether an event is executed once or permanently, by default one execution, or not PRESERVE.

ENABLE | DISABLE | DISABLE on SLAVE: Defines whether the event is opened or closed after it is created, and shuts down from the top. The Disable on SLAVE is automatically added to the statement that automatically synchronizes the creation event of the Lord from the server.

COMMENT ' COMMENT ': Defines the comment for the event.

1. Change the syntax of an event

Alter

[definer = {User | Current_User}]

EVENT Event_Name

[on SCHEDULE SCHEDULE]

[on completion [NOT] PRESERVE]

[RENAME to New_event_name]

[ENABLE | DISABLE | DISABLE on SLAVE]

[COMMENT ' COMMENT ']

[Do Event_body]

1. Syntax for deleting events

DROP EVENT [IF EXISTS] Event_Name

Third, the MySQL event combat

1. Test environment

Create a test table for testing:

CREATE TABLE ' Test ' (
' id ' int (one) not NULL auto_increment,
' T1 ' datetime DEFAULT NULL,
' Id2 ' int (one) not NULL DEFAULT ' 0 ',
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=106 DEFAULT Charset=utf8

1. Combat 1

• Create an event that inserts a single piece of data into the test table every 3 seconds, with the following code:

CREATE EVENT IF not EXISTS test on SCHEDULE every 3 SECOND

On completion PRESERVE

Do INSERT into Test (ID,T1) VALUES (", now ());

• Create an event that empties the test table data after 10 minutes

CREATE EVENT IF not EXISTS test

On SCHEDULE

At Current_timestamp + INTERVAL 1 MINUTE

Do TRUNCATE TABLE test.aaa;

• Create an event that clears the test table data at the time of 2012-08-23 00:00:00, with the following code:

CREATE EVENT IF not EXISTS test

On SCHEDULE

At TIMESTAMP ' 2012-08-23 00:00:00 '

Do TRUNCATE TABLE test;

• Create an event that starts from August 22, 2012 21:45 to 10 minutes and runs every 3 seconds to insert a data into the test table, with the following code:

CREATE EVENT IF not EXISTS test on SCHEDULE every 3 SECOND

Starts ' 2012-08-22 21:49:00 '

ENDS ' 2012-08-22 21:49:00 ' + INTERVAL MINUTE

On completion PRESERVE

Do INSERT into Test (ID,T1) VALUES (", now ());

1. Combat 2

The usual scenario is to call the stored procedure periodically through events, and here's a simple example:

Create a stored procedure that adds cardinality 2 to each row of the Id2 field of the test table, with the following stored procedure Code:

DROP PROCEDURE IF EXISTS test_add;

DELIMITER//

CREATE PROCEDURE Test_add ()

BEGIN

DECLARE 1_id INT DEFAULT 1;

DECLARE 1_id2 INT DEFAULT 0;

DECLARE error_status INT DEFAULT 0;

DECLARE datas CURSOR for SELECT ID from test;

DECLARE CONTINUE HANDLER for not FOUND SET Error_status=1;

OPEN datas;

FETCH datas into 1_id;

REPEAT

SET 1_id2=1_id2+2;

UPDATE test SET id2=1_id2 WHERE id=1_id;

FETCH datas into 1_id;

UNTIL Error_status

END REPEAT;

CLOSE datas;

END

//

Event Settings 2012-08-22 00:00:00 start running, every 1 calls to the stored procedure, 40 days after the end, the code is as follows:

CREATE EVENT test on SCHEDULE every 1 day

Starts ' 2012-08-22 00:00:00 '

ENDS ' 2012-08-22 00:00:00 ' +interval

On completion PRESERVE do

Call Test_add ();

Iv. Reference Links

Http://dev.mysql.com/doc/refman/5.1/en/events-configuration.html

Http://dev.mysql.com/doc/refman/5.1/en/create-event.html

http://blog.163.com/[email protected]/blog/static/8854373520105182123112/

Reprint to: http://blog.chinaunix.net/uid-20639775-id-3323098.html

MySQL Event learning