MySQL--定时器

1、创建存储过程

create procedure batchAuditShop()
  BEGIN
   //    具体业务
    update biz_shop_info set audit_status = 1;

  END;

 

2、创建定时任务

//    每一秒执行一次
create event if not exists eventJob 
  on schedule every 1 second 
  on completion PRESERVE
  do call batchAuditShop();

//    2018-05-29 20:10:00启动定时器，每隔12小时执行一次
create event2 if not exists eventJob 
  on schedule every 12 hour starts timestamp '2018-05-29 20:10:00' 
  on completion PRESERVE
  do call batchAuditShop();

 

3、启动定时器

SET GLOBAL event_scheduler = 1;

 

4、停止定时器

SET GLOBAL event_scheduler = 0;

 

5、开启事件

ALTER EVENT eventJob ON  COMPLETION PRESERVE ENABLE;

 

6、关闭事件

ALTER EVENT eventJob ON  COMPLETION PRESERVE DISABLE;

 

7、查看定时器状态

SHOW VARIABLES LIKE '%sche%';

 

8、设置定时器开机自启动

找到etc/my.cnf 打开 在[mysqld]底下添加 event_scheduler = 1;

 

9、删除存储过程

drop procedure batchAuditShop

 

10、删除任务

drop event eventJob