解决MySQL游标循环多执行一次的问题 MySQL存储过程使用游标时,多执行一次的问题
一、问题描述
1、在写MySQL存储过程,使用到游标遍历数据时,遇到一个问题:游标的循环比数据集填充到游标要多执行1次。
2、表述不清,这样理解:填充到游标时,select 查询到10条, 实际循环会执行 11次。 即永远 n+1 多一次。
二、问题重现
1、如下,存储过程 pro_multi() ,将会执行 2次。
DROP PROCEDURE IF EXISTS pro_multi ;
CREATE PROCEDURE pro_multi()
BEGIN
DECLARE count int DEFAULT 0 ; -- 定义 count 计数
DECLARE done int DEFAULT FALSE; -- 定义游标循环变量
DECLARE my_cursor CURSOR FOR( SELECT 1) ; -- 定义游标,获取到1条数据
DECLARE CONTINUE HANDLER FOR NOT FOUND set done = TRUE ; -- 游标循环结束时,将控制变量done = TRUE
OPEN my_cursor ; -- 打开游标
WHILE NOT done DO -- 开始循环
FETCH my_cursor INTO count ; -- 获取一行数据,赋值到自定义变量中
INSERT INTO `batch` (`id`,`name`, `age`) VALUES (UUID(),count, '0'); -- 插入数据
SET count = count + 1; -- 变量+1
END WHILE ; -- 结束循环
END ;
三、问题定位
1、原因:游标第一次获取数据时,正常 ;第二次获取数据时,执行到末尾,没有数据,此时done = TRUE , 然而下面的 INSERT 语句,却不知道游标已经到末尾,仍然插入了一条数据。
2、从而造成了,MySQL游标循环执行多一次的问题。
四、问题解决
1、方法一:在循环体外,先获取一次游标数据
DROP PROCEDURE IF EXISTS pro_multi_fix1 ;
CREATE PROCEDURE pro_multi_fix1()
BEGIN
-- 解决 多执行一次的问题
DECLARE count int DEFAULT 0 ; -- 定义 count 计数
DECLARE done int DEFAULT FALSE; -- 定义游标循环变量
DECLARE my_cursor CURSOR FOR( SELECT 1) ; -- 定义游标,获取到1条数据
DECLARE CONTINUE HANDLER FOR NOT FOUND set done = TRUE ; -- 游标循环结束时,将控制变量done = TRUE
OPEN my_cursor ; -- 打开游标
FETCH my_cursor INTO count ; -- 循环外,先获取一次数据
WHILE NOT done DO -- 开始循环
INSERT INTO `batch` (`id`,`name`, `age`) VALUES (UUID(),count, '0'); -- 插入数据
FETCH my_cursor INTO count ; -- 再一次获取数据
SET count = count + 1; -- 变量+1
END WHILE ; -- 结束循环
END ;
2、方法二:在循环体内,增加判断游标是否已经执行结束
DROP PROCEDURE IF EXISTS pro_multi_fix2 ;
CREATE PROCEDURE pro_multi_fix2()
BEGIN
-- 解决 多执行一次的问题 (方法二)
DECLARE count int DEFAULT 0 ; -- 定义 count 计数
DECLARE done int DEFAULT FALSE; -- 定义游标循环变量
DECLARE my_cursor CURSOR FOR( SELECT 1) ; -- 定义游标,获取到1条数据
DECLARE CONTINUE HANDLER FOR NOT FOUND set done = TRUE ; -- 游标循环结束时,将控制变量done = TRUE
OPEN my_cursor ; -- 打开游标
WHILE NOT done DO -- 开始循环
FETCH my_cursor INTO count ; -- 再一次获取数据
IF NOT done THEN -- 增加判断,游标循环结束后,不插入数据
INSERT INTO `batch` (`id`,`name`, `age`) VALUES (UUID(),count, '0'); -- 插入数据
END IF ;
SET count = count + 1; -- 变量+1
END WHILE ; -- 结束循环
END ;
---- 还有更多 ....
MyBatis调用存储过程,MyBatis调用函数的使用方法
MySQL 创建函数, MySQL定义函数实现汉字转拼音 MySQL汉字转拼音MySQL汉字生成拼音字符串
MySQL WHILE和LOOP和REPEAT循环的用法区别 MySQL三种循环的区别 MySQL循环使用方法