MYSQL定时器

查看当前是否已开启事件计划(调度器)有3种方法:

1)  SHOW VARIABLES LIKE ‘event_scheduler’;
2)  SELECT @@event_scheduler;

3)  SHOW PROCESSLIST;

开启事件计划(调度器)开关有4种方法:
1)  SET GLOBAL event_scheduler = 1;
2)  SET @@global.event_scheduler = 1;
3)  SET GLOBAL event_scheduler = ON;
4)  SET @@global.event_scheduler = ON;

键值1或者ON表示开启;0或者OFF表示关闭;

创建事件
create event if not exists e_test
on schedule every 30 second starts now()
on completion preserve
do
UPDATE `htx_emsd` SET type=3 WHERE sstime<NOW() and type=1
没30秒获取当前时间与字段sstime对比

每天凌晨执行

CREATE EVENT `course_listener`
ON SCHEDULE EVERY 1 DAY STARTS '2015-11-13 00:00:00'  这个时间是从什么时间开始
ON COMPLETION PRESERVE
ENABLE
DO
UPDATE `htx_emsd` SET type=3 WHERE sstime<NOW() and type=1

每个月的一号凌晨1点执行
create event if not exists e_test2
on schedule every 1 month starts DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
on completion preserve
do
UPDATE `htx_emsd` SET type=3 WHERE sstime<NOW() and type=1

也可以对事件任务进行关闭:
alter event e_test ON
COMPLETION PRESERVE DISABLE;

对已经关闭的事件任务进行开启:
alter event e_test ON
COMPLETION PRESERVE ENABLE;

 

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注