小开

表结构

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

查询:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

输出

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+

大多数用户都曾经在SQL数据库中处理过层次数据，并且毫无疑问地了解到层次数据的管理不是关系数据库的目的。关系数据库的表不是分层的(像XML一样)，而只是一个平面列表。层次数据具有亲子关系，在关系数据库表中不能自然地表示这种关系。阅读更多 < / p >

更多细节请参考博客。

编辑:

select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv

输出:

category_id name    parent
19  category1   0
20  category2   19
21  category3   20
22  category4   21

参考:如何在Mysql中做递归SELECT查询?

小开

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

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

用字段名和表名替换。

小开

我能想到的最好方法是

使用沿袭存储\sort\跟踪树。这已经足够了，而且阅读速度比其他任何方法都要快数千倍。它还允许即使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)

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

小开

对另一个问题也是这样吗

Mysql select recursive get all child with multiple level

查询将是:

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(
SELECT GROUP_CONCAT(id SEPARATOR ',')
FROM table WHERE parent_id IN (@pv)
) AS lv FROM table
JOIN
(SELECT @pv:=1)tmp
WHERE parent_id IN (@pv)
) a;

小开

我发现更容易做到:

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));

小开

对于MySQL 8 +:使用递归with语法。
对于MySQL 5. x:使用内联变量、路径id或自连接

MySQL 8 +

with recursive cte (id, name, parent_id) as (
select     id,
name,
parent_id
from       products
where      parent_id = 19
union all
select     p.id,
p.name,
p.parent_id
from       products p
inner join cte
on p.parent_id = cte.id
)
select * from cte;

在parent_id = 19中指定的值应该设置为你想要选择其所有后代的父类的id。

MySQL 5.倍

对于不支持通用表表达式的MySQL版本(直到5.7版本)，你可以通过以下查询来实现:

select  id,
name,
parent_id
from    (select * from products
order by parent_id, id) products_sorted,
(select @pv := '19') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

这是一个小提琴。

在这里，@pv := '19'中指定的值应该设置为你想要选择其所有后代的父类的id。

如果父节点有多个子节点，也可以这样做。然而，要求每条记录都满足条件parent_id < id，否则结果将不完整。

查询中的变量赋值

这个查询使用特定的MySQL语法:变量在执行过程中被赋值和修改。对执行顺序做了一些假设:

from子句首先求值。这就是@pv初始化的地方。
where子句按照从from别名中检索的顺序为每条记录求值。因此，这里的条件只包含父节点已经被标识为在后代树中的记录(主父节点的所有子节点都被逐步添加到@pv中)。
where子句中的条件是按顺序求值的，一旦总体结果确定，求值就会中断。因此，第二个条件必须在第二个位置，因为它将id添加到父列表中，并且只有在id通过了第一个条件时才会发生这种情况。length函数只被调用来确保这个条件总是为真，即使pv字符串由于某种原因会产生一个假值。

总而言之，人们可能会发现这些假设风险太大，无法依赖。文档警告:

你可能会得到你期望的结果，但这并不能保证…包含用户变量的表达式的求值顺序未定义。

因此，即使它与上面的查询一致，求值顺序仍然可能发生变化，例如当您添加条件或将此查询用作较大查询中的视图或子查询时。这是一个“特色”;将在未来的MySQL版本中删除:

以前版本的MySQL可以在SET以外的语句中为用户变量赋值。为了向后兼容，MySQL 8.0支持这个功能，但在MySQL的未来版本中可能会被删除。

如上所述，从MySQL 8.0开始，你应该使用递归的with语法。

效率

对于非常大的数据集，这个解决方案可能会变慢，因为find_in_set操作不是在列表中查找数字的最理想方法，当然不是在与返回的记录数量大小相同数量级的列表中。

选项1:`with recursive`， `connect by`

越来越多的数据库实现了ISO标准WITH [RECURSIVE]语法进行递归查询(例如:Postgres 8.4 +， SQL Server 2005+， DB2， Oracle 11 gr2 +， SQLite 3.8.4 +，火鸟2.1 +， H2， HyperSQL 2.1.0的+， ISO标准WITH [RECURSIVE]语法0， ISO标准WITH [RECURSIVE]语法1)。和ISO标准WITH [RECURSIVE]语法2。请参阅答案顶部的语法。

一些数据库有可选的、非标准的语法用于分层查找，例如甲骨文、DB2、Informix、CUBRID和其他数据库上可用的CONNECT BY子句。

MySQL 5.7版本不提供这样的特性。如果您的数据库引擎提供了这种语法，或者您可以迁移到提供这种语法的数据库引擎，那么这当然是最好的选择。如果不是，那么也要考虑以下备选方案。

备选方案2:路径样式标识符

如果你将包含层次信息的id值赋给路径，事情就会变得容易得多。例如，在你的例子中，它可能是这样的:

ID	的名字
19	category1
19/1	category2
19/1/1	category3
19/1/1/1	category4

然后你的select看起来像这样:

select  id,
name
from    products
where   id like '19/%'

替代方案3:重复的自连接

如果你知道层次树深度的上限，你可以像这样使用标准的sql查询:

select      p6.parent_id as parent6_id,
p5.parent_id as parent5_id,
p4.parent_id as parent4_id,
p3.parent_id as parent3_id,
p2.parent_id as parent2_id,
p1.parent_id as parent_id,
p1.id as product_id,
p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id
left join   products p3 on p3.id = p2.parent_id
left join   products p4 on p4.id = p3.parent_id
left join   products p5 on p5.id = p4.parent_id
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id,
p2.parent_id,
p3.parent_id,
p4.parent_id,
p5.parent_id,
p6.parent_id)
order       by 1, 2, 3, 4, 5, 6, 7;

请看这个小提琴

where条件指定了你想检索哪个父元素的后代。您可以根据需要使用更多级别扩展此查询。

小开

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

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

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

小开

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

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

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

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

小开

试试这些:

表定义:

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

小开

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

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 @pv:=id as id, name, parent_id
from products
join (select @pv:=19)tmp
where parent_id=@pv

结果:

id  name        parent_id
20  category2   19
21  category3   20
22  category4   21
26  category24  22

．.．左连接:

select
@pv:=p1.id as id
, p2.name as parent_name
, p1.name name
, p1.parent_id
from products p1
join (select @pv:=19)tmp
left join products p2 on p2.id=p1.parent_id -- optional join to get parent name
where p1.parent_id=@pv

@tincot列出所有孩子的解决方案:

select  id,
name,
parent_id
from    (select * from products
order by parent_id, id) products_sorted,
(select @pv := '19') initialisation
where   find_in_set(parent_id, @pv) > 0
and     @pv := concat(@pv, ',', id)

用Sql小提琴在线测试并查看所有结果。

< a href = " http://sqlfiddle.com/ !9 / a318e3/4/0 noreferrer“rel = > http://sqlfiddle.com/ !9 / a318e3/4/0 < / >

小开

这里没有提到的是，为每个项添加持久路径列，尽管它与第二种备选方案有点相似，但对于大型层次结构查询和简单的(插入、更新、删除)项来说不同且成本较低。

一些像:

id | name        | path
19 | category1   | /19
20 | category2   | /19/20
21 | category3   | /19/20/21
22 | category4   | /19/20/21/22

例子:

-- get children of category3:
SELECT * FROM my_table WHERE path LIKE '/19/20/21%'
-- Reparent an item:
UPDATE my_table SET path = REPLACE(path, '/19/20', '/15/16') WHERE path LIKE '/19/20/%'

优化路径长度和ORDER BY path使用base36编码代替实际数值路径id

 // base10 => base36
'1' => '1',
'10' => 'A',
'100' => '2S',
'1000' => 'RS',
'10000' => '7PS',
'100000' => '255S',
'1000000' => 'LFLS',
'1000000000' => 'GJDGXS',
'1000000000000' => 'CRE66I9S'

https://en.wikipedia.org/wiki/Base36

还通过对编码的id使用固定长度和填充来抑制斜杠'/'分隔符

详细优化说明如下: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/ < / p >

待办事项

构建一个函数或过程，以分割检索一个项的祖先的路径

小开

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

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

这是一个小提琴。

小开

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

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;

小开

它是一个类别表。

SELECT  id,
NAME,
parent_category
FROM    (SELECT * FROM category
ORDER BY parent_category, id) products_sorted,
(SELECT @pv := '2') initialisation
WHERE   FIND_IN_SET(parent_category, @pv) > 0
AND     @pv := CONCAT(@pv, ',', id)

< >强输出: < / p >

小开

基于@trincot的答案，非常好的解释，我使用WITH RECURSIVE ()语句制作面包屑使用当前页面的id和在层次结构中往回走找到我的route表中的每个parent。

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

我还添加了depth值，用于反转结果顺序(否则面包屑将上下颠倒)。

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版本 !

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

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

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

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

你可以看到基本的path列，其中包含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

如何创建一个MySQL层次递归查询?