顺序的SQL树层次结构
排序如下表格的最佳方法是：
CREATE TABLE category(
id INT(10),
parent_id INT(10),
name VARCHAR(50)
);
INSERT INTO category (id, parent_id, name) VALUES
(1, 0, 'pizza'), --node 1
(2, 0, 'burger'), --node 2
(3, 0, 'coffee'), --node 3
(4, 1, 'piperoni'), --node 1.1
(5, 1, 'cheese'), --node 1.2
(6, 1, 'vegetariana'), --node 1.3
(7, 5, 'extra cheese'); --node 1.2.1
按ID或名称分层排序：
'pizza'//节点1
'piperoni'//节点1.1
'cheese'//节点1.2
'多余的奶酪'//节点1.2.1
'vegetariana'//节点1.3
'汉堡'//节点2
'咖啡'//节点3
编辑： 名称末尾的数字是更好地可视化结构，它不是用于排序。
编辑2：正如多次提到的... name “奶酪1.2 ”结尾处的数字仅用于可视化目的，不适用于分类。 我将他们作为评论移动，太多人感到困惑，抱歉。
通过添加一个路径列和一个触发器，这可以很容易地完成。
首先添加一个varchar列，该列将包含从根节点到节点的路径：
ALTER TABLE category ADD path VARCHAR(50) NULL;
然后添加一个触发器来计算插入时的路径：
（只需将新的id与父路径相连接）
CREATE TRIGGER set_path BEFORE INSERT ON category
FOR EACH ROW SET NEW.path =
CONCAT(IFNULL((select path from category where id = NEW.parent_id), '0'), '.', New.id);
然后只需按路径选择顺序：
SELECT name, path FROM category ORDER BY path;
结果：
pizza 0.1
piperoni 0.1.4
cheese 0.1.5
extra cheese 0.1.5.7
vegetariana 0.1.6
burger 0.2
coffee 0.3
看小提琴。
这样维护成本也很小。 插入时隐藏路径字段，并通过触发器进行计算。 删除节点没有开销，因为节点的所有子节点也都被删除。 唯一的问题是更新节点的parent_id; 那么，不要这样做！ :)
嵌套树集与level列结合使用，对于读取和排序基于树的结构来说是非常好的技术。 很容易选择一个子树，将结果限制到一定水平，并在一个查询中进行排序。 但是插入和删除条目的成本相对较高，因此如果您在写入数据时经常查询数据，并且在读取性能很重要时应该使用它。 （对于50-100的时间去除，插入或移动元素应该没有问题，即使有1000个也不应该有问题）。
对于每个条目，您都存储它的left和right的level和值，在下面的示例中，如果您只想选择1.2的后代，那么它将是:( left ， right ， level ）：
SELECT * FROM table WHERE left >=7 AND right <=16
如果你想只选择孩子
SELECT * FROM table WHERE left >=7 AND right <=16 AND level=2
如果你想排序，你可以做
SELECT * FROM table WHERE left >=7 AND right <=16 ORDER BY left
根据您想要排序的方式，在保持分层结构分组的同时，按其他字段排序可能会产生问题。
1 (0,17,0)
|
|
+---------------+---------------------------------------+
| |
1.1 (1,6,1) 1.2 (7,16,1)
| |
+------------+-------+ +-------------------+--------+----------------+
| | | | |
1.1.1 (2,3,2) 1.1.2 (4,5,2) 1.2.1 (8,9,2) 1.2.2 (10,13,2) 1.2.2 (14,15,2)
|
|
|
1.2.2.1 (11,12,3)
关闭表 （为了完成，但我不会推荐你的用例）。 它将所有路径存储在树中，因此如果有多个级别，层次结构所需的存储空间将会非常快速地增长。
路径枚举有你存储的每个元素的路径与进入/0/ ， /0/1/查询路径很容易出现，但排序它不是灵活。
对于少量的条目，我会使用嵌套树集合 。 可悲的是，我没有一个很好的参考页面来描述这些技术并进行比较。
如果只有三层嵌套，你可以做类似的事情
SELECT c1.name FROM category as c1 LEFT JOIN category as c2
ON c1.parent_id = c2.id OR (c1.parent_id = 0 AND c1.id = c2.id)
ORDER BY c2.parent_id, c2.id, c1.id;
如果你有更多的嵌套层次，它会更棘手
为了获得更多的嵌套级别，你可以编写函数
delimiter ~
DROP FUNCTION getPriority~
CREATE FUNCTION getPriority (inID INT) RETURNS VARCHAR(255) DETERMINISTIC
begin
DECLARE gParentID INT DEFAULT 0;
DECLARE gPriority VARCHAR(255) DEFAULT '';
SET gPriority = inID;
SELECT parent_id INTO gParentID FROM category WHERE ID = inID;
WHILE gParentID > 0 DO
SET gPriority = CONCAT(gParentID, '.', gPriority);
SELECT parent_id INTO gParentID FROM category WHERE ID = gParentID;
END WHILE;
RETURN gPriority;
end~
delimiter ;
所以我现在就在
SELECT * FROM category ORDER BY getPriority(ID);
我有
+------+-----------+--------------------+
| ID | parent_id | name |
+------+-----------+--------------------+
| 1 | 0 | pizza 1 |
| 4 | 1 | piperoni 1.1 |
| 5 | 1 | cheese 1.2 |
| 7 | 5 | extra cheese 1.2.1 |
| 6 | 1 | vegetariana 1.3 |
| 2 | 0 | burger 2 |
| 3 | 0 | coffee 3 |
+------+-----------+--------------------+
下一篇: Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)