MYSQL recursive query, query all the parent classes according to the subclass ID (the first detailed tutorial of the universe)


In the work, the business requirements encounter MYSQL recursion, and most of the online data are copied back and forth, although the code can indeed run. But Mengxin really can't understand what this SQL writes. Looking at the comments below, many people didn't reply to their questions. He spent a whole morning to summarize this point, hoping to give a little help to the Mengxin people like me when they encounter MYSQL recursion in their work and study.

Data table structure

id    name  parent_id 

1        A        0
2        B        1
3        C        1
4        D        2
5        E        4
6        F        1
7        G        1

Note: A parent u id of 0 indicates the top-level parent node, that is, A is the top-level parent, and there is no parent node


dump SQL Table structure and data


-- ----------------------------
-- Table structure for demo
-- ----------------------------
  `name` varchar(255) NOT NULL,
  `parent_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)

-- ----------------------------
-- Records of demo
-- ----------------------------
INSERT INTO `demo` VALUES ('1', 'A', '0');
INSERT INTO `demo` VALUES ('2', 'B', '1');
INSERT INTO `demo` VALUES ('3', 'C', '1');
INSERT INTO `demo` VALUES ('4', 'D', '2');
INSERT INTO `demo` VALUES ('5', 'E', '4');
INSERT INTO `demo` VALUES ('6', 'F', '1');
INSERT INTO `demo` VALUES ('7', 'G', '1');

Demand: according to a sub ID,Query all parent classes

SQL As follows:

        @r AS _id, 
        (SELECT @r := parent_id FROM demo WHERE id = _id) AS parent_id, 
        @l := @l + 1 AS lvl 
        (SELECT @r := 5, @l := 0) vars, 
        demo h 
    WHERE @r <> 0) T1 
JOIN demo T2 
ON T1._id = 

give the result as follows

id      name
1         A
2         B
4         D
5         E

Analysis process (Q & a process)

I use the method of splitting SQL statements, and show the results of each SQL statement, so that you can see more clearly, so as to make changes according to your own business needs

1,First ignore T2,First put T1 Of SQL Draw out

        @r AS _id, 
        (SELECT @r := parent_id FROM demo WHERE id = _id) AS parent_id, 
        @l := @l + 1 AS lvl 
        (SELECT @r := 5, @l := 0) vars, 
        demo h 
    WHERE @r <> 0

Operation result


From here, we can see that T1 has found all the parent nodes with ID 5, and connected left with T2 (that is, demo table), just to get Name according to ID. It can also be seen that @ l has no effect in the whole SQL, but it is used to identify the level of the node. The lvl of the lower level child node is 1. The larger the lvl value of the parent node is, the closer it is to the top level parent node. Imagine the tree structure, and you will see

2,Understand@r := 5 

SELECT @r := 5, @l := 0 

Operation result

It can be seen from this that @ r, @ l is just a variable,

As a variable, you can name it as you like or assign it as you like. You can change it to @ A and @ b as well


Then the meaning of SQL comes out. It means to assign value to variable @ r, value 5, assign value to @ l, value 0

Therefore, in fact, the meaning of the whole SQL is also clear, which is to query all the parent classes according to the child ID5

3,Understand variables@r How values change

 @r AS _id, 
(SELECT @r := parent_id FROM demo WHERE id = _id) AS parent_id

According to the execution order of MYSQL (at the end of the article), the SQL statement will execute From first, that is to say, the assignment operation of the previous step will be executed first. Therefore, the value of [id] here is 5, so the above SQL is equivalent to the following SQL

SELECT @r := parent_id FROM demo WHERE id = 5

Operation result


No one is sure about this SQL sentence, but it is very important. It is the core of the whole execution process, @ r: = parent_Id. when querying the parent Id of the node with Id 5, it assigns the parent Id to the variable @ r at the same time. So the @ r value changed, from 5 to 4.

At this time, the idea is very clear. As long as we set restrictions and let SQL end the loop when @ r is 0, it's OK

4,<>Meaning of symbols

WHERE @r <> 0

There's nothing to say about this. The symbol < > in MYSQL means not equal to. This is the restriction we mentioned in step 4. It limits the @ r variable not equal to 0. So when @ r is not equal to 0, the SQL statement will query the parent ID upward according to the child ID, assign the parent ID as the child ID to @ r, and query upward again until the value of @ r variable is 0.


Add: if the business does not need it, it can be completely removed@l variable

        @a AS _id, 
        (SELECT @a := parent_id FROM demo WHERE id = _id) AS parent_id
        (SELECT @a := 5) vars, 
        demo h 
    WHERE @a <> 0

Operation result


It can be seen that @ l doesn't play a role in the whole SQL. It is only used to identify the level of nodes, which also confirms our explanation of @ L in the first step

Because this process is very similar to JAVA's way of recursive query by constructing tree structure, this SQL form is called MYSQL recursive query by many people

Article ending

about MYSQL Query order of

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP By
  6. CUBE|ROllUP
  10. ORDER BY
  11. LIMIT

The first thing to do is FROM Operation, the last thing to do is LIMIT Operation. Each operation produces a virtual table that serves as an input to the process

Published 325 original articles, praised 149, visited 9875
Private letter follow

Keywords: SQL MySQL Java

Added by Zay on Tue, 21 Jan 2020 07:33:12 +0200