MySql中游标使用总是多循环一次的解决方法

CREATE DEFINER = 'root'@'%'
PROCEDURE deyestest.procedure2()
BEGIN 
  DECLARE v_id        INT;
  DECLARE v_userName  VARCHAR(155);
  DECLARE v_mobileNum VARCHAR(55);
  DECLARE v_tmp       VARCHAR(1000);
  DECLARE v_info      VARCHAR(2000);
  DECLARE v_info2     VARCHAR(2000);
  DECLARE v_int       INT;

  DECLARE v_json      LONGTEXT;
  DECLARE done        INT DEFAULT 0;


  DECLARE myCursor CURSOR FOR
  SELECT userId
       , userName
       , mobileNum
  FROM
    user_info
  ORDER BY
    userId
  LIMIT
    2, 2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN myCursor;
  SET v_info = concat('{');
   REPEAT
  
  IF done <> 1 THEN
  FETCH myCursor INTO v_id, v_userName, v_mobileNum;
  SET v_tmp = concat('[', v_id, ',', v_userName, ',', v_mobileNum, ']', ',');
  SET v_info = concat(v_info, v_tmp);
  FETCH myCursor INTO v_id, v_userName, v_mobileNum;
END IF;
UNTIL done
END REPEAT;
  SET v_int = length(v_info);
  SET v_info = left(v_info, v_int - 1);
  
  SET v_info2 = concat(v_info, '}');
  CLOSE myCursor;
END

 

代码如上：

主要就是注意循环条件done的使用，当游标取到最后一个数据的下一个不存在的数据时，done会被值为1，那么就要在repeat中进行done的判断，当done不为1的时候，才执行循环。