MySQL Event learning

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.