Implementation of Mysql Tree Recursive query

Preface

For the tree-structured data in the database, such as department tables, sometimes we need to know all the subordinate parts of a department or all the superior departments of a certain part, so we need to use the recursive query of mysql

Recently in the project migration, the Oracle version moved to the Mysql version, encountered some oracle functions, mysql does not have, so I have to think of custom functions or find a way to replace the function to transform.

Oracle recursive query

If oracle implements a recursive query, you can use start with. Connect by

The basic syntax of connect by recursive query is:

Select 1 from form start with... Connect by prior id = pId

Start with: indicates what is the root node. You can write 1: 1 without restriction. If you want to use the node with id as the root node, write start with id = 123.

Connect by:connect by is necessary, some cases of start with can be omitted, or there are no restrictions on start with 1.

The prior:prior keyword can be placed before or after the equal sign to express different meanings. For example, prior id = pid means that pid is the root node of the record.

For details, please refer to a blog I wrote about oracle: https://www.jb51.net/article/156306.htm.

Implementation of Oracle

Select distinct u.unit_code, u.unit_name, u.unit_tel, u.para_unit_code from lzcity_approve_unit_info u start with 1 = 1 and u.unit_code = # {unitCode} and u.unit_name like'%'| # {unitName} | |'% 'connect by prior u.unit_code = u.para_unit_code and u.unit_code u.para_unit_code

Mysql recursive query

The following mainly introduces the implementation of Mysql. Mysql does not provide similar functions, so it can only be realized through custom functions. There are a lot of such materials on the Internet, but I don't know which one is original. This blog is well written, https://www.jb51.net/database/201209/152513.html. I also use the method provided by the author to realize myself. Thank you for sharing first.

Here we borrow the custom function provided by the author, plus the Find_in_set function find_in_set (u.unitwritten codeline getunitChildList (# {unitCode})). GetunitChildList is a custom function.

Select distinct u.unit_code, u.unit_name, u.unit_tel, u.para_unit_code from t_unit_info u and find_in_set (u.unitworthy codebook getunitChildList (# {unitCode})) and u.unit_name like concat ('%', # {unitName},'%')

GetUnitChildList custom function

DELIMITER $$USE `gd_ Base` $DROP FUNCTION IF EXISTS `getUnitChildList` $CREATE DEFINER= `root` @ `% `getUnitChildList` (rootId INT) RETURNS VARCHAR (1000) CHARSET utf8BEGIN DECLARE sChildList VARCHAR (1000); DECLARE sChildTemp VARCHAR (1000); SET sChildTemp = CAST (rootId AS CHAR); WHILE sChildTemp IS NOT NULL DO IF (sChildList IS NOT NULL) THEN SET sChildList = CONCAT (sChildList,',',sChildTemp); ELSE SET sChildList = CONCAT (sChildTemp); END IF; SELECT GROUP_CONCAT (unit_code) INTO sChildTemp FROM LZCITY_APPROVE_UNIT_INFO WHERE FIND_IN_SET (para_unit_code,sChildTemp) > 0; END WHILE; RETURN sChildList END$$DELIMITER

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.