dump SQL Table structure and data

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for demo
-- ----------------------------
DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `parent_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- 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:

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

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

SELECT 
        @r AS _id, 
        (SELECT @r := parent_id FROM demo WHERE id = _id) AS parent_id, 
        @l := @l + 1 AS lvl 
    FROM 
        (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

SELECT ，
        @a AS _id, 
        (SELECT @a := parent_id FROM demo WHERE id = _id) AS parent_id
    FROM 
        (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

Article ending

about MYSQL Query order of

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP By
6. CUBE|ROllUP
7. HAVING
8. SELECT
9. DISTINCT
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