如何创建MySQL分层递归查询

Question

如何创建MySQL分层递归查询

我有一个MySQL表，如下所示：

id | name        | parent_id

19 | category1   | 0

20 | category2   | 19

21 | category3   | 20

22 | 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 DETERMINISTICBEGIN    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 |+----+-----------+-----------------------------------------+