MySQL 如何摆脱“错误 1329：无数据 - 提取、选择或处理零行”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3463283/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get rid of "Error 1329: No data - zero rows fetched, selected, or processed"
提问by Babibo
I have a stored procedure which does not need to return any values. It runs smoothly and without any problem. However, it outputs an error message after finishing its run:
我有一个不需要返回任何值的存储过程。它运行顺利，没有任何问题。但是，它在完成运行后输出错误消息：
Error: No data - zero rows fetched, selected, or processed
错误：无数据 - 提取、选择或处理零行
How can I get rid of this error message?
我怎样才能摆脱这个错误信息？
CREATE PROCEDURE `testing_proc`()
READS SQL DATA
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE l_name VARCHAR(20);
DECLARE my_cur CURSOR FOR
SELECT name FROM customer_tbl;
OPEN my_cur;
my_cur_loop:
LOOP FETCH my_cur INTO l_name;
IF done = 1 THEN
LEAVE my_cur_loop;
END IF;
INSERT INTO names_tbl VALUES(l_name);
END LOOP my_cur_loop;
CLOSE my_cur;
END
回答by RobertG
I guess you just forgot to include the following line in your post:
我猜您只是忘记在您的帖子中包含以下行：
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
Your code is correct, but bug/strange behaviour of mysql causes the warning to appear even if it was handled. You can avoid that if you add a "dummy" statement to the end of your procedure that invovles a table and is successful, this will clear the warning. (See http://dev.mysql.com/doc/refman/5.5/en/show-warnings.html) In your case:
您的代码是正确的，但 mysql 的错误/奇怪行为会导致警告出现，即使它已被处理。您可以避免这种情况，如果您在调用表并成功的过程末尾添加“虚拟”语句，这将清除警告。（请参阅http://dev.mysql.com/doc/refman/5.5/en/show-warnings.html）在您的情况下：
SELECT name INTO l_name FROM customer_tbl LIMIT 1;
after the end of the loop. On MySQL 5.5.13 the warning disappears, on Linux and Windows. I commented on MySQL Bug 60840 and I hope they will fix it some time in the future...
循环结束后。在 MySQL 5.5.13 上警告消失，在 Linux 和 Windows 上。我评论了 MySQL Bug 60840，我希望他们在未来的某个时间修复它......
回答by robm
You need to define a continue handler like:
您需要定义一个继续处理程序，如：
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
So it would look like:
所以它看起来像：
DECLARE done INT DEFAULT 0;
DECLARE l_name VARCHAR(20);
DECLARE my_cur CURSOR FOR
SELECT name FROM customer_tbl;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN my_cur;
my_cur_loop:
LOOP FETCH my_cur INTO l_name;
IF done = 1 THEN
LEAVE my_cur_loop;
END IF;
INSERT INTO names_tbl VALUES(l_name);
END LOOP my_cur_loop;
CLOSE my_cur;
回答by DanJGer
I ran into this and pulled out my hair till I ran across this in the official mysql docs
我遇到了这个并拔掉了我的头发，直到我在官方 mysql 文档中遇到了这个
Before MySQL 5.6.3, if a statement that generates a warning or error causes a condition handler to be invoked, the handler may not clear the diagnostic area. This might lead to the appearance that the handler was not invoked. The following discussion demonstrates the issue and provides a workaround.
在 MySQL 5.6.3 之前，如果生成警告或错误的语句导致调用条件处理程序，则处理程序可能不会清除诊断区域。这可能会导致处理程序未被调用的外观。以下讨论演示了该问题并提供了解决方法。
Click the link and scroll to the bottom for details but the fix was to include a successful select INSIDE the CONTINUE HANDLER:
单击链接并滚动到底部以获取详细信息，但修复方法是在 CONTINUE HANDLER 中成功选择：
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SELECT 1 INTO @handler_invoked FROM (SELECT 1) AS t;
END;
回答by Jed
I tried the solutions in here and none, including the continue handler worked for me. I still get the messages in the MySQL error log. I discovered this also with my "select ... into ..." which made sense, but I really thought the continue handler would work for the cursors. Either way I found using "found_rows()" to find out if any rows were returned worked perfectly. This mean that the simple "select into" statements have to be converted to cursors, but it isn't much work and does solve the problem.
我在这里尝试了解决方案，但没有一个，包括继续处理程序对我有用。我仍然在 MySQL 错误日志中收到消息。我也用我的“select ... into ...”发现了这一点，这是有道理的，但我真的认为继续处理程序适用于游标。无论哪种方式，我都发现使用“found_rows()”来确定是否有任何行被完美地返回。这意味着简单的“select into”语句必须转换为游标，但它没有多少工作并且确实解决了问题。
DECLARE v_rowcount integer unsigned;
DECLARE cur_entries cursor for
select app_name, proc_name, error_code, sum(occurrences) occurrences
from that_table...;
open cur_entries;
set v_rowcount = found_rows();
if v_rowcount > 0 then
fetch cur_entries into v_app_name, v_proc_name, v_error_code, v_occurrences;
...
end if;
close cur_entries;
I wrote this up on my personal blog here: http://tinky2jed.wordpress.com/technical-stuff/mysql/mysql-no-data-zero-rows-fetched-how-to-code-for-it/
我在我的个人博客上写了这个：http: //tinky2jed.wordpress.com/technical-stuff/mysql/mysql-no-data-zero-rows-fetched-how-to-code-for-it/
回答by user977109
I don't know if this fixes the cursor issue, but I ran into this warning with a stored function and found that if you use:
我不知道这是否解决了游标问题，但我遇到了带有存储函数的警告，并发现如果您使用：
RETURN (SELECT x From myTable...);
instead of
代替
SELECT x into myVar...return myVar
I got this from this helpful doc: http://bugs.mysql.com/bug.php?id=42834
我从这个有用的文档中得到了这个：http: //bugs.mysql.com/bug.php?id= 42834
回答by Francisco Lemos
Normally this happens when you overshoot a cursor range, so checkout the loop conditions where the FETCHstatement is
通常，当您超出游标范围时会发生这种情况，因此请检查FETCH语句所在的循环条件
回答by Notapro-but-icanberight-too
so I was getting the same error in my code, and I realized that I had not incremented my loop variable (using while loop) and hence, the loop was going infinite.
所以我在我的代码中遇到了同样的错误，我意识到我没有增加我的循环变量（使用 while 循环），因此，循环是无限的。
In your code too, you are not setting "done" to 1 anywhere, and I think the code is showing error because of that.
在您的代码中，您也没有在任何地方将“完成”设置为 1，我认为代码因此而显示错误。
I hope this helps!
我希望这有帮助！
