mysql游标使用

近些碰到一个业务需要处理结果集,因此需要使用游标.用于统计策略的流量值.直接上栗子说明游标用法.

先看strategy表中数据

一共10条.

通过游标遍历统计流量

CREATE DEFINER=`root`@`localhost` PROCEDURE `sum_interface_traffic`() BEGIN declare sum_in_traffic float; declare sum_out_traffic float; declare strategy_id int; declare app_ids varchar(300); declare done INT default 0; #涉及到主键,必须用别名,否则fetch不到值 declare cur cursor for select s.id, s.app_ids from strategy s; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; /** 要是需要频繁的调用该存储过程,可以将 drop temporary table if exists sum_strategy_interface_traffic;注释 改为truncate table sum_strategy_interface_traffic;避免临时表的频繁创建 **/ drop temporary table if exists sum_strategy_interface_traffic; create temporary table sum_strategy_interface_traffic( strategy_id int unsigned, sum_in_traffic float unsigned, sum_out_traffic float unsigned )default charset utf8 collate utf8_general_ci engine InnoDB; open cur;#打开游标 re:repeat FETCH cur INTO strategy_id, app_ids; ##结束循环 if done then leave re; end if; call split(app_ids); select sum(sit.sum_in_traffic) from sum_interface_traffic_by_app_id sit where sit.app_id in (select * from tmp_strs) into sum_in_traffic; select sum(sit.sum_out_traffic) from sum_interface_traffic_by_app_id sit where sit.app_id in (select * from tmp_strs) into sum_out_traffic; insert into sum_strategy_interface_traffic values(strategy_id,sum_in_traffic,sum_out_traffic); until done end repeat; close cur;#关闭游标 END 运行结果;

PS:在开发种会碰到遍历结果集提前退出的场景,比如

有些存储过程要是涉及select XXX into .. (其中XXX涉及到数据库的查询,哪怕是调用其他的存储过程或者函数),要是碰到查询为空的情况会导致循环退出

在上面例子上稍稍修改说明:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sum_interface_traffic`() BEGIN declare sum_in_traffic float; declare sum_out_traffic float; declare strategy_id int; declare app_ids varchar(300); declare done INT default 0; declare test varchar(15); #涉及到主键,必须用别名,否则fetch不到值 declare cur cursor for select s.id, s.app_ids from strategy s; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; /** 要是需要频繁的调用该存储过程,可以将 drop temporary table if exists sum_strategy_interface_traffic;注释 改为truncate table sum_strategy_interface_traffic;避免临时表的频繁创建 **/ drop temporary table if exists sum_strategy_interface_traffic; create temporary table sum_strategy_interface_traffic( strategy_id int unsigned, sum_in_traffic float unsigned, sum_out_traffic float unsigned )default charset utf8 collate utf8_general_ci engine InnoDB; open cur;#打开游标 re:repeat FETCH cur INTO strategy_id, app_ids; ##结束循环 if done then leave re; end if; call split(app_ids); select sum(sit.sum_in_traffic) from sum_interface_traffic_by_app_id sit where sit.app_id in (select * from tmp_strs) into sum_in_traffic; select sum(sit.sum_out_traffic) from sum_interface_traffic_by_app_id sit where sit.app_id in (select * from tmp_strs) into sum_out_traffic; insert into sum_strategy_interface_traffic values(strategy_id,sum_in_traffic,sum_out_traffic); /***仅仅演示游标提前退出 当查询到第7条数据的时候,app_ids字符串为null,所以临时表tmp_strs是为空 select * from tmp_strs limit 1 into test;导致循环提前退出 **/ select * from tmp_strs limit 1 into test; until done end repeat; close cur;#关闭游标 END看效果;

只有7条数据.

解决办法,我在一篇技术博客看到有人提供了三种解决方案.我这只提供其中一种简单明了的:

在select * from tmp_strs limit 1 into test;后面添加处理逻辑

部分代码如下

/***仅仅演示游标提前退出 当查询到第7条数据的时候,app_ids字符串为null,所以临时表tmp_strs是为空 select * from tmp_strs limit 1 into test;导致循环提前退出 **/ select * from tmp_strs limit 1 into test; ##防止循环提前结束 set done = 0;