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

问题描述

我正在尝试使用phpMyAdmin创建MySQL函数并收到此错误.

I am trying to create a MySQL function using phpMyAdmin and getting this error.

#1415 - Not allowed to return a result set from a function

功能代码如下

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 ;

有什么建议吗?

预先感谢

推荐答案

因为

SELECT left_id AS c1 FROM mlm_user_mst WHERE parent_id=a AND left_id>0; 

不设置变量c1，它返回一个包含名为c1的列的集合

doesn't set the variable c1, it returns a set with a column named c1

你想要

SELECT left_id INTO c1 FROM mlm_user_mst WHERE parent_id=a AND left_id>0; 

与c2类似.

这篇关于为什么mysql给出错误“不允许从函数返回结果集"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！