这是一个分类表。

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)

输出:

我发现更容易做到:

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

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

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

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

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

一些像:

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路径使用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/

TODO

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

使用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 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

用字段名和表名替换。