MYSQL 定时任务调用存储过程

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/z317026263/article/details/50588359
</pre>创建存储过程:<pre name="code" class="sql">drop procedure IF EXISTS case_inter_sum;
create procedure case_inter_sum()
begin
  DECLARE casecnt INT DEFAULT 0;
  DECLARE intercnt INT DEFAULT 0;
  DECLARE datetime DATETIME DEFAULT NOW();
  set @casecnt = (SELECT COUNT(1)  FROM CASE_INFO);
  set @intercnt =(SELECT COUNT(1)  FROM INTERFACE_INFO);
  INSERT INTO CASE_INTER_SUM(sum_num,sum_state,sum_date) VALUES (@casecnt,'case',datetime);
  INSERT INTO CASE_INTER_SUM(sum_num,sum_state,sum_date) VALUES (@intercnt,'interface',datetime);
 end

在mysql命令行建EVENT:

   //每个月1号1点执行

drop EVENT GETINNERSUM;
CREATE event if not exists GETINNERSUM  
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 ENABLE  
DO  
   CALL case_inter_sum();


在命令行输入: 

SHOW VARIABLES LIKE '%sche%'; 查看是否开启定时器。

SET GLOBAL event_scheduler = ON;开启定时器。

show EVENTS 查看事件。

ALTER EVENT event_test ON    

COMPLETION PRESERVE ENABLE;    //开启事件

ALTER EVENT event_test ON    

COMPLETION PRESERVE DISABLE;  //关闭事件。


阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页