您可以在其他数据库中使用递归查询(性能上的YMMV)很容易地做到这一点。

另一种方法是存储两个额外的数据位，一个左值和一个右值。左值和右值来自于对所表示的树结构的预序遍历。

这就是所谓的Modified Preorder Tree遍历，允许您运行一个简单的查询来一次性获得所有父值。它也被称为“嵌套集”。

试试这些:

表定义:

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

如果需要快速读取速度，最好的选择是使用闭包表。闭包表为每个祖先/后代对包含一行。在你的例子中，闭包表是这样的

ancestor | descendant | depth
0        | 0          | 0
0        | 19         | 1
0        | 20         | 2
0        | 21         | 3
0        | 22         | 4
19       | 19         | 0
19       | 20         | 1
19       | 21         | 3
19       | 22         | 4
20       | 20         | 0
20       | 21         | 1
20       | 22         | 2
21       | 21         | 0
21       | 22         | 1
22       | 22         | 0

一旦有了这个表，分层查询就变得非常简单和快速。获取类别20的所有子类:

SELECT cat.* FROM categories_closure AS cl
INNER JOIN categories AS cat ON cat.id = cl.descendant
WHERE cl.ancestor = 20 AND cl.depth > 0

当然，无论何时使用这样的非规格化数据都有一个很大的缺点。您需要在类别表旁边维护闭包表。最好的方法可能是使用触发器，但是正确跟踪闭包表的插入/更新/删除有点复杂。与任何事情一样，您需要查看您的需求，并决定哪种方法最适合您。

编辑:请参阅问题:在关系数据库中存储层次数据的选项是什么?更多选项。不同的情况有不同的最佳解决方案。

使用BlueM/tree php类在mysql中创建一个自关系表的树。

Tree和Tree\Node是PHP类，用于处理使用父ID引用分层结构的数据。一个典型的例子是关系数据库中的一个表，其中每个记录的“父”字段引用另一个记录的主键。当然，Tree不能只使用来自数据库的数据，而是使用任何数据:您提供数据，Tree使用它，而不管数据来自何处以及如何处理。阅读更多

下面是一个使用BlueM/tree的例子:

<?php 
require '/path/to/vendor/autoload.php'; $db = new PDO(...); // Set up your database connection 
$stm = $db->query('SELECT id, parent, title FROM tablename ORDER BY title'); 
$records = $stm->fetchAll(PDO::FETCH_ASSOC); 
$tree = new BlueM\Tree($records); 
...

基于@trincot的答案，很好地解释了，我使用WITH RECURSIVE()语句使用当前页面的id创建一个面包屑，并在层次结构中反向查找路由表中的每个父节点。

因此，@trincot解决方案在相反的方向上进行了调整，以寻找父母而不是后代。

我还添加了深度值，这是有用的结果顺序颠倒(否则面包屑将上下颠倒)。

WITH RECURSIVE cte (
    `id`,
    `title`,
    `url`,
    `icon`,
    `class`,
    `parent_id`,
    `depth`
) AS (
    SELECT   
        `id`,
        `title`,
        `url`,
        `icon`,
        `class`,
        `parent_id`,
        1 AS `depth` 
    FROM     `route`
    WHERE    `id` = :id
      
    UNION ALL 
    SELECT 
        P.`id`,
        P.`title`,
        P.`url`,
        P.`icon`,
        P.`class`,
        P.`parent_id`,
        `depth` + 1
    FROM `route` P
        
    INNER JOIN cte
        ON P.`id` = cte.`parent_id`
)
SELECT * FROM cte ORDER BY `depth` DESC;

在升级到mySQL 8+之前，我正在使用vars，但它已弃用，并且不再在8.0.22版本上工作!

编辑2021-02-19: 分层菜单示例

在@david评论之后，我决定尝试制作一个包含所有节点的完整分层菜单，并按我想要的方式排序(用排序列在每个深度中排序项目)。对我的用户/授权矩阵页面非常有用。

这确实简化了我的旧版本，每个深度上都有一个查询(PHP循环)。

这个例子集成了一个INNER JOIN和url表来根据网站(多网站CMS系统)过滤路由。

您可以看到包含CONCAT()函数的基本路径列，以正确的方式对菜单进行排序。

SELECT R.* FROM (
    WITH RECURSIVE cte (
        `id`,
        `title`,
        `url`,
        `icon`,
        `class`,
        `parent`,
        `depth`,
        `sorting`,
        `path`
    ) AS (
        SELECT 
            `id`,
            `title`,
            `url`,
            `icon`,
            `class`,
            `parent`,
            1 AS `depth`,
            `sorting`,
            CONCAT(`sorting`, ' ' , `title`) AS `path`
        FROM `route`
        WHERE `parent` = 0
        UNION ALL SELECT 
            D.`id`,
            D.`title`,
            D.`url`,
            D.`icon`,
            D.`class`,
            D.`parent`,
            `depth` + 1,
            D.`sorting`,
            CONCAT(cte.`path`, ' > ', D.`sorting`, ' ' , D.`title`)
        FROM `route` D
        INNER JOIN cte
            ON cte.`id` = D.`parent`
    )
    SELECT * FROM cte
) R

INNER JOIN `url` U
    ON R.`id` = U.`route_id`
    AND U.`site_id` = 1

ORDER BY `path` ASC  

这是一个有点棘手的问题，检查一下它是否适合你

select a.id,if(a.parent = 0,@varw:=concat(a.id,','),@varw:=concat(a.id,',',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (a.id = b.parent),(select @varw:='') as c  having list like '%19,%';

SQL小提琴链接http://www.sqlfiddle.com/#!2 / e3cdf / 2

用字段名和表名替换。