mysql游标死循环_解决MySQL 游标最后一行重复

在使用MySQL游标时，发现最后一行一直被重复读取

后来在发现应该把读取到的数据放在下一个循环里面使用可以避免重复

代码如下：

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `sp_getAllTableName`$$

CREATE PROCEDURE `sp_getAllTableName`(OUT strAllTableNames VARCHAR(10000))

BEGIN

DECLARE bEnd BOOLEAN DEFAULT FALSE;

DECLARE tbName VARCHAR(255);

# 声明游标

DECLARE curTableNames CURSOR FOR SELECT TABLE_NAME FROM information_schema.tables AS t WHERE t.table_schema="test";

# DECLARE CONTINUE HANDLER

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET bEnd=TRUE;

SET strAllTableNames = "";

# 打开游标

OPEN curTableNames;

# 获取所有行数据

# 获取第一行内容

FETCH curTableNames INTO tbName;

# 循环开始

REPEAT

SET strAllTableNames = CONCAT(strAllTableNames,tbName,";");

# 获取下一行内容

FETCH curTableNames INTO tbName;

# 循环结束

UNTIL bEnd END REPEAT;

# 关闭游标

CLOSE curTableNames;

# 处理完毕

SET strAllTableNames = CONCAT("All table names:",strAllTableNames);

END$$

DELIMITER ;

CALL test.sp_getAllTableName(@allNames);

SELECT @allNames;