顺序sql树层次结构

Question

对这样一张桌子进行排序的最佳方法是什么：

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或name分层排序

“比萨饼”//节点1

‘'piperoni’//节点1.1

“奶酪”//节点1.2

“额外奶酪”//节点1.2.1

‘素食者’//节点1.3

‘汉堡包’//节点2

“咖啡”//节点3

编辑：名称末尾的数字是为了更好地可视化结构，而不是用于排序。

编辑2:我曾多次提到.name“奶酪1.2”末尾的数字仅用于可视化目的，而不是用于排序。我感动了他们的评论，太多的人被搞糊涂了，对不起。

Answer 1

通过添加path列和触发器，这可以相当容易地完成。

首先，添加一个varchar列，该列将包含从根到节点的路径：

ALTER TABLE category ADD path VARCHAR(50) NULL;

然后添加一个触发器来计算insert上的路径：

(只需将新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);

然后只需按路径选择order：

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时，不要这样做！:)

Answer 2

嵌套树集与level列相结合是一种非常好的基于树的结构读取和排序技术。很容易选择子树，将结果限制在一定的级别，并在一个查询中进行排序。但是插入和删除实体的成本相对较高，因此，如果查询数据比编写数据更频繁，并且阅读性能很重要，则应该使用它。(对于50-100年，移除、插入或移动元素的时间应该是没有问题的，即使有1000个元素也没有问题)。

对于存储的每个条目，它都是level以及left和right的值，在下面的示例中是：(left、right、level)如果您只想选择带有它的后代的1.2，那么应该这样做：

 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)

闭包表(用于完成，但我不推荐您的用例)。它将所有路径存储在树中，因此，如果您有多个级别，那么层次结构所需的存储空间将增长得非常快。

Path枚举--您在那里用条目/0/存储每个元素的路径，/0/1/查询路径在那里很容易，但是对排序来说并不那么灵活。

对于少量的实体，我将使用嵌套树集。遗憾的是，我没有一个很好的参考页面来描述和比较这些技术。

Answer 3

如果只有3个层次的嵌套，您可以这样做。

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