MySql infinite classification data structure -- pre-sorted traversal tree algorithm _ MySQL

MySql infinite classification data structure -- pre-sorting traversal tree algorithm bitsCN.com

Unlimited classification is a very common application in our development, such as Forum Sections, CMS categories, and many applications.
The most common and simple method is ID, parentID, and name in MySql. The advantage is that the structure is simple. The disadvantage is that the efficiency is not high, because the database needs to be queried every recursion, and hundreds of pieces of data are not very fast!

The storage tree 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:

Next we will use another method, which is the pre-sorted traversal tree algorithm (modified preorder tree traversal algorithm)
This method is rarely used and is not easy to understand for the first time. However, because this method does not use recursive query algorithms, it has 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 child nodes of "Fruit" 2-11.

:

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;



As you can see, 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;

How many child nodes does a node have? Very simple, descendant total = (right-left-1)/2
Descendants = (right-left-1)/2. if you are not clear about this formula, go to the book and write it clearly in the data structure!

The method for adding nodes at the same level is as follows:

Lock table nested_category WRITE;


SELECT @ myRight: = rgt FROM nested_category
WHERE name = 'cherry ';



UPDATE nested_category SET rgt = rgt + 2 WHERE rgt> @ myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft> @ myRight;

Insert into nested_category (name, lft, rgt) VALUES ('Strawberry ', @ myRight + 1, @ myRight + 2 );

Unlock tables;

The method for adding a subnode to a tree is as follows:

Lock table nested_category WRITE;

SELECT @ myLeft: = lft FROM nested_category

WHERE name = 'beef ';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt> @ myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft >@myleft;

Insert into nested_category (name, lft, rgt) VALUES ('charqui', @ myLeft + 1, @ myLeft + 2 );

Unlock tables;

After each node is inserted, you can use the following SQL statement to view and verify it:

Select concat (REPEAT ('', (COUNT (parent. name)-1), node. name) AS name
FROM nested_category AS node,
Nested_category AS parent
WHERE node. lft BETWEEN parent. lft AND parent. rgt
Group by node. name
Order by node. lft;

To delete a node, there is an intermediate variable, as follows:

Lock table nested_category WRITE;


SELECT @ myLeft: = lft, @ myRight: = rgt, @ myWidth: = rgt-lft + 1
FROM nested_category
WHERE name = 'cherry ';


Delete from nested_category WHERE lft BETWEEN @ myLeft AND @ myRight;


UPDATE nested_category SET rgt = rgt-@ myWidth WHERE rgt> @ myRight;
UPDATE nested_category SET lft = lft-@ myWidth WHERE lft> @ myRight;

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 still worthwhile relative to efficiency. this discovery made me discover that the database structure is really useful, but I basically forgot the tree I learned at school, this problem is applied to the project!

References:
Http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Http://www.sitepoint.com/article/hierarchical-data-database/3/

BitsCN.com