排序sql树层次结构

2022-01-05 00:00:00 sql tree mysql

像这样对表格进行排序的最佳方法是什么:

What is the best way to sort a table like this:

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
'奶酪'//节点 1.2
'额外的奶酪'//节点 1.2.1
'vegetariana'//节点 1.3
'汉堡'//节点2
'咖啡'//节点 3

To sort it hierarchically by id or name:
'pizza' //node 1
'piperoni' //node 1.1
'cheese' //node 1.2
'extra cheese' //node 1.2.1
'vegetariana' //node 1.3
'burger' //node 2
'coffee' //node 3

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

the number at the end of the name is to visualize the strucutre better, it is not for sorting.

EDIT 2: 多次提到......namecheese 1.2"末尾的数字仅用于可视化目的，不是为了排序.我把它们当评论了，太多人糊涂了，抱歉.

EDIT 2: as mentioned several times ... the number at the end of the name "cheese 1.2" was only for visualization purpose, NOT for sorting. I moved them as comments, too many people got confused, sorry.

推荐答案

通过添加路径列和触发器，这可以很容易地完成.

By adding a path column and a trigger, this can be done fairly easily.

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

First add a varchar column that will contain the path from root to the node:

ALTER TABLE category ADD path VARCHAR(50) NULL;

然后添加一个触发器来计算插入时的路径:

Then add a trigger that calculates the path on insert:

(简单地将新 id 与父路径连接起来)

(simply concats the new id with path of the parent)

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

然后只需按路径选择顺序:

Then simply select order by path:

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 时；好吧，不要那样做！:)

This way maintenance cost is also minimal. The path field is hidden when inserting and is calculated via trigger. Removing a node has no overhead, since all the children of the node are also removed. The only problem is when updating the parent_id of a node; Well, don't do that! :)

相关文章