Detailed explanation of 25 lines of code to implement mysql tree query

This article mainly shares with you the detailed explanation of the 25-line mysql tree query code. I hope it can help you.

Requirement: Find all child nodes under the current (any) level.

Achieved through custom mysql function, paste the code first, and give detailed instructions later:

delimiter $$
CREATE FUNCTION `getChildList`(rootId INT)
RETURNS varchar(1024)
BEGIN
    DECLARE childListStr VARCHAR(1024);
    DECLARE tempChildStr VARCHAR(1024);
    DECLARE rootIdStr VARCHAR(64);
    SET childListStr=NULL;
    SET rootIdStr=cast(rootId as CHAR);
    myloop: WHILE TRUE
    DO
        SELECT GROUP_CONCAT(id) INTO tempChildStr FROM test where FIND_IN_SET(parrent_id,rootIdStr)>0;
        IF tempChildStr IS NOT NULL THEN
            SET rootIdStr=tempChildStr;
            IF childListStr IS NULL THEN
                SET childListStr=tempChildStr;
            ELSE
                SET childListStr=concat(childListStr,',',tempChildStr);
            END IF;
        ELSE
            LEAVE myloop;
        END IF;
    END WHILE;
  RETURN childListStr;
END $$

Create table sql:

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parrent_id` int(11) DEFAULT '0',
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| parrent_id | int(11)          | YES  |     | 0       |                |
| name       | varchar(32)      | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

+----+------------+------+
| id | parrent_id | name |
+----+------------+------+
|  1 |          0 | cg1  |
|  2 |          1 | cg2  |
|  3 |          2 | cg3  |
|  4 |          3 | cg4  |
|  5 |          4 | cg5  |
|  6 |          5 | cg6  |
|  7 |          6 | cg7  |
|  8 |          7 | cg8  |
|  9 |          8 | cg9  |
| 10 |          1 | cg10 |
| 11 |          2 | cg11 |
+----+------------+------+

Line 1:
delimiter writing When writing the content of the function body, you need to use the DELIMITER keyword to change the separator to something else. Otherwise, when writing the statement, it will be executed directly, causing the function to fail.

Lines 2-4 : mysql function syntax specification, no further explanation

5-9 lines: Define variables required for logic.

childListStr: The final returned child node ids_str (for example: "1,2,3,4,5").
tempChildStr: Temporary child node ids_str (for example: "1").
rootIdStr: Convert the input root node to char type.

10-23 lines: The most critical part of the entire function is the processing of tempChildStr in while, and the understanding of the built-in functions GROUP_CONCAT and FIND_IN_SET

每一次循环,通过 GROUP_CONCAT函数找出输入的根节点的直接下级节点，通过GROUP_CONCAT函数得到这些子节点的id组成的字符串。并将这次得到的子字符串作为根节点，去寻找下一级的所有的子节点。
最后找到最后子节点没有下级时候，tempChildStr IS NOT NULL。退出循环，返回结果。

Running results:

mysql> select getChildList(1);
+-----------------------+
| getChildList(1)       |
+-----------------------+
| 2,10,3,11,4,5,6,7,8,9 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select getChildList(2);
+------------------+
| getChildList(2)  |
+------------------+
| 3,11,4,5,6,7,8,9 |
+------------------+
1 row in set (0.00 sec)

Related recommendations:

SQL statement to implement descendant tree query classic example

mysql-Using MYSQL to implement oracle start with connect by recursive tree query

Oracle tree query and related functions

The above is the detailed content of Detailed explanation of 25 lines of code to implement mysql tree query. For more information, please follow other related articles on the PHP Chinese website!