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的時候，才執行循環。