如何创建MySQL分层递归查询

Question

如何创建MySQL分层递归查询我有一个MySQL表，如下所示：id | name        | parent_id19 | category1   | 020 | category2   | 1921 | category3   | 2022 | category4   | 21......现在，我想要一个MySQL查询，我只提供id [例如说'id = 19']然后我应该得到它的所有子id [即结果应该有id'，21,22']。 ...而且，孩子们的等级不知道它可以变化....另外，我已经有了使用for循环的解决方案.....如果可能的话，让我知道如何使用单个MySQL查询来实现相同的功能。

Answer 1

试试这些：

表定义：

DROP TABLE IF EXISTS category;

CREATE TABLE category (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(20),

    parent_id INT,

    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)

    REFERENCES category (id)

) engine=innodb;

实验行：

INSERT INTO category VALUES

(19, 'category1', NULL),

(20, 'category2', 19),

(21, 'category3', 20),

(22, 'category4', 21),

(23, 'categoryA', 19),

(24, 'categoryB', 23),

(25, 'categoryC', 23),

(26, 'categoryD', 24);

递归存储过程：

DROP PROCEDURE IF EXISTS getpath;

DELIMITER $$

CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)

BEGIN

    DECLARE catname VARCHAR(20);

    DECLARE temppath TEXT;

    DECLARE tempparent INT;

    SET max_sp_recursion_depth = 255;

    SELECT name, parent_id FROM category WHERE 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 ;

存储过程的包装函数：

DROP FUNCTION IF EXISTS getpath;

DELIMITER $$

CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC

BEGIN

    DECLARE res TEXT;

    CALL getpath(cat_id, res);

    RETURN res;

END$$

DELIMITER ;

选择示例：

SELECT id, name, getpath(id) AS path FROM category;

输出：

+----+-----------+-----------------------------------------+

| id | name      | path                                    |

+----+-----------+-----------------------------------------+

| 19 | category1 | category1                               |

| 20 | category2 | category1/category2                     |

| 21 | category3 | category1/category2/category3           |

| 22 | category4 | category1/category2/category3/category4 |

| 23 | categoryA | category1/categoryA                     |

| 24 | categoryB | category1/categoryA/categoryB           |

| 25 | categoryC | category1/categoryA/categoryC           |

| 26 | categoryD | category1/categoryA/categoryB/categoryD |

+----+-----------+-----------------------------------------+

过滤具有特定路径的行：

SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';

输出：

+----+-----------+-----------------------------------------+

| id | name      | path                                    |

+----+-----------+-----------------------------------------+

| 20 | category2 | category1/category2                     |

| 21 | category3 | category1/category2/category3           |

| 22 | category4 | category1/category2/category3/category4 |

+----+-----------+-----------------------------------------+