Events in MySQL

Overview

The event Scheduler is a new feature after MySQL5.1 that triggers a database to be triggered by a custom time period, which can be understood as a time trigger 类似于linux系统下面的任务调度器crontab，或者类似与window下面的计划任务 . It is 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.

See if the event function 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;#查看事件状态SHOW EVENTS;

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.

Turn on event function

Method One, through the dynamic parameter modification

SET GLOBAL event_scheduler = ON;

After changing this parameter, it takes effect immediately, but the restart MySQL is restored again, that is, the settings cannot be restarted across reboots.
mode Two, change the configuration file and then restart
In the [mysqld] section of MY.CNF, add the following, and then restart MySQL.

event_scheduler=ON;

Once and for all, you need permission to modify the database configuration.

Mode three, directly in the start command plus "–event_scheduler=1"

--event_scheduler=ON

syntax for events 1. Create an event

create  [definer = {user< /span> |    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}

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 Events

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. Syntax for deleting events

DROP EVENT [IF EXISTS] event_name;

examples of event use 1. Create an event

Example One
Inserts data into the table Test2 every 3 seconds at regular intervals

create event event_insert_t2on schedule every 3 secondon completion preservedo insert into test2(department,time_v) value(‘1‘,now());

Execution results

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

CREATE EVENT IF NOT EXISTS event_truncate_test2ON SCHEDULEAT CURRENT_TIMESTAMP + INTERVAL 10 MINUTEDO TRUNCATE TABLE test2;

example Three
Create an event that clears the test table data at the time of 2015-04-17 14:42:00

DROP EVENT IF EXISTS event_truncate_test2;CREATE EVENT event_truncate_test2ON SCHEDULEAT TIMESTAMP ‘2015-04-17 14:42:00‘DO TRUNCATE TABLE test2;

example Four
5 days after the start of the day 3 seconds to insert data into the table test2, after one months to stop execution

CREATEEVENTIF  not EXISTSEvent_truncate_test2 onSCHEDULE every3 SECONDStartsCurrent_timestamp+INTERVAL 5  DayENDSCurrent_timestamp+INTERVAL  1 Month onCompletionPRESERVE Do INSERT  intoTest2 (DEPARTMENT,TIME_V)VALUES(' 1 ', now ());

example of modifying events

Example One
Temporary shutdown events

alter event event_insert_t2 disable;

Other similar creation events.

example of deleting an event

DROP EVENT IF EXISTS event_insert_t2;

An event invokes a stored procedure or function as if it were a normal call.

the pros and cons of events and their application scenarios

1. Advantages
   
   • Scheduled tasks are managed centrally by the DBA to avoid deployment at the operating system level.
   • Reduce the risk of mis-operation by system administrators.
   • Facilitate the follow-up management and maintenance.
2. Disadvantages
   
   • Carefully deploy and enable the scheduler on busy and performance-demanding databases.
   • Overly complex processing is more appropriate for using program implementations.
   • The Enable and shutdown events require superuser privileges.
3. Application Scenarios
   It is suitable for collecting statistic information regularly, clearing historical data periodically, checking the database regularly, and so on.

Reference

http://blog.163.com/[email protected]/blog/static/8854373520105182123112/
Http://blog.chinaunix.net/uid-20639775-id-3323098.html

Events in MySQL