MySQL存储过程来订购链表

I have a table with a representation of a linked list

我有一个表格链表的表格

id | parent
1  | 1
2  | 1
3  | 2
4  | 3
...

I know for a fact that mysql does not have the tree building capabilities built in for queries but I was wondering if anyone had used stored procedures to perform this sorting. Currently I am performing the sorting in my domain code but the table as to be read by an external system and performance is paramount on the external system so I would like to provide it with an already sorted list to not use its the precious CPU cycles.

我知道mysql没有为查询内置的树构建功能，但我想知道是否有人使用存储过程来执行此排序。目前我正在我的域代码中执行排序，但该表将由外部系统读取，并且性能在外部系统上是最重要的，因此我想为它提供已经排序的列表，以便不使用其宝贵的CPU周期。

Other advices are welcomed as this is still in the design phase.

其他建议受到欢迎，因为它仍处于设计阶段。

2 个解决方案

#1

MySQL doesn't have the handy START WITH / CONNECT BY syntax that Oracle does, however, you might want to check out this link from someone who tried to mimic this same functionality in MySQL.

MySQL没有Oracle提供的方便的START WITH / CONNECT BY语法，但是，您可能希望从试图模仿MySQL中相同功能的人那里查看此链接。

#2

I already solved this in the DBA StackExchange on October 24, 2011 : https://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7161#7161

我已经在2011年10月24日的DBA StackExchange中解决了这个问题：https：//dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/ 7161＃7161

In my answer, I included the stored procedures and sample data based on that question.

在我的回答中，我根据该问题包含了存储过程和示例数据。

Give it a Try !!!

试一试！！！

#1