顺序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 或 名字 ：
“比萨饼” //节点1
“piperoni” //节点1.1
“奶酪” //节点1.2
“额外的奶酪” //节点1.2.1
“vegetariana” //节点1.3
“汉堡” //节点2
‘咖啡’//节点3

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

编辑2： 正如多次提到的… name“奶酪 1.2 ”
末尾的数字仅用于可视化目的，而不用于排序。我以评论为由动了他们，太多人感到困惑，抱歉。

Answer 1

通过添加路径列和触发器，可以很容易地做到这一点。

首先添加一个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时；好吧，不要那样做！:)