一、查看定时任务是否开启
-- 查看定时任务是否开启
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 //
