MySQL 为什么mysql会给出错误“不允许从函数返回结果集”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11880306/
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
Why mysql is giving error "Not allowed to return a result set from a function"?
提问by aslamdoctor
I am trying to create a MySQL function using phpMyAdmin and getting this error.
我正在尝试使用 phpMyAdmin 创建一个 MySQL 函数并收到此错误。
#1415 - Not allowed to return a result set from a function
The function code is as below
功能代码如下
DELIMITER $$
CREATE FUNCTION get_binary_count(a INT, c INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE c1, c2 INT;
SET c1=0;
SET c2=0;
SELECT left_id AS c1 FROM mlm_user_mst WHERE parent_id=a AND left_id>0;
SELECT right_id AS c2 FROM mlm_user_mst WHERE parent_id=a AND right_id>0;
IF(c1>0 AND c2>0)
THEN
SET c=c+1;
SET c=c+get_binary_count(c1,0);
SET c=c+get_binary_count(c2,0);
END IF;
RETURN c;
END$$
DELIMITER ;
Any suggestions?
有什么建议？
Thanks in advance
提前致谢
回答by podiluska
Because
因为
SELECT left_id AS c1 FROM mlm_user_mst WHERE parent_id=a AND left_id>0;
doesn't set the variable c1, it returns a set with a column named c1
不设置变量 c1，它返回一个名为 c1 的列的集合
You want
你要
SELECT left_id INTO c1 FROM mlm_user_mst WHERE parent_id=a AND left_id>0;
Similarly for c2.
对于 c2 也是如此。
回答by Omesh
that is because you are using SELECTqueries whose output is not stored into variables or temporary inside FUNCTIONwhich must. Function can return only one single value. So your code should be something like this:
那是因为您正在使用SELECT其输出未存储到变量或临时存储在FUNCTION其中的查询。函数只能返回一个值。所以你的代码应该是这样的：
CREATE TABLE t1 AS SELECT left_id AS c1 FROM mlm_user_mst WHERE parent_id=a AND left_id>0;
CREATE TABLE t2 AS SELECT right_id AS c2 FROM mlm_user_mst WHERE parent_id=a AND right_id>0;
or
或者
SELECT left_id AS c1 INTO @c1 FROM mlm_user_mst WHERE parent_id=a AND left_id>0 LIMIT 1;
SELECT right_id AS c2 INTO @c2 FROM mlm_user_mst WHERE parent_id=a AND right_id>0 LIMIT 1;
