(14) mysql events and 14mysql events

(14) mysql events and 14mysql events
Overview

The event scheduler is a new feature after MySQL5.1. It can trigger an operation on a database according to a custom time cycle and can be understood as a time trigger,Similar to crontab in linux, or similar to scheduled tasks in Windows. It is worth mentioning that the MySQL event scheduler can execute a task exactly every second, and the scheduled task of the operating system (such as CRON in Linux or task plan in Windows) it can be executed only once per minute.

Check whether the event function is enabled

When using the event function, you must first ensure that your mysql version is 5.1 or later, and then check whether the event on your mysql server is enabled.
Run the following command to check whether the event is Enabled:

# Method 1 show variables like 'event _ scheduler '; # method 2 SELECT @ event_schedist; # method 3 show processlist; # view event status show events;

If event_schedist is on or PROCESSLIST shows event_schedist information, the event is enabled. If the event is off or the event_scheduler information cannot be viewed in PROCESSLIST, it indicates that the event is not enabled and we need to enable it.

Enable the event Function

Method 1: Modify Dynamic Parameters

SET GLOBAL event_scheduler = ON;

This parameter takes effect immediately after it is changed, but mysql is restored after it is restarted, that is, it cannot be set to cross-restart.
Method 2: change the configuration file and restart
Add the following content in [mysqld] Of my. cnf and restart mysql.

event_scheduler=ON;

Once and for all, you must be able to modify Database configurations.

Method 3: Add "-event_scheduler = 1" to the startup command"

mysqld ... --event_scheduler=ON

Event syntax 1. Create an event

CREATE    [DEFINER = { user | 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 is executed.
On schedule schedule: defines the execution time and interval.
On completion [NOT] PRESERVE: defines whether an event is executed once or permanently. It is executed once by default, that is, not preserve.
ENABLE | disable on slave: defines whether to ENABLE or DISABLE an event after it is created, and whether to DISABLE it from the top. If the statement for creating an event ON the master node is automatically synchronized from the server, disable on slave is automatically added.
COMMENT 'comment': defines the comments of 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. Delete event syntax

DROP EVENT [IF EXISTS] event_name;

Event examples 1. Create an event

Example 1
Insert data to table test2.

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

Execution result

Example 2
Create an event to clear data in the test table after 10 minutes

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

Example 3
Create an event to clear the data of the test table at 14:42:00 on January 17 ,.

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 4
After five days, the daily scheduled task is enabled for 3 seconds to insert data to table test2. The task will be stopped after one month.

CREATE EVENT IF NOT EXISTS event_truncate_test2ON SCHEDULE EVERY 3 SECONDSTARTS CURRENT_TIMESTAMP + INTERVAL 5 dayENDS CURRENT_TIMESTAMP + INTERVAL  1 monthON COMPLETION PRESERVEDO INSERT INTO test2(department,time_v) VALUES('1',NOW());

Event modification example

Example 1
Temporarily close the event

alter event event_insert_t2 disable;

Other similar Creation events.

Example of deleting an event

DROP EVENT IF EXISTS event_insert_t2;

An event calls a stored procedure or function as a normal call.

Advantages and disadvantages of events and application scenarios Reference

Http://blog.163.com/duanpeng3@126/blog/static/8854373520105182123112/
Http://blog.chinaunix.net/uid-20639775-id-3323098.html