为物化路径树结构生成路径模式的最佳方法
浏览网络上的示例，我可以看到人们使用“parent_id.node_id”之类的东西生成路径。示例：-
uid | name | tree_id
--------------------
1 | Ali | 1.
2 | Abu | 2.
3 | Ita | 1.3.
4 | Ira | 1.3.
5 | Yui | 1.3.4
但正如这个问题所解释的 - 使用物化路径对树进行排序？，对 tree_id 使用零填充可以轻松地按创建顺序对其进行排序。
uid | name | tree_id
--------------------
1 | Ali | 0001.
2 | Abu | 0002.
3 | Ita | 0001.0003.
4 | Ira | 0001.0003.
5 | Yui | 0001.0003.0004
使用像这样的固定长度字符串也使我可以轻松计算级别 - length(tree_id)/5。我担心的是它会将我限制为最多 9999 个用户，而不是每个分支 9999 个。我在这儿吗？
9999 | Tar | 0001.9999
10000 | Tor | 0001.??
Browsing through examples all over the web, I can see that people generate the path using something like "parent_id.node_id". Examples:-
uid | name | tree_id
--------------------
1 | Ali | 1.
2 | Abu | 2.
3 | Ita | 1.3.
4 | Ira | 1.3.
5 | Yui | 1.3.4
But as explained in this question - Sorting tree with a materialized path?, using zero padding to the tree_id make it easy to sort it by the creation order.
uid | name | tree_id
--------------------
1 | Ali | 0001.
2 | Abu | 0002.
3 | Ita | 0001.0003.
4 | Ira | 0001.0003.
5 | Yui | 0001.0003.0004
Using fix length string like this also make it easy for me to calculate the level - length(tree_id)/5. What I'm worried is it would limit me to maximum 9999 users rather than 9999 per branch. Am I right here ?
9999 | Tar | 0001.9999
10000 | Tor | 0001.??
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
你是对的——用零填充每个节点 ID 可以让你非常简单地对整个树进行排序。但是，您必须使填充宽度与 ID 字段的数字上限相匹配，正如您在上一个示例中所指出的那样。例如，如果您使用
int unsigned字段作为 ID，则最大值将为 4,294,967,295。这是十位数字，意味着上一个示例中的记录集可能如下所示：只要您知道将来不需要将 ID 字段更改为
bigint unsigned，这将继续工作，尽管它可能有点需要数据，具体取决于您的表有多大。您可以通过以十六进制存储值来减少每个节点 ID 的两个字节，这些值仍然可以在字符串排序中正确排序：我可以想象，当尝试更新路径（修剪节点等）时，这会让事情变得真正令人头痛。
您还可以创建额外的字段进行排序，例如：
但是，正如这个人对类似物化路径排序的回答所述，存在一些限制问题。
name字段必须填充到设定的长度（幸运的是，在您的示例中，每个名称似乎都是三个字符长），并且它将占用大量空间。总之，考虑到上述问题，我发现进行此类排序的最通用方法是简单地在应用程序逻辑中进行排序 - 例如，使用构建嵌套数组的递归函数，对每个数组的子元素进行排序节点随着它的运行而变化。
You are correct -- zero-padding each node ID would allow you to sort the entire tree quite simply. However, you have to make the padding width match the upper limit of digits of the ID field, as you have pointed out in your last example. E.g., if you're using an
int unsignedfield for your ID, the highest value would be 4,294,967,295. This is ten digits, meaning that the record set from your last example might look like:As long as you know you're not going to need to change your ID field to
bigint unsignedin the future, this will continue work, though it might be a bit data-hungry depending on how huge your tables get. You could shave off two bytes per node ID by storing the values in hexadecimal, which would still be sorted correctly in a string sort:I can imagine this would make things a real headache when trying to update the paths (pruning nodes, etc) though.
You can also create extra fields for sorting, e.g.:
There are limitations, however, as laid out by this guy's answer to a similar materialized path sorting question. The
namefield would have to be padded to a set length (fortunately, in your example, each name seems to be three characters long), and it would take up a lot of space.In conclusion, given the above issues, I've found that the most versatile way to do sorting like this is to simply do it in your application logic -- say, using a recursive function that builds a nested array, sorting the children of each node as it goes.