MySQL 中的递归存储函数 [英] Recursive stored functions in 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

当我尝试使用没有父级 (parent_id = 0) 的类别运行此函数时，它工作正常，但是当我尝试使用 parent_id > 0 的类别时，我得到 1424 递归存储函数和触发器是不允许的.

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.

我该如何解决这个问题?我打算将此代码托管在至少应具有 MySQL 服务器版本 5.1 的常规网络托管服务上.

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.

在艾克·沃克 (Ike Walker) 的帮助下，我做了一个程序，但效果很好

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 不允许递归函数，即使你设置了 max_sp_recursion_depth.

MySQL does not allow recursive FUNCTIONs, even if you set max_sp_recursion_depth.

如果您设置 max_sp_recursion_depth，它确实允许在 PROCEDURE 中进行多达 255 次递归.

It does allow up to 255 recursion in a PROCEDURE if you set max_sp_recursion_depth.

所以我建议你用一个过程替换你的函数，为 return_path 使用一个 INOUT 变量.

So I recommend that you replace your function with a procedure, using an INOUT variable for the return_path.

这篇关于MySQL 中的递归存储函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！