MySQL中的递归存储函数 [英] Recursive stored functions in MySQL
问题描述
我正在尝试创建一个为特定类别递归构建路径的函数
CREATE FUNCTION getPath(inId INT)
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE return_path TEXT;
DECLARE return_parent_id INT;
SELECT CONCAT('/', name) INTO return_path FROM article_categories WHERE id = inId;
SELECT parent_id INTO return_parent_id FROM article_categories WHERE id = inId;
IF return_parent_id > 0 THEN
SELECT CONCAT(getPath(return_parent_id), return_path) INTO return_path;
END IF;
RETURN return_path;
END
当我尝试使用没有父项的类别(parent_id = 0)运行此函数时,它可以正常工作,但是当我尝试使用parent_id> 0的类别时,我得到1424递归存储的函数,并且不允许使用触发器. /p>
我该如何解决?我将在常规的Web托管服务上托管此代码,该服务应至少具有MySQL服务器版本5.1.
在Ike Walker的帮助下,我做了一个很好的程序
DROP PROCEDURE IF EXISTS getPath;
DELIMITER //
CREATE PROCEDURE getPath(IN category_id INT UNSIGNED, OUT return_path TEXT)
BEGIN
DECLARE parent_id INT UNSIGNED;
DECLARE path_result TEXT;
SET max_sp_recursion_depth=50;
SELECT CONCAT('/', ac.name), ac.parent_id INTO return_path, parent_id FROM article_categories AS ac WHERE ac.id = category_id;
IF parent_id > 0 THEN
CALL getPath(parent_id, path_result);
SELECT CONCAT(path_result, return_path) INTO return_path;
END IF;
END //
DELIMITER ;
然后我用类似的东西来称呼它
CALL getPath(72, @temp); SELECT @temp;
即使设置了max_sp_recursion_depth,MySQL也不允许递归功能.
如果设置max_sp_recursion_depth,则它确实允许在过程中最多进行255次递归.
因此,我建议您用一个过程替换函数,对return_path使用INOUT变量.
I'm trying to make a function that recursively builds a path for a specific category
CREATE FUNCTION getPath(inId INT)
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE return_path TEXT;
DECLARE return_parent_id INT;
SELECT CONCAT('/', name) INTO return_path FROM article_categories WHERE id = inId;
SELECT parent_id INTO return_parent_id FROM article_categories WHERE id = inId;
IF return_parent_id > 0 THEN
SELECT CONCAT(getPath(return_parent_id), return_path) INTO return_path;
END IF;
RETURN return_path;
END
When I try to run this function with a category that has no parents (parent_id = 0) it works fine but when I try a category that has a parent_id > 0 I get 1424 Recursive stored functions and triggers are not allowed.
How do I work around this? I'm going to host this code on a regular web hosting service that should have at least MySQL server version 5.1.
After some help from Ike Walker I have made a precedure instead that works fine
DROP PROCEDURE IF EXISTS getPath;
DELIMITER //
CREATE PROCEDURE getPath(IN category_id INT UNSIGNED, OUT return_path TEXT)
BEGIN
DECLARE parent_id INT UNSIGNED;
DECLARE path_result TEXT;
SET max_sp_recursion_depth=50;
SELECT CONCAT('/', ac.name), ac.parent_id INTO return_path, parent_id FROM article_categories AS ac WHERE ac.id = category_id;
IF parent_id > 0 THEN
CALL getPath(parent_id, path_result);
SELECT CONCAT(path_result, return_path) INTO return_path;
END IF;
END //
DELIMITER ;
I then use something like this to call it
CALL getPath(72, @temp); SELECT @temp;
MySQL does not allow recursive FUNCTIONs, even if you set max_sp_recursion_depth.
It does allow up to 255 recursion in a PROCEDURE if you set max_sp_recursion_depth.
So I recommend that you replace your function with a procedure, using an INOUT variable for the return_path.
这篇关于MySQL中的递归存储函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!