Realization of MySQL Infinite class classification

The first scenario:
The use of recursive algorithms is also the most frequently used, most open-source programs are the same, but generally only use the level four classification. The database structure design of this algorithm is the simplest. A field ID in the Category table, a field FID (parent ID). This allows you to judge the top level of content based on where id = FID, using recursion to the topmost level.
Analysis: Through this kind of database design infinite level, can say reads the time is quite laborious, so most of the program up to 3-4 level classification, this is enough satisfies the demand, thus reads all the data at once, then obtains the array or the object to recursively. The load itself is not too much of a problem. But if you classify it to more levels, that's not a desirable approach.
So it seems that this classification has a benefit, is to delete and change the time relaxed ... However, in the case of level two classification, the use of this algorithm should be considered the highest priority.

The second scenario:
Set the FID field type to varchar and the parent class ID in this field, separated by symbols, for example: 1,3,6
This can be relatively easy to get the ID of each parent classification, and in the query classification of information under the time,
Can be used: SELECT * from category WHERE pid like "1,3%".

Analysis: Compared to the recursive algorithm, the advantage of reading data is very large, but if the search for the classification of all the parent classification or sub-classification query efficiency is not very high, at least two times query, from a certain point of view, the personal feel less in line with the design of the database paradigm. If you increment to an infinite level, you also need to consider whether the field meets the requirements, and the operation will be cumbersome when you modify the classification and transfer classification.
For the time being, a solution similar to the second scenario is used in your own project. In the case of this program in my project there is such a problem, if all the data records reached tens of thousands or even more than 10W, one-time will be so classified, orderly classification of the reality out, the efficiency is very low. It is very likely that the project processing data code is inefficient. is now being improved.
The third scenario:
An infinite class classification----improved pre-sequence traversal tree
So what are the characteristics of the ideal tree structure? Data storage redundancy is small, intuitive, and easy to return the entire tree structure data; it is easy to return a subtree (for easy layering); fast access to a node's ancestor spectrum path, insert, delete, move node high efficiency and so on. With these demands. I have found a lot of data, discovered a kind of ideal tree-type structure storage and operation algorithm, improved the pre-sequence traversal tree model (the Nested Set model).
Principle:
Let's lay out the trees in a horizontal way first. Start with the root node ("food"), and then his left is written on 1. Then write 2 to the left of "Fruit" in the Order of the tree (top to bottom). So you walk along the edge of the tree (this is "traversal"), and then write numbers on the left and right sides of each node. Finally, we go back to the root node "food" on the right to write 18. The following is a tree labeled with numbers, and the order of traversal is marked with arrows.

We call these numbers the left and right values (for example, the left value of "food" is 1 and the right value is 18). As you can see, these numbers are on time for the relationship between each node. Because "Red" has 3 and 62 values, it is followed by a "food" node that has a value of 1-18. Similarly, we can infer that all nodes with an lvalue greater than 2 and a right value of less than 11 are all followed by a 2-11 "Fruit" node. In this way, the structure of the tree is stored by the left and right values. This method of counting the entire tree node is called "improved pre-sequence traversal tree" algorithm.

Table structure Design:

So how can we use a SQL statement to all the categories are queried, and if it is a subclass of the front to hit a few spaces to express the sub-classification. It's good to check out all the categories: SELECT * from category WHERE lft>1 and lft<18 ORDER by LFT so all the classifications are out, but who is the sub-class but not clear, then how to do? We look closely at the diagram is not difficult to find if the right value of the next two records of the right value is larger than the second one is his parent class, such as the right value of food is 18 and the right value of fruit is 11 then food is the parent class of fruit, but also consider the multilevel directory. So with this design, we use an array to store the right value of the previous record, and then compare it to the right value of this record, if the former is smaller than the latter, the description is not a parent-child relationship, the array_pop pops up the array, otherwise it will be preserved, then the size of the array to print the space. This solves the problem. The code is as follows
Table structure:

Copy CodeThe code is as follows:
--
--The structure of the table ' category '
--
CREATE TABLE IF not EXISTS ' category ' (
' id ' int (one) not NULL auto_increment,
' Type ' int (one) not NULL COMMENT ' 1 for Article Type 2 for product Type 3 for download type ',
' title ' varchar (not NULL),
' LfT ' int (one) is not NULL,
' RGT ' int (one) is not NULL,
' Lorder ' int (one) not NULL COMMENT ' sort ',
' Create_time ' int (one) is not NULL,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8 auto_increment=10;
--
--Export the data in the table ' category '
--
INSERT into ' category ' (' id ', ' type ', ' title ', ' LfT ', ' rgt ', ' lorder ', ' create_time ') VALUES
(1, 1, ' Top column ', 1, 18, 1, 1261964806),
(2, 1, ' Company Profile ', 14, 17, 50, 1264586212),
(3, 1, ' News ', 12, 13, 50, 1264586226),
(4, 2, ' Company Products ', 10, 11, 50, 1264586249),
(5, 1, ' honorary credentials ', 8, 9, 50, 1264586270),
(6, 3, ' Data download ', 6, 7, 50, 1264586295),
(7, 1, ' recruitment ', 4, 5, 50, 1264586314),
(8, 1, ' Message board ', 2, 3, 50, 1264586884),
(9, 1, ' president ', 15, 16, 50, 1267771951);

/**
* Show the tree and display all the nodes.
* 1, first get the root node of the left and right values (the default root nodes title is "Top Directory").
* 2, query the left and right values in the root node of the value of the record within the range, and sorted by the left value.
* 3, if the right value of this record is greater than the last record of the right value is sub-classification, output time with a space.
* @return Array
**/
function Display_tree () {

Get root left and right values
$arr _LR = $this->category->where ("title = ' top Column '")->find ();
Print_r ($arr _LR);
if ($arr _lr) {
$right = Array ();
$arr _tree = $this->category->query ("Select ID, type, title, RGT from category WHERE LfT >=". $arr _lr[' LfT ']. " and LfT <= ". $arr _lr[' RGT ']." ORDER by LfT ");
foreach ($arr _tree as $v) {
if (count ($right)) {
while ($right [count ($right)-1] < $v [' RGT ']) {
Array_pop ($right);
}
}
$title = $v [' title '];
if (count ($right)) {
$title = ' | '. $title;
}
$arr _list[] = array (' id ' = = $v [' id '], ' type ' = + $type, ' title ' = = Str_repeat (', Count ($right)). $title, ' name ' = = $v [' title ']);
$right [] = $v [' RGT '];
}
return $arr _list;
}
}

All right, so long as all the classifications can be queried at once, instead of passing recursion.
The following question is how to insert, delete, and modify operations
Insert: Insert operation is very simple to find its parent node, and then the left and right values are greater than the left of the parent node value plus 2, and then insert this node, the left value of the parent node, respectively, plus one and plus two, you can use a stored procedure to operate:

Copy CodeThe code is as follows:
CREATE PROCEDURE ' category_insert_by_parent ' (in PID int,in title VARCHAR (a), in type INT, in L_order int, in Pubtime int)
BEGIN
DECLARE Myleft INT;
SELECT LfT to Myleft from category WHERE id= pid;
UPDATE qy_category SET RGT = rgt + 2 WHERE rgt > myleft;
UPDATE qy_category SET lft = lft + 2 WHERE lft > myleft;
INSERT into Qy_category (type, title, LfT, RGT, Lorder, Create_time) VALUES (type, title, Myleft + 1, Myleft + 2, L_order, p Ubtime);
Commit
END

Delete operation:
Principle of deletion: 1. Get the left and right values of the nodes to be deleted, and get their difference plus one, @mywidth = @rgt-@lft + 1;
2. Delete the left and right values between the nodes of this node
3. Modify the condition to be greater than the right value of this node all the nodes, the operation is to put their left and @mywidth minus the value of
The stored procedures are as follows:

Copy CodeThe code is as follows:
CREATE PROCEDURE ' Category_delete_by_key ' (in ID INT)
BEGIN
SELECT @myLeft: = LfT, @myRight: = RGT, @myWidth: = rgt-lft + 1
From category
WHERE id = ID;
DELETE from 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;

Modify:
Kill the modification operation, I looked for a long time also did not see what the rule out, as long as this is the worst way, first delete and then insert, as long as the call above 2 stored procedures on it!
Summary: The query is convenient, but the addition and deletion of the operation is a bit cumbersome, but the general classification of this kind of operation is not a lot, or query with more, and then get a stored procedure is also convenient!

The third scenario above specifically explains the class capacity is copied from http://home.phpchina.com/space.php?uid=45095&do=blog&id=184675, convenient to view later. For the time being, it is biased towards the third proposal from various aspects and theoretically. However, there has not been a test, how efficient.
Looking for a better solution!

Realization of MySQL Infinite class classification