我能想到的最好方法是

使用沿袭存储\排序\跟踪树。这已经足够了，而且阅读速度比其他任何方法都要快数千倍。 它还允许即使DB将改变也保持该模式(因为任何DB将允许使用该模式) 使用为特定ID确定谱系的函数。 您可以随心所欲地使用它(在选择中，或在CUD操作中，甚至按作业)。

谱系方法描述。可以在任何地方找到，例如 这里或者这里。 至于功能，这就是我的灵感所在。

在最后-得到或多或少简单，相对快速，简单的解决方案。

函数的身体

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
    READS SQL DATA
BEGIN

 DECLARE v_rec INT DEFAULT 0;

 DECLARE done INT DEFAULT FALSE;
 DECLARE v_res text DEFAULT '';
 DECLARE v_papa int;
 DECLARE v_papa_papa int DEFAULT -1;
 DECLARE csr CURSOR FOR 
  select _id,parent_id -- @n:=@n+1 as rownum,T1.* 
  from 
    (SELECT @r AS _id,
        (SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := the_id, @l := 0,@n:=0) vars,
        table m
    WHERE @r <> 0
    ) T1
    where T1.parent_id is not null
 ORDER BY T1.lvl DESC;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open csr;
    read_loop: LOOP
    fetch csr into v_papa,v_papa_papa;
        SET v_rec = v_rec+1;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- add first
        IF v_rec = 1 THEN
            SET v_res = v_papa_papa;
        END IF;
        SET v_res = CONCAT(v_res,'-',v_papa);
    END LOOP;
    close csr;
    return v_res;
END

然后你就

select get_lineage(the_id)

希望它能帮助到一些人:)

这对我有用，希望这对你也有用。它会给你一个记录集根到子为任何特定的菜单。根据您的需求更改字段名称。

SET @id:= '22';

SELECT Menu_Name, (@id:=Sub_Menu_ID ) as Sub_Menu_ID, Menu_ID 
FROM 
    ( SELECT Menu_ID, Menu_Name, Sub_Menu_ID 
      FROM menu 
      ORDER BY Sub_Menu_ID DESC
    ) AS aux_table 
    WHERE Menu_ID = @id
     ORDER BY Sub_Menu_ID;

我发现更容易做到:

1)创建一个函数，检查一个项目是否在另一个项目的父层次结构中的任何地方。就像这样(我不会写函数，用WHILE DO):

is_related(id, parent_id);

在你的例子中

is_related(21, 19) == 1;
is_related(20, 19) == 1;
is_related(21, 18) == 0;

2)使用子选择，就像这样:

select ...
from table t
join table pt on pt.id in (select i.id from table i where is_related(t.id,i.id));

使用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); 
...

我向你提出了一个问题。这将给你递归类别与一个单一的查询:

SELECT id,NAME,'' AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 WHERE prent is NULL
UNION 
SELECT b.id,a.name,b.name AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id WHERE a.prent is NULL AND b.name IS NOT NULL 
UNION 
SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id WHERE a.prent is NULL AND c.name IS NOT NULL 
UNION 
SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id LEFT JOIN Table1 AS d ON d.prent=c.id WHERE a.prent is NULL AND d.name IS NOT NULL 
ORDER BY NAME,subName,subsubName,subsubsubName

这是一把小提琴。

基于@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