Mysql存储过程--大于十分钟执行

佚名 5年前 (2019-09-16) Mysql 861人围观抢沙发百度已收录

--存储过程
DELIMITER |
DROP PROCEDURE IF EXISTS update_tatus |
CREATE PROCEDURE update_status()
BEGIN
    IF exists (select id from status_his where status = 2 and createTime <= now()-interval 10 minute) THEN
            update status_his set status = 4
            where id in (select d.id from (SELECT d.id from status_his d where status = 2 and createTime <= now()-interval 10 minute ) d)and status = 2;
    END IF;
END 
 |DELIMITER;

--定时器  
    DELIMITER //
    CREATE EVENT  update_status
    ON SCHEDULE EVERY 300 second  do
    begin
    call update_status();
    end //
    DELIMITER;

--启动定时器
    ALTER EVENT update_status ON  
    COMPLETION PRESERVE ENABLE;

--查询是否开启event
show variables like '%event_scheduler%';

--开启event
set global event_scheduler =1;


--删除定时器
DROP event update_status