mysql 函数递归函数_MySQL中的递归存储函数

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.