Shulou(Shulou.com)06/01 Report--

This article is about implementing the function of mysql tree query. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

Requirements: find all child nodes at the current (any) level.

It is implemented by customizing the mysql function, and the code is posted first, followed by a detailed description:

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 the table sql:

CREATE TABLE `test` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `parrent_ id` int (11) DEFAULT '0mm, `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 | cg1 | 2 | 1 | cg2 | 3 | 2 | cg3 | 4 | 3 | cg4 | 5 | 4 | cg5 | 6 | 5 | cg6 | 7 | 6 | cg7 | 8 | cg8 | 9 | 8 | cg9 | | 10 | 1 | cg10 | | 11 | 2 | cg11 | +-- +

Line 1:

When delimiter writes the content of the function body, you need to use the DELIMITER keyword to change the delimiter to something else, otherwise it will be directly executed when writing the statement, resulting in the failure of function writing.

Line 2-4: mysql function syntax specification, not much explanation

Lines 5-9: define the variables required by the 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: the input root node is converted to type char.

Line 10-23: the most critical part of the whole function is the handling of tempChildStr in while, and the understanding of the built-in functions GROUP_CONCAT and FIND_IN_SET

In each loop, the direct subordinate nodes of the input root nodes are found through the GROUP_CONCAT function, and the string composed of the id of these child nodes is obtained by the GROUP_CONCAT function. And take the substring obtained this time as the root node to find all the child nodes of the next level. Finally, when the last child node has no subordinate, tempChildStr IS NOT NULL. Exit the loop and return the result.

Running result:

Mysql > select getChildList (1); +-+ | getChildList (1) | +-- + | 2 row in set 10, 3, 11, 4, 5, 6, 7, 8, 9 | +-+ 1 row in set (0.00 sec) mysql > select getChildList (2) +-+ | getChildList (2) | +-+ | 3, sec 11, 4, 5, 6, 7, 8, 9 | +-+ 1 sec (0.00 sec) Thank you for reading! On the realization of the mysql tree query function is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.