Principle and Implementation of unlimited mysql left/right value Classification

The Principle and Implementation of the infinite classification of mysql left and right values is a very common application in our development. The most common and simplest method is id, name, f_id in MySql. The advantage is simplicity and simple structure. The disadvantage is that the efficiency is not high, because the database needs to be queried every recursion, and hundreds of databases are not very fast!
The storage tree www.2cto.com is a common problem with multiple solutions. There are two main methods: the model of the adjacent table and the algorithm of modifying the pre-tree traversal. We will explore the energy-saving data of these two methods. I will use a tree from a fictitious online food store as an example. This Food Store organizes its food categories by color and type. This tree looks like this:
Under www.2cto.com, we will use another method, which is the modified preorder tree traversal algorithm method, the first use is not as easy to understand as the above method, but because this method does not use recursive query algorithms, it has a higher query efficiency. First, we will draw the multilevel data on the paper in the following way, write 1 on the left side of the root node Food, and then write 2 down the tree to the left side of the Fruit, and then proceed, along the edge of the entire tree, each node is marked with numbers on the left and right. The last digit is 18 on the right of the Food. In the figure below, you can see the multilevel structure of the entire labeled number. (Not understood? Point your finger at the number from 1 to 18 to see what's going on. I still don't understand. I'll try again. Pay attention to moving your finger ). These numbers indicate the relationship between each node. "Red" numbers are 3 and 6, and it is the child node of "Food" 1-18. Similarly, we can see that all nodes with a left value greater than 2 and a right value less than 11 are "Fruit" 2-11 child nodes: in this way, the entire tree structure can be stored in the database through left and right values. Before proceeding, let's take a look at the data tables that have been organized below. Note: Because "left" and "right" have special meanings in SQL, we need to use "lft" and "rgt" to represent left and right fields. In addition, the "parent" field is no longer required to represent the tree structure. That is to say, the following table structure is enough. SELECT * FROM tree WHERE lft BETWEEN 2 AND 11; all these nodes can be obtained through a single query. In order to display the entire tree structure as the recursive function above, we also need to sort such queries. Sort BY the Left value of the node: SELECT * FROM tree WHERE lft BETWEEN 2 AND 11 order by lft ASC; then how many child nodes does a node have? Very simple, the total number of children = (right-left-1)/2 descendants = (right-left-1)/2. If you do not know this formula, let's go and read the book. We wrote it clearly in the data structure! The method for adding nodes at the same level is as follows: SQL code 1. LOCK TABLE nested_category WRITE;
2.
3.
4. SELECT @ myRight: = rgt FROM nested_category
5. WHERE name = 'cherry ';
6.
7.
8.
9. UPDATE nested_category SET rgt = rgt + 2 WHERE rgt> @ myRight;
10. UPDATE nested_category SET lft = lft + 2 WHERE lft> @ myRight;
11.
12. insert into nested_category (name, lft, rgt) VALUES ('strawberry ', @ myRight + 1, @ myRight + 2 );
13.
14. unlock tables; the method for adding subnodes to a tree is as follows: SQL code 1. LOCK TABLE nested_category WRITE;
2.
3. SELECT @ myLeft: = lft FROM nested_category
4.
5. WHERE name = 'beef ';
6.
7. UPDATE nested_category SET rgt = rgt + 2 WHERE rgt> @ myLeft;
8. UPDATE nested_category SET lft = lft + 2 WHERE lft> @ myLeft;
9.
10. insert into nested_category (name, lft, rgt) VALUES ('charqui', @ myLeft + 1, @ myLeft + 2 );
11.
12. unlock tables. You can use the following SQL statement to view and verify each time you insert a node: SQL code 1. select concat (REPEAT ('', (COUNT (parent. name)-1), node. name) AS name
2. FROM nested_category AS node,
3. nested_category AS parent
4. WHERE node. lft BETWEEN parent. lft AND parent. rgt
5. group by node. name
6. order by node. lft; the method for deleting a node is a little troublesome. There is an intermediate variable, as follows: SQL code 1. LOCK TABLE nested_category WRITE;
2.
3.
4. SELECT @ myLeft: = lft, @ myRight: = rgt, @ myWidth: = rgt-lft + 1
5. FROM nested_category
6. WHERE name = 'cherry ';
7.
8.
9. delete from nested_category WHERE lft BETWEEN @ myLeft AND @ myRight;
10.
11.
12. UPDATE nested_category SET rgt = rgt-@ myWidth WHERE rgt> @ myRight;
13. UPDATE nested_category SET lft = lft-@ myWidth WHERE lft> @ myRight;
14.
15. unlock tables;
This method is a bit difficult to understand, but it is suitable for the use of a large amount of data. You only need two SQL statements to view all the structures. It is a little troublesome to add and delete nodes, however, it is worthwhile for efficiency.