MYSQL timed Automatic event execution

MySQL supports the event function starting from 5.1, similar to the scheduled task job function of Oracle and MSSQL. With this feature, we can let MySQL automatically execute stored procedures to achieve data summarization and other functions, not the same as the previous manual operation completed. Let's test how to automate the execution of specified stored procedures in MySQL to implement related functions.

First, create a test table
CREATE TABLE event_table (
ID INT auto_increment PRIMARY KEY not NULL,
Conent VARCHAR (80)
) Engine=innodb DEFAULT Charset=utf8;

Second, create call stored procedure
DROP PROCEDURE IF EXISTS pr_event;
CREATE PROCEDURE Pr_event (
)
Proc_start:begin

INSERT into Event_table (conent) VALUES (' 520 ');

END Proc_start

Iii. creating an event call stored procedure

To create the event syntax:

CREATE EVENT [IF not EXISTS] Event_Name
On SCHEDULE SCHEDULE
[on completion [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT ' COMMENT ']
Do sql_statement;
For timed events to work, MySQL's constant global event_scheduler must be on or 1.
1. Check the current status of scheduler :
A, SHOW VARIABLES like ' Event_scheduler ';
B, SELECT @ @event_scheduler;

2. Modify the Scheduler status to open (0:off, 1:on):
Show VARIABLES like ' Event_scheduler ';--check whether the timer is on (off: Off, on: on)

Turn on the timer (four ways):

A, SET GLOBAL event_scheduler=on;
B, SET @ @global. Event_scheduler=on;
C, SET GLOBAL event_scheduler=1;
D, SET @ @global. event_scheduler=1;

3. Create event, set to execute every 10 minutes (call Pr_event stored procedure).
CREATE EVENT IF not EXISTS ent_test
On SCHEDULE every MINUTE
--On completion PRESERVE--When the event expires, the event is disable, but the event will still exist
--On completion not PRESERVE--when the event expires, the event is automatically deleted, which is the default value
Do call Pr_event (); --Pr_event () is a stored procedure that requires timed processing, and can also be changed to a related SQL executable statement

4. Open Event
ALTER EVENT ent_test ENABLE;

5. Temporary Shutdown Event
ALTER EVENT ent_test DISABLE;

6. Rename events and add notes
ALTER event Test.ent_test RENAME to ent_test_new COMMENT ' renaming events ent_test ';

7. Delete Events

DROP EVENT IF EXISTS ent_test;

8. View detailed information for the specified event

SELECT * from INFORMATION_SCHEMA. EVENTS WHERE event_name= ' ent_test ';

SELECT * from INFORMATION_SCHEMA. EVENTS WHERE event_name like CONCAT ('% ', ' ent_test ', '% ');

9. View the event creation SQL statement

SHOW CREATE EVENT ent_test;

Iv. Some typical application examples:

1. Regular execution every nine days from now

CREATE EVENT EVENT1

On SCHEDULE every 9 day starts now ()

On completion PRESERVE ENABLE

Do

BEGIN

Call Total ();

END

2, every month, the number of 1 o'clock in the morning to execute

CREATE EVENT EVENT2

On SCHEDULE Every 1 month starts Date_add (Date_add (Date_sub (), Curdate Day (INTERVAL ())-1 day), Curdate 1 month), I Nterval 1 HOUR)

On completion PRESERVE ENABLE

Do

BEGIN

Call STAT ();

END

3, each quarter of the 2 o'clock in the morning execution

CREATE EVENT total_season_event

On SCHEDULE every 1 QUARTER starts Date_add (Date_add (DATE (CONCAT (Curdate ()), '-', ELT (QUARTER (Curdate ()), 1,4,7,10 ), '-', 1), INTERVAL 1 QUARTER), INTERVAL 2 HOUR)

On completion PRESERVE ENABLE

Do

BEGIN

Call Season_stat ();

END

4, January 1, four o'clock in the morning each year to execute

CREATE EVENT total_year_event

On SCHEDULE Every 1 year starts Date_add (DATE (CONCAT (curdate () + 1, '-', 1, '-', 1)), INTERVAL 4 HOUR)

On completion PRESERVE ENABLE

Do

BEGIN

Call Year_stat ();

END

MySQL event and Oralce job is a bit different, this makes the author more troubled

5, in the Monthly, quarterly, annual automatic call stored procedures, in order to test the system can be changed to the last day of the year, such as 2010-12-31 23:59:55;

This Oracle job will execute the monthly, quarterly, and yearly stored procedures. But MySQL changed the system time and the event did not execute regularly. I wonder if you have any good ways to do prawns? This problem can be solved.

V. View event run basic information

SELECT * from Information_schema.events ORDER by last_executed DESC

SELECT * from Mysql.event ORDER by last_executed descshow EVENTS

This article references: http://www.jz123.cn/text/1134105.html

Http://blog.sina.com.cn/s/blog_5ceb51480101ndab.html

MYSQL timed Automatic event execution