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;