mysql创建定时执行存储过程任务实现订单定时关闭

CREATE PROCEDURE `tableName`.`procedureName`()

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT '将3天前的未支付订单改为交易关闭状态\n将7天前的已支付订单改为交易成功状态'

begin

update order_info set STATE = 7 where state = 0 and TO_DAYS(now()) - TO_DAYS(ORDERTIME) > 2;

update order_info set STATE = 8 where state BETWEEN 1 and 4 and TO_DAYS(now()) - TO_DAYS(ORDERTIME) > 6;

end;

#创建定时任务 每天执行一次

CREATE EVENT `tableName`.`startUpdateOrderState`

ON SCHEDULE EVERY 1 DAY

STARTS '2016-02-13 00:00:00'

ON COMPLETION NOT PRESERVE

ENABLE

DO

call `procedureName`

set GLOBAL event_scheduler = 1;   #开启定时器

select * from mysql.event;查看事件运行状态

ALTER EVENT procedureName ENABLE;开启事件

ALTER EVENT procedureName DISABLE;关闭事件