MySQL multi-level classification storage mode (product classification, article classification)

In daily work, we often encounter product classification, article classification and so on 修改不频繁 multi-level classification. The usual approach is similar to this structure:

General Practice

If you follow a multilevel query, you can use the following SQL statement:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

Disadvantages:

The code is as follows	Copy Code
Let's take a real example--the product category has a total of three levels, all the products are related to the classification of the third level leaf node. Now the requirement is to search through the top categories for all the products that are included in this large category.

Left value Right value method

Let's take a look at the way it works.

We use nested way to express multi-layer relations, when the table is added to the left and right values, the structure is as follows:

At that time you will certainly ask, how is the value of the left and right calculated? Take a look at the following picture:

It is estimated that after you read these two pictures, you will understand the skills, then we have to see what the benefits of such a structure?

We're going to solve the problem of the product classification in the conventional approach.

The code is as follows	Copy Code
Suppose that the top-level classification selects the root node and then queries all of its products Select category_id from category where Lft>1 and rgt<20

If you want to query the child tree is the same reason ~

The code is as follows	Copy Code
To expand, you can also add the parent Node ID field to each record, so that you have the properties of the underlying method

Disadvantages:

The code is as follows	Copy Code
If you want to add and remove a node, you need to recalculate all nodes for the left value of the right value!!!