Events in MySQL

First, the foreword since MySQL5.1.0, has added a very characteristic function – Event Scheduler (Scheduler), can be used to perform certain tasks on a timed basis (for example: Deleting records, summarizing data, etc.) to replace the work that was previously performed only by the operating system's scheduled tasks. It is also worth mentioning that the MySQL event Scheduler can perform one task per second, while the operating system's scheduled tasks (such as Cron under Linux or Scheduled tasks under Windows) can only be performed once per minute. For some applications that require a higher level of real-time data (e.g., stocks, odds, scores, etc.), it is very suitable. The event scheduler can sometimes also be called a temporary trigger (temporal triggers) because the event scheduler performs certain tasks based on a specific time period trigger, and the trigger (triggers) is triggered based on events generated by a table, and the difference is here. Before you can use this feature, you must ensure that Event_scheduler is turned on and executable

SET GLOBAL event_scheduler = 1;
---or we can add Event_scheduler = 1 in the configuration My.ini file

Or

SET GLOBAL event_scheduler = on;

To open, you can also add "–event_scheduler=1" directly to the start command, for example:

Mysqld ...--event_scheduler=1

To see if the event Scheduler is currently turned on, perform the following sql:

Like ' Event_scheduler ';

Or

SELECT @ @event_scheduler;

Or

SHOW processlist;

Second, create event to see its syntax first:

CREATE EVENT [IFNotEXISTS] Event_Name
On SCHEDULE SCHEDULE
[On completion [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT ' COMMENT ']
 Do sql_statement;
 
Schedule
AtTIMESTAMP [+INTERVALinterval] 
 | Every interval [Starts timestamp] [ENDS timestamp] 
 
 interval: 
 Quantity {year | quarter | month | day | hour | minute | 
 week | second | year_month         

1) First look at a simple example to demonstrate inserting a record into a data table per second

Use test;
TIMESTAMP);
CREATE EVENT E_test_insert

VALUES (current_timestamp);

Wait 3 seconds, then execute the query to see:

From AAA;
+

+



+---------------------+

2) Empty the test table after 5 days:

CREATE EVENT E_test
Day
TRUNCATE TABLE test.aaa;

3) July 20, 2007 12 O'Clock full empty test table:

CREATE EVENT E_test
TIMESTAMP ' 2007-07-20 12:00:00 '
TRUNCATE TABLE test.aaa;

4) Clear the test table periodically every day:

CREATE EVENT E_test
Day
TRUNCATE TABLE test.aaa;

5) After 5 days, open the test table at regular intervals every day:

CREATE EVENT E_test
Day
Day
TRUNCATE TABLE test.aaa;

6) Clear the test table every day and stop execution after 5 days:

CREATE EVENT E_test
Day
Day
TRUNCATE TABLE test.aaa;

7) After 5 days, open the test table periodically, and stop execution after one months:

CREATE EVENT E_test
Day
Day
MONTH
TRUNCATE TABLE test.aaa;

[on completion [NOT] PRESERVE] can set whether this event is executed once or persisted, default to not PRESERVE.
8) Clear the test table every day (only once and terminate the event when the task is completed):

CREATE EVENT E_test
Day
Not PRESERVE
TRUNCATE TABLE test.aaa;

[ENABLE | DISABLE] But sets whether the state is turned on or off after the event is created, and the default is enable.
[COMMENT ' COMMENT '] can add comments to the event. III. modifying events (Alter event)

ALTER EVENT Event_Name
[on SCHEDULE SCHEDULE]
[RENAME to New_event_name]
[On completion [not] PRESERVE]
[COMMENT ' COMMENT ']
[ENABLE | DISABLE]
[Dosql_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

The Delete events (drop event) syntax is simple, as follows:

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

EXISTS e_test;
Note: If you have performed the event, alter event event_name disable. Then when you restart the MySQL service
The event will be deleted (Beta version: 5.1.30)

Application case this case is to use the nature of the event scheduler, call a stored procedure every second, to determine whether the SLAVE is working properly, if SLAVE is turned off, ignore 0 errors, and then restart SLAVE.

• Create a stored procedure first
  
  Delimiter//
  CREATE PROCEDURE ' Slave_monitor ' ()
  Begin
  SELECT Variable_value into @SLAVE_STATUS
  From INFORMATION_SCHEMA. Global_status
  WHERE variable_name= ' slave_running ';
  IF (' on '! = @SLAVE_STATUS) Then
  SET GLOBAL sql_slave_skip_counter=0;
  SLAVE START;
  END IF;
  End //
  
  Because statements like SHOW SLAVE STATUS cannot be called in a stored procedure, exact replication error messages and error codes cannot be obtained and further processing of SLAVE stops is not possible.
• Next, create the task
  
  CREATE EVENT IF not EXISTS ' Slave_monitor '
  On SCHEDULE every 5 SECOND
  On completion PRESERVE
  Do
  Call Slave_monitor ();
  
  A task is created, executed every 5 seconds, and the task is retained after the task is completed, rather than deleted. Of course, the task in this example will not end unless it is manually disabled.
• If you want to temporarily close a task while running, execute the ALTER EVENT statement:
  (root:localhost:) test> alter event ' Slave_monitor ' on
  Completion PRESERVE DISABLE;
  (root:localhost:) test> alter event ' Slave_monitor ' on
  Completion PRESERVE ENABLE;
• Source: Http://blog.163.com/[email protected]/blog/static/8854373520105182123112/