Home>

It is often necessary to have some timing tasks executed on the mysql table.For example, statistics, migration, delete unnecessary data, and so on.The previous method was to use linux cron to run the script at regular intervals.But finding such extra dependencies is sometimes inconvenient,For example, when a single machine is deployed in multiple instances,You need to configure different cron tasks manually, and you need to configure corresponding users and permissions.When deploying a new environment, it is easy to miss cron tasks.

mysql provides an event scheduler, which is similar to crontab under linux, and can run tasks based on time scheduling.Run one or more times.

The complete event schduler creation statement is as follows:

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}

First, schedulerThe scheduling in mysql can be run only once,You can also repeat the run at a specified interval.Its definition is in the on schedule clause of the event definition. The clause has the following format:

on schedule
at timestamp [+ interval interval]…
| every interval
  [starts timestamp [+ interval interval]…]
  [ends timestamp [+ interval interval]…]

Among them, timestamp must include "year, month, day, hour, minute, and second". After participating in expression calculation,The result is of type datetime or timestamp.

The interval can be as follows:

<Number>{year | quarter | month | day | hour | minute |
      week | second | year_month | day_hour | day_minute |
      day_second | hour_minute | hour_second | minute_second}

The meaning is clear,Such as year;quarter;year_month year + month;minute_second minute + second.

supplement:

year | quarter | month | year_month are converted to month in the background, and all other intervals are converted to second

The time on schedule uses the time zone information time_zone in the session at the time of creation. This time zone defaults to the global time_zone of the server. It may also be manually updated later.These times will be converted to UTC time and stored in the mysql.event table.

Run onceat directly specify the time,Or use a time expression to calculate a determined point in time.

Example:

at "2006-02-10 23:59:00 ′ specify the exact runtime,The local time zone.

at current_timestamp + interval "1:15 ′ minute_second Specify to run after 1 minute and 15 seconds.

2. Run multiple times

every sets the time interval to run,You cannot specify [+ interval interval] here.

Specifying starts and ends is optional.

starts specifies the first time a repeat run is performed.Without specifying,Will run the first time the event is created,That is equivalent to starts current_timestamp!

ends tells mysql when to end the repeated runs.Without specifying,mysql will continue to run forever.

Example:

every 5 weekRuns for the first time when created.

every 3 day starts "2013-12-4 09:10:00 'Run every 3 days.

every 2 month starts current_timestamp + interval 10 minute ends "2014-12-31 23:59:59 '

Event

1.Enable event scheduler functionEvents are executed by a specific event scheduler thread,During the running process, you can view its current status information through show full processlist.Such as:

7384313 event_scheduler localhost [null] daemon 3 waiting on empty queue [null]

The event scheduler function is not enabled by default.The global parameter event_scheduler needs to be configured. This parameter can be set dynamically.Take effect immediately.

event_scheduler has the following three values:

off/0 off, default value. Without running the event scheduler thread, event scheduling cannot be performed.Set to on to enable it immediately.

on/1 is enabled.

disabled. Also does not run the event scheduler thread. This setting is only useful when the mysql service is started.When event_scheduler is on or off, event_scheduler cannot be set to disabled at runtime. If event-scheduler=disabled is configured at startup, the runtime cannot be set to on/off. In other words,You can set it to disabled when the mysql service starts, and then completely disable event_scheduler, which cannot be adjusted dynamically.

So, to enable event_scheduler, run time:

set global event_scheduler=on

To enable it with the mysql service,Add in /etc/my.cnf

[mysqld]
event-scheduler=on

2. Syntax for creating events

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}

Parameter detailed description:

definer:define the user who checks permissions when the event is executed.

on schedule schedule:Define the time and interval of execution.

on completion [not] preserve:defines 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 enabled or disabled after the event is created,Well closed from above.If it is a slave that automatically synchronizes the statement that created the event on the master,Disable on slave will be added automatically.

comment "comment":Defines a comment for the event.

3. Change the syntax of the 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]

4. Delete event syntax

drop event [if exists] event_name

5.do clauseImplement the specific logic of the event in the do clause,Almost all mysql statements that can be run in stored procedures can be used in events.

1) Simple sql example:

create event e_hourly
  on schedule
   every 1 hour
  comment "clears out sessions table each hour."
  do
   delete from site_activity.sessions;

2) Complex SQL example:

delimiter |
create event e
  on schedule
   every 5 second
  do
   begin
    declare v integer;
    declare continue handler for sqlexception begin end;
    set v=0;
    while v<5 do
     insert into t1 values ​​(0);
     update t2 set s1=s1 + 1;
     set v=v + 1;
    end while;
  end |
delimiter;

3) Limitation of sql in do clause

Basically, you can use any SQL statement allowed in stored routines, and there are some restrictions in stored procedures.There are some additional restrictions on events.

The following statements in the stored routine are not allowed:

lock tables/unlock tables load data and load table

Support dynamic SQL (prepare, execute, deaalocate prepare)! However, some statements of prepare itself are not allowed to be executed.

insert delayed will not take effect

Event restrictions:

If the do clause contains an alter event clause, although it can be created,But there will be errors during runtime.

Do not use select or show in the do clause. This is just a query.Because its output cannot be obtained from the outside.You can use select… into to save the query results.

5. View eventThere are ways to view event information:

mysql.event
information_schema.events
show events
show create event

Third, other points of event schedulemysql saves the sql_mode at the time of event creation as its sql_mode at runtime;

If the task is not processed within a scheduling interval,New schedules will still be generated,This can happen when multiple tasks are running at the same time.If i want to avoid multiple tasks,You can use get_lock () function or row lock, table lock.

Fourth, mysql event combat

test environmentCreate a test table for testing:

create table `test` (
 `id` int (11) not null auto_increment, `t1` datetime default null, `id2` int (11) not null default" 0 ", primary key (`id`)
) engine=innodb auto_increment=106 default charset=utf8

Actual combat 1øCreate an event to insert a piece of data into the test table every 3 seconds,code show as below:

create event if not exists test on schedule every 3 second
on completion preserve
do insert into test (id, t1) values ​​("", now ());

ØCreate an event to clear test table data after 10 minutes

create event if not exists test
on schedule
at current_timestamp + interval 1 minute
do truncate table test.aaa;

Øcreate an event to clear the test table data at 2012-08-23 00:00:00,code show as below:

create event if not exists test
on schedule
at timestamp "2012-08-23 00:00:00"
do truncate table test;

ØCreate one from 21:45 on August 22, 2012 to 10 minutes later,Run the event of inserting a data into the test table every 3 seconds,code show as below:

create event if not exists test on schedule every 3 second
starts "2012-08-22 21:49:00"
ends "2012-08-22 21:49:00" + interval 10 minute
on completion preserve
do insert into test (id, t1) values ​​("", now ());

Actual combat 2The usual application scenario is to periodically call stored procedures through events,Here is a simple example:

Create a stored procedure that allows the id2 field of the test table to add radix 2 to each row.The stored procedure code is as follows:

drop procedure if exists test_add;
delimiter //
create procedure test_add ()
begin
declare 1_id int default 1;
declare 1_id2 int default 0;
declare error_status int default 0;
declare datas cursor for select id from test;
declare continue handler for not found set error_status=1;
open datas;
fetch datas into 1_id;
repeat
set 1_id2=1_id2 + 2;
update test set id2=1_id2 where id=1_id;
fetch datas into 1_id;
until error_status
end repeat;
close datas;
end
//

Event settings will start at 2012-08-22 00:00:00,Call the stored procedure every 1After 40 days,code show as below:

create event test on schedule every 1 day
starts "2012-08-22 00:00:00"
ends "2012-08-22 00:00:00" + interval 40 day
on completion preserve do
call test_add ();
  • Previous C # method based on delegation to achieve operations between multiple threads
  • Next Example of factoring and finding the least common multiple using Java code