mysql event使用,用MySQL的Event设置定时任务执行sql语句 | 老疯子

想在每天某个特定时间段定时执行一条sql语句命令，来进行数据库的备份或者更新删除等，可以直接使用MySQL自带的定时任务Event事件来执行操作。需要注意的是Event事件是在MySQL 5.1版本中新增的，还有一点就是确保你有MySQL数据库的root管理权限，因为Event默认是关闭状态需要用root权限开启 下面会给出开启方法。

查看Event事件是否开启

1、查看MySQL是否开启event，执行下面的命令。

SHOW VARIABLESLIKE'event_scheduler';

如果显示OFF，说明是关闭状态需要开启。

2、 开启event，使用下面的命令 。

SETGLOBALevent_scheduler = 1;

执行完后再次查看event状态，ON为开启。

Event 事件语法说明

语法

CREATE

[DEFINER = { user|CURRENT_USER}]

EVENT

[IF NOTEXISTS]

event_name

ONSCHEDULE schedule

[ONCOMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE | DISABLE ONSLAVE]

[COMMENT 'string']

DO event_body;

schedule:

ATtimestamp[+ 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：指定可执行该定时器的MySQL账号，user的格式是’user_name’@’host_name’，CURRENT_USER或CURRENT_USER()，注意，单引号是需要在语句中输入的。如果不指定，默认是DEFINER = CURRENT_USER。

event_name：事件名称，最大64个字符，不区分大小写，MyEvent和myevent是一样的，命名规则和其他MySQL对象是一样的。

ON SCHEDULE schedule： 指定何时执行，下文详细说明。

[ON COMPLETION [NOT] PRESERVE]：可选，preserve是保持的意思，这里是说这个定时器第一次执行完成以后是否还需要保持，如果是NOT PRESERVE，该定时器只执行一次，完成后自动删除事件；没有NOT，该定时器会多次执行，可以理解为这个定时器是持久性的。默认是NOT PRESERVE。

[ENABLE | DISABLE | DISABLE ON SLAVE]：可选，是否启用该事件，ENABLE-启用，DISABLE-禁用，可使用alter event语句修改该状态。DISABLE ON SLAVE是指在主备复制的数据库服务器中，在备机上也创建该定时器，但是不执行。

COMMENT: 注释，必须用单引号括住。

DO event_body：事件要执行的SQL语句，可以是一个SQL，也可以是使用BEGIN和END的复合语句，和存储过程相同。

ON SCHEDULE 事件的执行时间

ON SCHEDULE指定事件何时执行，执行的频率和执行的时间段，有AT和EVERY两种形式。

AT(在指定的时间只执行一次)

AT语法AT timestamp，用于只执行一次事件。执行的时间由timestamp指定，timestamp必须包含完整的日期和时间，即年月日时分秒都要有。可以使用DATETIME或TIMESTAMP类型，或者可以转换成时间的值，例如“2018-01-21 00:00:00”。如果指定是时间是过去的时间，该事件不会执行，并会生成警告。

可以使用CURRENT_TIMESTAMP(当前时间的意思)指定执行时间，这样的话，事件创建成功会立即执行。

如果事件执行的时间是未来的某个时间点，可以使用+ INTERVAL interval指定具体时间。interval有数字(quantity) 和 时间单位(Unit of time)两部分组成，例如：2分10秒后执行，应写为 + INTERVAL ‘2:10’，可用的时间单位有很多，列表如下图：

AT小例子

1小时后执行该事件。创建名字为myevent的事件，ON SCHEDULE指定时间，DO要执行的sql语句

CREATE

EVENT myevent

ONSCHEDULE

SCHEDULE ATCURRENT_TIMESTAMP+ INTERVAL 1HOUR

DO

UPDATEmyschema.mytableSETmycol = mycol + 1;

Every(每间隔多久执行一次)

如果需要让事件定时执行，使用Every这种方式。注意Every后边没有+ INTERVAL，时间格式和单位和上面的相同。

Every小例子

EVERY 20 second每20秒执行一次

CREATE

EVENT myevent

ONSCHEDULE

EVERY 20 second

DO

UPDATEmyschema.mytableSETmycol = mycol + 1;

EVERY后面可以跟可选的STARTS和ENDS，指定事件开始和结束时间，在这个时间段内，时间定时执行。STARTS和ENDS可同时指定，或者只指定STARTS，或两者都不指定。

更多案例

EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK 一周以后开始，每隔三个月

EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL ‘6:15’ HOUR_MINUTE 六个小时15分钟以后，每隔两周

EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK 30分钟以后开始，4周后结束，每隔12个小时

例子：每天凌晨执行

use 指定名为WordPress的数据库，在每天凌晨将wp_vod数据表中的，vod_day字段值设为0

use WordPress;

CREATE

EVENT myevent

ONSCHEDULE

EVERY 1 DAYSTARTS '2019-01-01 00:00:00'

DO

updatewp_vodsetvod_day = 0;

删除Event事件

语句很简单 ，后面myevent为事件名

dropevent myevent;

MySQL服务器重启 断电会导致事件关闭

整个服务器重启、断电会导致event事件恢复成默认OFF关闭状态，想解决这个问题，则需要在mysql.ini文件中修改加入event_scheduler = ON;