MySQL游标总是退出循环

Question

如果我在mysql中运行游标查询和select value查询，则它会返回行，但在游标中时，它总是退出循环。

这里有什么问题吗？

我添加了"BEFORE LOOP"、"EXIT"one_answers"IN LOOP"，所以它打印在它所在的位置，但它总是以BEFORE LOOP开始，然后以EXIT结束。

CREATE PROCEDURE getTotal()
BEGIN
DECLARE HOSTID INT;
DECLARE cITEMID INT;
DECLARE Total INT;
declare finished INT default false;
declare cur1 cursor for SELECT itemid  FROM items WHERE hostid = 10579;
declare continue handler for not found set finished = true;
open cur1;
loop_1: loop
fetch cur1 into cITEMID;
SELECT "BEFORE LOOP";
if finished then
SELECT "EXIT";
leave loop_1;
end if;
SELECT "IN LOOP";
-- Test query
SELECT value from history_uint WHERE itemid = cITEMID ORDER BY itemid DESC LIMIT 1;
-- Final select query will look like this.
-- SET @Total := @Total + (SELECT value from history_uint WHERE itemid = cITEMID ORDER BY itemid DESC LIMIT 1);
-- SELECT @Total;
end loop;
close cur1;
END //
DELIMITER ;

查询：

SELECT itemid  FROM items WHERE hostid = 10579;
| itemid |
| 12345  |
| 12346  |
| 12347  |
SELECT value from history_uint WHERE itemid = 12345 ORDER BY itemid DESC LIMIT 1;
| value | 
| 1     |

Answer 1

解决了这个问题。

变量不应与查询中的任何列相同。

DECLARE HOSTID INT;
declare cur1 cursor for SELECT itemid  FROM items WHERE hostid = 10579;

由于cur1查询有一个名为hostid的列，它将触发not foundcontinue处理程序，该处理程序将finished变量设置为true。

它不仅会触发continue处理程序，而且在某些情况下还会产生语法错误。来源-https://stackoverflow.com/a/60988686/13109839

解决方案将HOSTID变量重命名为_HOSTID。

DECLARE _HOSTID INT;