mysql - MySQL中的hierarchy查询问题，不返回路径

Question

我试图从桌子上的项目列表中创建一个hierarchy。该表如下所示。

item_id	项目名	父亲身份
1	主要的	0
2	子 1	1
3	子 2	1

我想使用 MySQL 获取完整路径 (SYS_CONNECT_BY_PATH)，例如项目 2 的 Main/Sub-1 和 item3 的 Main/Sub-2 等等。所以我尝试了以下步骤。

1. 创建了一个过程：

DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
    DECLARE catname VARCHAR(2000);
    DECLARE temppath TEXT;
    DECLARE tempparent INT;
    SET max_sp_recursion_depth = 255;
    SELECT item_name, father_id FROM boats WHERE item_id=cat_id INTO catname, tempparent;
    IF tempparent IS NULL
    THEN
        SET path = catname;
    ELSE
        CALL getpath(tempparent, temppath);
        SET path = CONCAT(temppath, '/', catname);
    END IF;
END$$
DELIMITER ; 

2. 创建了一个函数：

DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(item_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE res TEXT;
    CALL getpath(item_id, res);
    RETURN res;
END$$
DELIMITER ;

3. 创建了一个查询：

SELECT item_id, item_name, getpath(item_id) AS path FROM my_table;

预期结果：

item_id	项目名	小路
1	主要的	主要的
2	子 1	主/副1
3	子 2	主要/次要 2

实际结果：

item_id	项目名	小路
1	主要的	无效的
2	子 1	无效的
3	子 2	无效的

任何帮助，将不胜感激。

标签： mysqlsqldatabaserecursionstored-procedures