Implementation method of Mysql tree recursive query

Preface

For tree-structured data in the database, such as department tables, sometimes we need to know all the subordinate departments of a department or all the superior departments of a department. At this time, we need to use MySQL recursive query

I'm currently migrating a project from Oracle to MySQL. I encountered some Oracle functions that MySQL doesn't have, so I had to customize the functions or find a way to replace the functions for the transformation.

Oracle recursive query

If Oracle implements recursive query, you can use start with ... connect by

The basic syntax of connect by recursive query is:

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

start with: indicates what node is the root node. You can write 1=1 without restriction. To use the node with id 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 without restriction

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

For details, please refer to an Oracle blog I wrote before: https://www.jb51.net/article/156306.htm

Oracle Implementation

<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 <>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 implemented 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. Below I also use the method provided by the author to implement my own. Thanks to the author for sharing.

Here we use the custom function provided by the author, plus the Find_in_set function find_in_set(u.unit_code,getunitChildList(#{unitCode})) , where 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

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.