mysql保持开启事件调度器_MySql-使用事件调度器(event_scheduler)

写在前面：

MySql版本要高于5.1

0. 开启服务

SHOW VARIABLES LIKE 'event_scheduler';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| event_scheduler | OFF |

+-----------------+-------+

1 row in set

默认没有开启

使用 SET GLOBAL event_scheduler = 1;

或者SHOW VARIABLES LIKE 'event_scheduler';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| event_scheduler | ON |

+-----------------+-------+

1 row in set

1. 实际需求

系统中有一个模块需要从多个视图中根据用户名取数据，加载到首页，因为刷新首页比较耗时，所以新建一张绩效表，专门存这些数据，而即时更新这张表的值，就交给了事件调度器来做了。

2. SQL语法(官网)

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}

[ON COMPLETION]：执行完之后是否保留当前event

[ENABLE | DISABLE | DISABLE ON SLAVE]：创建后启用还是不启用

3. 示例

create event e_update_test

on schedule every 10 second

starts current_timestamp

on completion preserve enable

do

update test_table set num = num + 1;

创建一个事件调度器，名字为e_update_test，从创建完后每10秒执行一次，并且执行完不删除，执行内容为 更新test_table表的num字段，每次更新都加一

而用Navicat for MySql创建，图形化界面更直观也更简单一些：

内容定义：

时间计划设置：