MySQL Event learning

1. Turn events on or off

1) See if the event is turned on (two ways)

 like ' Event_scheduler ' ; SELECT @ @event_scheduler;

2) Turn on MySQL events (two ways)

SET = 1 ; SET = on;

3) Turn off the MySQL event

SET = OFF ; SET @ @global = OFF ; SET = 0 ; SET @ @global = 0;

2. Increment and Revise event syntax

1) Add (Create) event

CREATE    [definer = {User | Current_User}]EVENT[IF not EXISTS]Event_Name onSCHEDULE SCHEDULE[On completion [ not]PRESERVE][ENABLE | DISABLE | DISABLE on SLAVE]    [COMMENT ' COMMENT ']Do event_body; Schedule:attimestamp [+ 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.

2) Change 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]

3) Delete Event

DROP [IF EXISTS] event_name;

3. Usage

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

CREATE IF  not EXISTS  on 3 SECOND  on  INSERTintoVALUES(", Now ());

2) Create an event that empties the test table data after 10 minutes with the following code:

CREATE IF  not EXISTS Test  on  current_timestamp+1TRUNCATETABLE test.aaa;

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

CREATE IF  not EXISTS Test  on  TIMESTAMP'2012-08-23 00:00:00'TRUNCATE TABLE test;

4) Create an event that inserts a single piece of data into the test table every 3 seconds from 2012 August 22nd, 21 points from 45 to 10 minutes, with the following code:

CREATEEVENTIF  not EXISTSTest onSCHEDULE every3Secondstarts'2012-08-22 21:49:00'ENDS'2012-08-22 21:49:00'+INTERVALTenMINUTE onCompletion PreservedoINSERT  intoTest (ID,T1)VALUES("', now ());

5) Create a run starting from 2012-08-22 00:00:00, call the stored procedure every 1 days,end 40 days later, the code is as follows:

CREATE  on 1  Day  '2012-08-22 00:00:00'2012-08-22 00:00:00'  + Day on
Do call Test_add ();

4. Reference links

Http://blog.chinaunix.net/uid-20639775-id-3323098.html

MySQL Event learning