MySQL存储过程中游标（Cursor）的使用技巧与实例

MySQL游标的核心操作步骤

在MySQL的存储程序中，游标（Cursor）主要用于逐行处理 SELECT 查询返回的结果集。虽然对于大批量数据，通常更推荐使用 Set-Based 集合操作以提高性能，但在处理复杂的逐行业务逻辑（如记录日志、条件分支调用其他存储过程等）时，游标是必要的手段。

使用游标的标准顺序遵循以下五个步骤：

1. 声明游标（DECLARE CURSOR）：定义游标名称及其对应的 SELECT 语句。
2. 声明变量：用于接收 FETCH 操作获取的每一行字段值，通常需要声明一个结束标志变量（如 done INT DEFAULT 0）。
3. 声明异常处理器（HANDLER）：当无法从游标获取数据（即到达结果集末尾）时，触发 CONTINUE 事件并将 done 标记为 1，以便退出循环。
4. 打开游标（OPEN）：激活游标并准备读取数据。
5. 循环读取（FETCH & CLOSE）：在 WHILE 或 REPEAT 循环中不断 FETCH 数据至变量，处理完毕后关闭游标。

实例一：批量插入商户路由关联数据

此场景用于根据商户表中的基础信息，自动生成 MAC 地址并插入到配置表中。注意：UNTIL done END REPEAT 是标准的退出判断写法，原草稿中的 UNTIL 0 会导致死循环。

DELIMITER $$

USE `mmm_mac`$$

DROP PROCEDURE IF EXISTS `批量插入商户路由关联数据`$$

CREATE DEFINER=`root`@`%` PROCEDURE `批量插入商户路由关联数据`()
BEGIN
  -- 1. 声明接收游标数据的变量
  DECLARE v_partner_no VARCHAR(32);
  DECLARE v_partner_id INT(11);
  DECLARE v_sc_pid INT(11);
  DECLARE v_mac_no VARCHAR(32);
  DECLARE v_mac_addr VARCHAR(32);
  
  DECLARE n_mac_no BIGINT;
  DECLARE n_mac_addr BIGINT;
  DECLARE n_mac_addr_str VARCHAR(32);
  
  -- 声明用于判断是否取完数据的结束标志
  DECLARE done INT DEFAULT 0;
  
  -- 2. 声明游标
  DECLARE cur_partnerlist CURSOR FOR
    SELECT comp_id, partner_no, sc_pid FROM mmm_partner.anl_partner; 
    
  -- 3. 设置异常处理器：当 fetch 失败时，将 done 设为 1
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  
  -- 初始化自增计数器
  SET n_mac_no = 100000000;
  SET n_mac_addr = 1000000000;
  
  -- 4. 打开游标
  OPEN cur_partnerlist;
  
  read_loop: REPEAT
    FETCH cur_partnerlist INTO v_partner_id, v_partner_no, v_sc_pid;
    
    IF NOT done THEN
      -- 构造 MAC 号
      SET v_mac_no = CONCAT('MAC', v_sc_pid, LPAD(n_mac_no, 9, '0'));
      
      -- 构造 MAC 地址字符串 (例如 C8:87:18:AB:79:66)
      SET n_mac_addr_str = CONCAT(
        SUBSTR(n_mac_addr,1,2), ':',
        SUBSTR(n_mac_addr,3,2), ':',
        SUBSTR(n_mac_addr,5,2), ':',
        SUBSTR(n_mac_addr,7,2), ':',
        SUBSTR(n_mac_addr,9,2)
      );
      SET v_mac_addr = CONCAT('CC:', n_mac_addr_str);
      
      -- 递增计数器
      SET n_mac_no = n_mac_no + 1;
      SET n_mac_addr = n_mac_addr + 1;
      
      -- 执行插入操作
      INSERT INTO t_machine_sc_config(
        mac_no, partner_no, partner_id, sc_pid, mac_addr, comp_id, is_lock
      ) VALUES(
        v_mac_no, v_partner_no, v_partner_id, v_sc_pid, v_mac_addr, 1, 1
      );
    END IF;
    
  UNTIL done END REPEAT;
  
  -- 5. 关闭游标
  CLOSE cur_partnerlist;
END$$

DELIMITER ;

实例二：更新空白的商户编号

此示例展示了如何使用游标遍历 anl_partner 表，查找那些 partner_no 为空（或经过 Trim 处理后为空）的记录，并为其生成唯一的编号进行更新。

DELIMITER $$

USE `mmm_partner`$$

DROP PROCEDURE IF EXISTS `更新商户表`$$

CREATE DEFINER=`root`@`%` PROCEDURE `更新商户表`()
BEGIN
  DECLARE v_partner_no VARCHAR(32);
  DECLARE v_partner_id VARCHAR(32); -- 建议类型保持一致，通常为 INT 或 CHAR
  DECLARE n BIGINT;
  DECLARE prefix_no VARCHAR(32);
  DECLARE done INT DEFAULT 0;
  
  -- 声明游标：筛选出需要更新的商户
  DECLARE partnerid_list CURSOR FOR
    SELECT comp_id FROM 100msh_partner.anl_partner WHERE TRIM(partner_no) = ''; 
  
  -- 异常处理器
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  
  SET prefix_no = '2015415parno';
  SET n = 10000000;
  
  OPEN partnerid_list;
  
  update_loop: REPEAT
    FETCH partnerid_list INTO v_partner_id;
    
    IF NOT done THEN
      SET v_partner_no = CONCAT(prefix_no, n);
      
      -- 更新该商户的编号
      UPDATE mmm_partner.anl_partner 
      SET partner_no = v_partner_no 
      WHERE comp_id = v_partner_id;
      
      SET n = n + 1;
    END IF;
    
  UNTIL done END REPEAT;
  
  CLOSE partnerid_list;
END$$

DELIMITER ;

注意事项与排错

• 变量作用域：确保变量声明在 BEGIN/END 块的起始部分，且必须在游标和 HANDLER 之前声明。
• 死循环风险：如果在循环内部没有正确捕获 done=1 导致未执行 BREAK 或未依赖 UNTIL done 退出，游标处理会陷入死循环。务必使用 WHILE NOT done DO 配合 BREAK，或者如上文所示使用 REPEAT ... UNTIL done 结构。
• 性能考量：游标在 MySQL 中逐行执行，效率远低于一次性 UPDATE ... JOIN 操作。如果仅是为了补全 ID 或修改某列，优先尝试集合操作（Set-based）。只有在必须对每一行进行复杂计算或并发控制时才使用游标。

总结

掌握游标的关键在于熟练掌握 DECLARE、OPEN、FETCH 和 CLOSE 这四个状态机步骤，并正确处理 NOT FOUND 异常以安全退出循环。上述代码可直接复制并在测试环境中验证。

以上就是MySQL存储过程中游标用法的详细内容，更多关于MySQL游标与存储过程的资料请关注本站其它相关文章！