Mysql中event事件的入门
主要涉及的知识点:mysql的存储过程、mysql的event事件调度。
参考资料:
直接示例:
假如要实现这样一个场景,购买订单生成后,三十分钟未支付,自动更新订单状态,并返还商品库存。
存储过程示例:
delimiter //
DROP PROCEDURE IF EXISTS `updateOrderStatus` //
CREATE PROCEDUREupdateOrderStatus()BEGIN
DECLARE order_id int DEFAULT 0;DECLARE goods_id int DEFAULT 0;DECLARE done int DEFAULT 0;DECLARE overtime_order CURSOR FOR select id,shop_id from `order` where `status`='等待支付' and `order_time`<=(unix_timestamp(now())-1800);DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;OPENovertime_order;
read_loop:LOOPFETCH overtime_order intoorder_id,goods_id;if done = 1 THENleave read_loop;END IF;update `order` set `status` = '未支付',order_end_time=now() where `id`=order_id;update `shop` set `num` = `num`+1 where `id`=goods_id;ENDLOOP;CLOSEovertime_order;END //delimiter ;
event事件调度示例:
drop event if existsevent_update_order;CREATEevent event_update_orderON SCHEDULE EVERY 1MINUTE
DO CALL updateOrderStatus();
知识点:
(1)修改sql默认的以【分号;】结束为【//】: delimiter //
(2) 创建存储过程前先查看下是否已存在:show create procedure updateOrderStatus;
或存在先删除:DROP PROCEDURE IF EXISTS `updateOrderStatus`;
(3)创建event事件前先查看下是否已存在:show create event event_update_order;
或存在先删除:drop event if exists event_update_order;
(4)mysql的event事件调度程序默认是关闭的。
查看event是否开启:
mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.01 sec)
开启:
SET GLOBAL event_scheduler=on;
(5)存储过程中定义的变量不能和数据表中的字段名重名,不然取不到值。
(6)查看调度器线程:show processlist;
(7)在存储过程中这样定义变量:DECLARE order_id int DEFAULT 0;
(8)当存在多个数据需要循环时,可以用到游标,
//创建游标,并存储数据
declare user_data CURSOR for
select id,name from user where id<10;
//创建变量,标识游标是否结束
declare done INT DEFAULT 0;
//当游标中的内容执行完后将done设置为1,说明循环游标结束
declare CONTINUE HANDLER FOR NOT FOUND SET done=1;
//打开游标
open user_data;
//执行循环
read_loop:LOOP
//判断是否结束循环
IF done=1 THEN
LEAVE read_loop;//跳出循环
END IF;
//取游标中的值
FETCH user_data into user_id,user_name;
//执行增删改查操作
insert ...;
delete ...;
update ...;
select ...;
END LOOP read_loop;
//释放游标
CLOSE user_data;