检测MySQL事件是否开启
show variables like 'event_scheduler';
上图显示未开启,开启事件方法
set global event_scheduler = on;
举例事件代码
//事件计划在重启服务器之后会停止,要让其自动启动 BEGIN DECLARE time1 int DEFAULT unix_timestamp() - 1800; DECLARE yes int default 1; declare l_orderid int default 0; declare l_productid int default 0; declare l_amount int default 0; declare success int default 0; DECLARE nimei CURSOR FOR SELECT id,product_id,shuliang FROM tb_order where addtime<time1 and status_pay<>1 and isdel=0 limit 10; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET yes=0; OPEN nimei; FETCH nimei INTO l_orderid, l_productid, l_amount; while yes <> 0 DO /*删除订单*/ update tb_order set isdel=1 where id=l_orderid; /*删除卡号*/ update tb_card set isdel = 1 where order_id=l_orderid; /*还原库存*/ update tb_product set stock=stock+l_amount where id=l_productid; /*写记录*/ /*insert into tb_log(order_id,dotime,info) values(l_orderid,unix_timestamp(),'订单超时未支付');*/ set success=success+1; /*下一行记录*/ FETCH nimei INTO l_orderid, l_productid, l_amount; END WHILE; CLOSE nimei; select success; END