为什么mysql给出错误“不允许从函数返回结果集”？

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

提前致谢

2 个解决方案

#1

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。

#2

that is because you are using SELECT queries whose output is not stored into variables or temporary inside FUNCTION which 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;

要么

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;

#1