Implementation Method of Mysql Tree Recursive Query

Preface

For the tree structure data in the database, such as the department table, sometimes we need to know all the subordinate parts of a department or all the superior departments of a department. In this case, we need to use the recursive query of mysql

Recently, I am doing project migration. I moved the Oracle version to the Mysql version, and encountered some oracle functions, but mysql did not, so I had to customize the functions or find a way to replace them.

Oracle Recursive Query

If oracle implements recursive queries, you can use start with... connect by

connect by the basic syntax for recursive queries is:

select 1 from Form start with... connect by prior id = pId

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

connect by: connect by is required, start with can be omitted in some cases, or start with 1=1 is not limited directly

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

For details, please refer to my previous blog on oracle: https://www.ofstack.com/article/156306. htm

Implementation of Oracle

<select id="listUnitInfo" resultType="com.admin.system.unit.model.UnitModel" databaseId="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
 <if test="unitCode != null and unitCode !=''">
 and u.unit_code = #{unitCode}
 </if>
 <if test="unitName!=null and unitName!=''">
 and u.unit_name like '%'|| #{unitName} ||'%'
 </if>
 connect by prior u.unit_code = u.para_unit_code
 and u.unit_code &lt;>u.para_unit_code
 </select>

Mysql Recursive Query

The following mainly introduces the implementation of Mysql, Mysql does not provide similar functions, so it can only be achieved through custom functions, a lot of this information on the Internet, but I don't know that is original, this blog is written well, https://www.ofstack.com/database/201209/152513. html, I also use the method provided by the author to achieve my own, thank the author for sharing first

Here we borrow the user-defined function provided by the author, plus the Find_in_set function find_in_set(u.unit_code,getunitChildList(#{unitCode})) getunitChildList is a custom function

<select id="listUnitInfo" resultType="com.admin.system.unit.model.UnitModel" databaseId="mysql">
 select distinct u.unit_code,
  u.unit_name,
  u.unit_tel,
  u.para_unit_code
  from t_unit_info u
  <where>
  <if test="unitCode != null and unitCode !=''">
  and find_in_set(u.unit_code,getunitChildList(#{unitCode}))
  </if>
  <if test="unitName!=null and unitName!=''">
  and u.unit_name like concat('%', #{unitName} ,'%')
  </if>
  </where>
 </select>

getUnitChildList Custom Function

DELIMITER $$

USE `gd_base`$$

DROP FUNCTION IF EXISTS `getUnitChildList`$$

CREATE DEFINER=`root`@`%` FUNCTION `getUnitChildList`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
 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 ;

Summarize