Detailed explanation of MySQL's new functions (event scheduler) examples

Overview

EventThe scheduler is a newly added function after MySQL5.1, you can press the database A custom time period triggers a certain operation, which can be understood as a time trigger , is similar to the task scheduler crontab under the <a href="http://www.php.cn/wiki/1497.html" target="_blank">linux</a> system, or similar to the plan under the window Task. It is worth mentioning that MySQL's event scheduler can execute one task per second, while the operating system's scheduled tasks (such as CRON under Linux or task planning under Windows) can only execute one task per minute.

Check whether the event function is turned on

When using the event function, first make sure that your mysql version is 5.1 or above, and then check your mysql server Whether the event is enabled.
To check whether the event is enabled, use the following command to check:

#方式一
SHOW VARIABLES LIKE 'event_scheduler';
#方式二
SELECT @@event_scheduler;
#方式三
SHOW PROCESSLIST;

#查看事件状态
SHOW EVENTS;

If you see event_scheduler is on or event_scheduler information is displayed in PROCESSLIST, the event has been enabled. If it is displayed as off or the event_scheduler information cannot be seen in PROCESSLIST, it means that the event is not enabled and we need to enable it.

Enable event function

Method 1. Modify through dynamic parameters

SET GLOBAL event_scheduler = ON;

After changing this parameter, it will take effect immediately. But restarting mysql restored it, that is, the settings cannot span restarts.
Method 2. Change the configuration fileand then restart
Add the following content to the [mysqld] section in my.cnf, and then restart mysql.

event_scheduler=ON;

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

Method 3: Add “–event_scheduler=1” directly to the startup command

mysqld ... --event_scheduler=ON

Event syntax

1. Create 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: Define the user who checks permissions when the event is executed.
ON SCHEDULE schedule: Define the execution time and interval.
ON COMPLETION [NOT] PRESERVE: Define whether the event is executed once or permanently. The default is one execution, that is, NOT PRESERVE.
ENABLE | DISABLE | DISABLE ON SLAVE: Define whether the event is turned on or off after it is created, and whether it is turned off from above. If the slave server automatically synchronizes the statement of the creation event on the master, DISABLE ON SLAVE will be automatically added.
COMMENT 'comment': Comment that defines 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. Syntax of delete event

DROP EVENT [IF EXISTS] event_name;

Event usage example

1. Create an event

Example 1
Send to table test2 every 3 secondsInsert data

create event event_insert_t2
on schedule every 3 second
on completion preserve
do insert into test2(department,time_v) value('1',now());

Execution result

##Event execution result

Example 2Create an event that clears the test table data after 10 minutes

CREATE EVENT IF NOT EXISTS event_truncate_test2
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
DO TRUNCATE TABLE test2;

Example 3Create an event at 2015-04-17 14:42:00 Event of clearing test table data

DROP EVENT IF EXISTS event_truncate_test2;
CREATE EVENT event_truncate_test2
ON SCHEDULE
AT TIMESTAMP '2015-04-17 14:42:00'
DO TRUNCATE TABLE test2;

Example 4Start inserting data into table test2 every 3 seconds after 5 days, and stop execution after one month

CREATE EVENT IF NOT EXISTS event_truncate_test2
ON SCHEDULE EVERY 3 SECOND
STARTS CURRENT_TIMESTAMP + INTERVAL 5 day
ENDS CURRENT_TIMESTAMP + INTERVAL  1 month
ON COMPLETION PRESERVE
DO INSERT INTO test2(department,time_v) VALUES('1',NOW());

Modify event example

Example 1Temporarily close event

alter event event_insert_t2 disable;

Other similar creation events.

Delete event example

DROP EVENT IF EXISTS event_insert_t2;

Event call

Stored procedure or function is like a normal call.

Advantages, disadvantages and application scenarios of events

1. Advantages

2. Scheduled tasks are managed uniformly by DBA , avoid deployment at the operating system layer.

3. Reduce the risk of misoperation by system administrators.

4. is conducive to subsequent management and maintenance.

5. Disadvantages

6. Deploy and enable the scheduler carefully on busy and performance-demanding databases.

7. Overly complex processing is more suitable to be implemented using programs.

8. Opening and closing events requires super user privileges.

9. Application Scenario

   Suitable for regular collection of statistical information, regular clearing of historical data, regular database checks, etc.
   

【Related recommendations】

1.

Free mysql online video tutorial

2.

MySQL latest manual tutorial

3. Boolean Education Yan Shiba mysql introductory video tutorial

The above is the detailed content of Detailed explanation of MySQL's new functions (event scheduler) examples. For more information, please follow other related articles on the PHP Chinese website!