mysql游标使用

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

先看strategy表中数据

一共10条.

通过游标遍历统计流量

CREATE DEFINER=`root`@`localhost` PROCEDURE `sum_interface_traffic`()BEGINdeclare 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`()BEGINdeclare 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;