MySQL定时任务

一、查看定时任务是否开启

-- 查看定时任务是否开启
select @@event_scheduler;
show variables like 'event%';

二、开启关闭定时任务

--开启定时任务
set global event_scheduler = 1;
set global event_scheduler = on;

--关闭定时任务
set global event_scheduler = 0;
set global event_scheduler = off;

三、建立定时任务，实例

--建立定时任务
delimiter //
create event insert_job
on schedule every 10 second STARTS TIMESTAMP '2018-03-26 01:00:00'  --从2018-03-27凌晨1点开始每10秒执行一次
on completion preserve  /*-- 当本次event到期了,event会被disable,但是该event还是会存在--*/
do
begin
 call insertdata('laosong001',20,3);
end //

delimiter ;

四、修改事件

-- 开启事件
alter EVENT insert_job ON COMPLETION preserve ENABLE;
-- 关闭事件
ALTER EVENT insert_job ON completion PRESERVE DISABLE;

-- 删除事件
DROP EVENT if EXISTS insert_job; 

注： 其中的存储过程为

delimiter //
create procedure  insertdata(in vname varchar(20),in vage int, in numberdata int) 
begin
declare vnumber  int;
set vnumber = 1;
while vnumber <= numberdata do
insert into test_001(tname,age) values(vname,vage);
set vnumber = vnumber+1;
set vage = vage + 1;
end while;
end //

delimiter ;

执行定时任务后，数据表中得到数据如下：

执行定时任务后结果.png

定时任务实例-定时更新字段

select HOUR(timediff(now(),createtime)) from test_event where id = 1;


select MINUTE(timediff(now(),createtime)) from test_event where id = 1;


/*----创建的定时任务----*/
delimiter //
create event judgetime
on schedule every 5 MINUTE 
on COMPLETION PRESERVE
do
begin
    call updatestatus();
end //

/*----引用的存储过程----*/
create procedure updatestatus( )
begin
    declare vdiff int;
    declare vids int;
    declare num int;
      select count(id) into vids from test_event;
    set num = 1;
    while num <= vids do
    select MINUTE(TIMEDIFF(now(),createtime)) into vdiff from test_event where id = num;
        
     if  vdiff >= 10  THEN
        update test_event set orderstatus ='true' where id = num;
     end if;
     set num = num + 1;
     end while;
end //