mysql中的定时任务

mysql中的定时任务

查看mysql定时事件开启状态：

mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set

mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
1 row in set

mysql> 

event_scheduler的状态是关闭的，即：

mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set

设置为开启状态：

mysql> set global event_scheduler=1;
Query OK, 0 rows affected

mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
1 row in set

在navicat中事例

1.创建一个存储过程

create procedure count_post_minute()
BEGIN
    DECLARE cnt int DEFAULT 0;
    select count(*) into cnt from t_post;
    INSERT INTO t_event (count, scheduler_time) values(cnt, date_format(now(), '%Y-%m-%d %H:%i:%s'));
END

2.在navicat中新建一个事件，调用存储过程

3.设置定时执行时间

4.并在定义模块中设置为开启状态: ENABLE

5.可以看到t_event表中已经有数据了