How mysql can be classified infinitely

Solution 1:
Recursive Algorithms are also the most frequently used. Most open-source programs do the same, but generally only use level 4 classification. The database structure design of this algorithm is the simplest. Id of a field in the category table, and fid of a field (parent id ). In this way, you can determine the upper level of content based on WHERE id = fid, and apply recursion to the top layer.
Analysis, then recursion is performed on the obtained array or object. The load is not too high. However, it is not advisable to classify data into more levels.
In this way, it seems that this category has the advantage of being easy to add, delete, and modify... However, for level-2 classification, this algorithm should be the highest priority.

Solution 2:
Set the fid field type to varchar. Set the parent class id to this field and separate them with symbols, such as and 6.
In this way, it is easier to obtain the ID of each parent category, and when querying the information under the category,
You can use: SELECT * FROM category WHERE pid LIKE "1, 3% ".

Analysis: Compared with recursive algorithms, it has great advantages in reading data. However, if the query efficiency for all parent or subcategories of the classification is not very high, at least two queries are required, in a sense, I personally think it is not in line with the design of the database paradigm. If the number of fields increases to an infinite number, you also need to consider whether the field meets the requirements, and it will be very troublesome to modify and transfer the category.
Currently, the solution similar to the second solution is used in your project. This solution has such a problem in my project. If all the data records reach tens of thousands or even more than 10 million, the classification and orderly classification will come out at a time, and the efficiency is very low. It is very likely that the efficiency of project data processing code is low. Improvements are being made.
Solution 3:
Unlimited classification-improved the pre-order traversal tree
What are the characteristics of the ideal tree structure? Small Data Storage redundancy and strong intuition; easy to return the entire tree structure data; can easily return a child tree (convenient for hierarchical loading ); quickly obtain the original spectrum path of a node; insert, delete, and move nodes with high efficiency. With these requirements, I found a lot of information and an ideal tree-structure data storage and operation algorithm, and improved The Nested Set Model ).
Principle:
First, we should open the tree horizontally. Start from the root node ("Food") and write 1 on the left. Then write 2 to the left of "Fruit" in the tree Order (from top to bottom. In this way, you can go along the boundary of the tree (this is "traversal") and write numbers on the left and right sides of each node at the same time. Finally, we return to the root node "Food" and write 18 on the right. Below 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 reflect the relationship between each node on time. Because "Red" has 3 and 6 values, it is followed by a "Food" node with 1-18 values. Similarly, we can infer that all nodes with a left value greater than 2 and a right value less than 11 will be followed by "Fruit" nodes with 2 to 11. In this way, the tree structure is stored through the left and right values. This method for calculating nodes in the entire tree is called the "improved forward traversal Tree" algorithm.

Table Structure Design:

Then, how can we use an SQL statement to query all the categories? In addition, if it is a subclass, we need to enter a few spaces to represent subcategories. It is easy to query all the categories: SELECT * FROM category WHERE lft> 1 AND lft <18 order by lft, all the categories will be available, but who is the sub-class but cannot tell clearly, what should we do? It is not difficult to figure out that if the first right value of the two adjacent records is larger than that of the second record, it is his parent class, for example, if the right value of food is 18 and the right value of fruit is 11, then food is the parent class of fruit, but multi-level directories must be considered. So with this design, we use an array to store the right value of the previous record, and then compare it with the right value of this record. If the former is smaller than the latter, if it is not a parent-child relationship, array_pop is used to pop up the array. Otherwise, the array is retained and spaces are printed Based on the array size. This solves this problem. The Code is as follows:
Table Structure:Copy codeThe Code is as follows :--
-- Table structure 'category'
--
Create table if not exists 'category '(
'Id' int (11) not null AUTO_INCREMENT,
'Type' int (11) not null comment '1 is Article type 2 is product type 3 is download type ',
'Title' varchar (50) not null,
'Lft 'int (11) not null,
'Rgt 'int (11) not null,
'Lorder 'int (11) not null comment' sorting ',
'Create _ time' int (11) not null,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8 AUTO_INCREMENT = 10;
--
-- Export the data 'category' In the table'
--
Insert into 'category '('id', 'type', 'title', 'lft', 'rgt ', 'lorder', 'create _ Time') VALUES
(1, 1, 'top-level topic ', 1, 18, 1, 1261964806 ),
(2, 1, 'Company profile ', 14, 17, 50,126 4586212 ),
(3, 1, 'News', 12, 13, 50,126 4586226 ),
(4, 2, 'Company product', 10, 11, 50,126 4586249 ),
(5, 1, 'honorary qualification ', 8, 9, 50,126 4586270 ),
(6, 3, 'Data downloads', 6, 7, 50,126 4586295 ),
(7, 1, 'talent recruiters ', 4, 5, 50,126 4586314 ),
(8, 1, 'guest', 2, 3, 50,126 4586884 ),
(9, 1, 'President', 15, 16, 50,126 7771951 );

/**
* Display tree: displays all nodes.
* 1. Obtain the left and right values of the root node (the title of the root node is "top-level directory" by default ").
* 2. query records whose left and right values are within the left and right value ranges of the root node, and sort the records by the Left value.
* 3. If the right value of the current record is greater than the right value of the previous record, it is a subcategory and a space is added to the output.
* @ Return array
**/
Function display_tree (){

// Obtain the left and right values of the root user
$ Arr_lr = $ this-> category-> where ("title = 'top topic '")-> 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, as long as all the categories can be queried at one time, instead of recursion.
The following question is how to insert, delete, and modify an object.
Insert: The insert operation is simple to find its parent node. Then, add 2 to the left and right values of the nodes with the left and right values greater than the left values of the parent node, and then insert the current node, the left and right values are the parent node's left values plus one and two respectively. You can use a stored procedure to perform the operation:Copy codeThe Code is as follows: create procedure 'category _ insert_by_parent '(IN pid INT, IN title VARCHAR (20), IN type INT, IN l_order INT, IN pubtime INT)
BEGIN
DECLARE myLeft INT;
SELECT lft into 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, pubtime );
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 nodes between the current node and the left and right values.
3. Set the condition to all nodes whose values are greater than the right of the current node. The operation is to subtract the left and right values from @ mywidth.
The stored procedure is 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:
I haven't seen any rule for a long time in the fatal modification operation. As long as this rule is made, delete it first and then insert it. Just call the above two stored procedures!
Summary: The query is convenient, but the addition, deletion, modification, and modification operations are a little complicated. However, there are not many such operations by category, but they are still used for query. It is also convenient to create a storage process!

The above third solution specific explanation class is from http://home.phpchina.com/space.php? Uid = 45095 & do = blog & id = 184675 copy the file so that you can view it later. Currently, the third solution is preferred in various aspects and in theory. However, no tests have been conducted to determine the efficiency.
Looking forward to better solutions!