mysql - 顺序 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
'vegetariana'//节点 1.3
'汉堡'//节点2
'咖啡'//节点3

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

编辑 2: 正如多次提到的那样......名称“奶酪 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

见 fiddle .

这样维护成本也很低。路径字段在插入时隐藏，通过触发器计算。删除一个节点没有开销，因为该节点的所有子节点也都被删除了。唯一的问题是更新节点的 parent_id 时；好吧，不要那样做! :)