Mysql创建的定时器去处理存储过程

1.定时器sql

 

DROP EVENT IF EXISTS update_order_status;
CREATE EVENT IF NOT EXISTS  update_order_status
ON SCHEDULE EVERY 1 MINUTE
ON COMPLETION PRESERVE
DO  
CALL update_order_status(15);

2.mysql存储过程

DROP procedure IF EXISTS update_order_status;
create procedure update_order_status(IN after_days int)
BEGIN
declare diff_days varchar(50); 
declare p_entity_id int; 
declare stop_f int  DEFAULT 2; 
declare cur cursor for SELECT  ENTITY_ID,DATEDIFF (now(),updated_at)  as diff_days from sales_flat_order where status = 'complete' ; 

declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop_f=1; 

open cur; 


fetch cur into p_entity_id,diff_days; 

while stop_f <> 1 do 

if diff_days>after_days then 
update sales_flat_order set  state='confirm_goods',status='confirm_goods' where entity_id=p_entity_id;
update sales_flat_order_grid set  status='confirm_goods' where entity_id=p_entity_id; 
update sales_flat_order_status_history set  status='confirm_goods' where parent_id=p_entity_id; 
end if; 

fetch cur into p_entity_id,diff_days; 
end while; 
close cur;  
end;