MySQL定时任务(event事件)(2)

查看调度器线程

mysql> show processlist; +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | | 3 | event_scheduler | localhost | NULL | Daemon | 6 | Waiting on empty queue | NULL| +----+-----------------+-----------+------+---------+------+------------------------+------------------+

3.2.3 关闭事件调度器
通过命令行
可通过如下任何一个命令行

SET GLOBAL event_scheduler = OFF; SET @@global.event_scheduler = OFF; SET GLOBAL event_scheduler = 0; SET @@global.event_scheduler = 0;

通过配置文件my.cnf
在[mysqld]下增加

event_scheduler = 0 #或者OFF，DISABLED

查看调度器线程

mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-----------+------+---------+------+-------+------------------+

3.3 例如：创建一个表记录每次事件调度的名字和事件戳
3.3.1 创建测试表

mysql> drop table if exists events_list; mysql> create table events_list(event_name varchar(20) not null, event_started timestamp not null);

3.3.2 创建事件1（立即启动事件）

create event event_now on schedule at now() do insert into events_list values('event_now', now());

查看事件执行结果

mysql> select * from events_list; +------------+---------------------+ | event_name | event_started | +------------+---------------------+ | event_now | 2014-07-01 04:06:40 | +------------+---------------------+

3.3.3 创建事件2（每分钟启动事件）

create event test.event_minute on schedule every 1 minute do insert into events_list values('event_now', now());

查看事件执行结果

mysql> select * from events_list; +------------+---------------------+ | event_name | event_started | +------------+---------------------+ | event_now | 2014-07-01 04:26:53 | | event_now | 2014-07-01 04:27:53 | | event_now | 2014-07-01 04:28:53 | +------------+---------------------+

3.3.3 创建事件3（每秒钟启动事件）

CREATE event event_now ON SCHEDULE EVERY 1 SECOND DO INSERT INTO event_test VALUES(1);

3.3.4 创建事件4（每秒钟调用存储过程）

CREATE DEFINER=`root`@`localhost` EVENT `eventUpdateStatus` ON SCHEDULE EVERY 1 SECOND STARTS '2017-11-21 00:12:44' ON COMPLETION PRESERVE ENABLE DO call updateStatus()