Mysql tree structure and mutual conversion, mysql Tree Structure

Mysql tree structure and mutual conversion, mysql Tree Structure

Mysql implements a tree structure. There are two common methods in databases: receiving tables and pre-sorting traversal trees (MPTT ).
Receiving table method --
It mainly depends on a parent field, which is used to point to the upper-level node and connect adjacent upper and lower-level nodes. The id is auto-incrementing, And the parent_id is the id of the upper-level node.
The advantages of the table picking method are that it is easy to understand and the code is relatively simple. The disadvantage is that recursive SQL queries will increase the load, especially when a large tree structure needs to be processed, the query statements will increase as the hierarchy increases, the bottlenecks of WEB applications are basically in the database aspect. Therefore, this is a fatal drawback, which directly leads to difficulties in tree structure expansion.
Sort the traversal tree
Now let's talk about the second method-the pre-sorted Traversal Tree (namely, the MPTT, Modified Preorder Tree Traversal ). Based on the first method, this algorithm adds a left and right number to each node to identify the traversal sequence of the node, as shown in:

From the root node, the left side is 1, then the left side of the next node is 2, and so on. After arriving at the lowest layer node, the right side of the lowest layer node adds 1 to the left side of the node. Following these nodes, we can easily traverse the entire tree. We made some changes to the data table and added two fields. lft and rgt are used to store the left and right numbers (left and right are reserved words of MySQL, so they are abbreviated ).

It can be seen that the MPTT storage method not only contains the affiliation but also the order, so it does not need to be recursive when reading the subtree, which greatly improves the efficiency.

Next we will discuss how to convert the two.

MPTT is easier to switch to a table. As long as the search level is 1 smaller than the current node, and the lft <current node lft, rgt> current node rgt node is the parent node.

The process of converting a receiving table to MPTT is typically generated recursively. However, this is not tail recursion. there are limits on the number of recursive layers. mysql does not have an array of self-built stacks to use tables, and the efficiency is very low. What should I do?

I have designed an approximate recursive algorithm to share with you:

First, determine the problem: the access table structure (id, pid), the target MPTT table structure (id, lvl, lft, rgt ).

For processing purposes, the MPTT table adds the cnt and seq fields to record the number of nodes and Their subnodes and the serial numbers traversed in MPTT.

The processing algorithm is as follows:

1] The root node is transferred to the MPTT table so that lvl = 1, lft = 1, rgt = null, cnt = null, seq = 1;

2] layer-by-layer processing of p subnodes, lvl + 1;

3. process the nodes at each layer from the bottom layer (lvl maximum) to the top (lvl decreasing). cnt = Number of child nodes + 1

4] from the top (lvl = 1) Down (lvl increments) processing nodes at each layer, seq = parent node seq + sum (id less than the sibling node cnt of the current node) + 1

5] for each node, lft = seq * 2-lvl, rgt = lft + cnt * 2-1

Processing is complete;

This algorithm has been applied in the project, and the code is copyrighted and will not be pasted.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.