mysql 递归查询主要是对于层级关系的查询（mysql如何实现递归查询效果）

25-04-27 5

对于想了解mysql递归查询主要是对于层级关系的查询的读者，本文将提供新的信息，我们将详细介绍mysql如何实现递归查询效果，并且为您提供关于DB2：递归查询、JavaPhpMysql递归查询查询当前

对于想了解mysql 递归查询主要是对于层级关系的查询的读者，本文将提供新的信息，我们将详细介绍mysql如何实现递归查询效果，并且为您提供关于DB2：递归查询、Java Php Mysql 递归查询查询当前节点下的所有子节点包含当前节点返回列表、JOOQ - 递归查询、mybatis：递归查询，关联查询传入多个参数的有价值信息。

本文目录一览：

mysql 递归查询主要是对于层级关系的查询（mysql如何实现递归查询效果）
DB2：递归查询
Java Php Mysql 递归查询查询当前节点下的所有子节点包含当前节点返回列表
JOOQ - 递归查询
mybatis：递归查询，关联查询传入多个参数

mysql 递归查询主要是对于层级关系的查询（mysql如何实现递归查询效果）

最近遇到了一个问题，在 mysql 中如何完成节点下的所有节点或节点上的所有父节点的查询？
在 Oracle 中我们知道有一个 Hierarchical Queries 可以通过 CONNECT BY 来查询，但是，在 MySQL 中还没有对应的函数！！！

下面给出一个 function 来完成的方法

下面是 sql 脚本，想要运行的直接赋值粘贴进数据库即可。

创建表 treenodes（可以根据需要进行更改）

-- ---------------------------- 
-- Table structure for `treenodes` 
-- ---------------------------- 
DROP TABLE IF EXISTS `treenodes`; 
CREATE TABLE `treenodes` ( 
  `id` int(11) NOT NULL, 
  `nodename` varchar(20) DEFAULT NULL, 
  `pid` int(11) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

　　插入几条数据

-- ---------------------------- 
-- Records of treenodes 
-- ---------------------------- 
INSERT INTO `treenodes` VALUES (''1'', ''A'', ''0''); 
INSERT INTO `treenodes` VALUES (''2'', ''B'', ''1''); 
INSERT INTO `treenodes` VALUES (''3'', ''C'', ''1''); 
INSERT INTO `treenodes` VALUES (''4'', ''D'', ''2''); 
INSERT INTO `treenodes` VALUES (''5'', ''E'', ''2''); 
INSERT INTO `treenodes` VALUES (''6'', ''F'', ''3''); 
INSERT INTO `treenodes` VALUES (''7'', ''G'', ''6''); 
INSERT INTO `treenodes` VALUES (''8'', ''H'', ''0''); 
INSERT INTO `treenodes` VALUES (''9'', ''I'', ''8''); 
INSERT INTO `treenodes` VALUES (''10'', ''J'', ''8''); 
INSERT INTO `treenodes` VALUES (''11'', ''K'', ''8''); 
INSERT INTO `treenodes` VALUES (''12'', ''L'', ''9''); 
INSERT INTO `treenodes` VALUES (''13'', ''M'', ''9''); 
INSERT INTO `treenodes` VALUES (''14'', ''N'', ''12''); 
INSERT INTO `treenodes` VALUES (''15'', ''O'', ''12''); 
INSERT INTO `treenodes` VALUES (''16'', ''P'', ''15''); 
INSERT INTO `treenodes` VALUES (''17'', ''Q'', ''15'');

把下面的语句直接粘贴进命令行执行即可（注意修改传入的参数，默认 rootId，表明默认 treenodes）

根据传入 id 查询所有父节点的 id

delimiter // 
CREATE FUNCTION `getParLst`(rootId INT)
RETURNS varchar(1000) 

BEGIN
	DECLARE sTemp VARCHAR(1000);
	DECLARE sTempPar VARCHAR(1000); 
	SET sTemp = ''''; 
	SET sTempPar =rootId; 
	
	#循环递归
	WHILE sTempPar is not null DO 
		#判断是否是第一个，不加的话第一个会为空
		IF sTemp != '''' THEN
			SET sTemp = concat(sTemp,'','',sTempPar);
		ELSE
			SET sTemp = sTempPar;
		END IF;

		SET sTemp = concat(sTemp,'','',sTempPar); 
		SELECT group_concat(pid) INTO sTempPar FROM treenodes where pid<>id and FIND_IN_SET(id,sTempPar)>0; 
	END WHILE; 
	
RETURN sTemp; 
END

执行命令

select * from treenodes where FIND_IN_SET(id,getParList(15));

结果：

id	nodename	pid
8	H	0
9	I	8
12	L	9
15	O	12

根据传入 id 查询所有子节点的 id

delimiter // 
CREATE FUNCTION `getParLst`(rootId INT)
RETURNS varchar(1000) 

BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempChd VARCHAR(1000);

    SET sTemp = ''$'';
    SET sTempChd =cast(rootId as CHAR);

    WHILE sTempChd is not null DO
        SET sTemp = concat(sTemp,'','',sTempChd);
        SELECT group_concat(id) INTO sTempChd FROM  treeNodes where FIND_IN_SET(pid,sTempChd)>0;
       END WHILE;
    RETURN sTemp; 
END

执行命令

select * from treenodes where FIND_IN_SET(id,getChildList(7));

结果：

id	nodename	pid
7	G	6

DB2：递归查询

如何解决DB2：递归查询

需要帮助。

表：M_Objects

Col:    Id     Child_Id
Row1    M1       M2
Row2    M3       M4
Row3    M2       M5
Row4    M5       M6

当我要求 Id M1 时，我希望在我的查询中得到这些结果，行：1,3,4。所以对于Id，它应该寻找child-id并转到id...

WITH RPL (oid,ocid) AS
     (  SELECT Upp.ID,Upp.CHILD_ID
        FROM M_ObjectsUpp
        WHERE Upp.ID = ''M1''
      UNION ALL
       SELECT child.ID,child.CHILD_ID
        FROM RPL ab,M_Objects child
        WHERE CHILD.child_id = ab.oid
     )
SELECT *
FROM RPL

解决方法

您是自上而下遍历，从 ID 开始，然后使用其 child_id 查找下一个 ID。因此，您必须将递归部分中的 WHERE 更改为：

WITH RPL (oid,ocid) AS
     (  SELECT Upp.ID,Upp.CHILD_ID
        FROM M_Objects Upp
        WHERE Upp.ID = ''M1''
      UNION ALL
       SELECT child.ID,child.CHILD_ID
        FROM RPL ab,M_Objects child
        WHERE CHILD.id = ab.child_id
     )
SELECT *
FROM RPL

好的，谢谢！是否可以在 first 之前定义一个条件，只在第一次调用而不是每次递归运行时调用它？

 SELECT Upp.ID,Upp.CHILD_ID
    FROM M_Objects Upp
    WHERE Upp.ID = ''M1''

Java Php Mysql 递归查询查询当前节点下的所有子节点包含当前节点返回列表

递归查询

MySQL
- 1. WITH RECURSIVE 语法
- 2. 子查询
- 3. 函数
JAVA 代码
- Hutool 工具
- 执行多次 SQL
- 执行一次 SQL
PHP 代码

MySQL

1. WITH RECURSIVE 语法

使用了 MySQL 的 WITH RECURSIVE 语法。首先，它选取了当前节点作为起点，然后在递归的过程中找到了所有它的子节点。最后，它输出了所有找到的节点的标识符。
请注意将查询中的 [当前节点的 ID] 替换为你要查询的节点的 ID。

WITH RECURSIVE cte AS (
  SELECT id, parent_id
  FROM tree_table
  WHERE id = [当前节点的ID]
  UNION ALL
  SELECT t.id, t.parent_id
  FROM tree_table t
  INNER JOIN cte ON t.parent_id = cte.id
)
SELECT id FROM cte;

2. 子查询

如果你使用的 MySQL 版本不支持 WITH RECURSIVE 语法，或者你想尝试不使用 WITH RECURSIVE 的方法；
这个查询使用了两个子查询。第一个子查询选取了当前节点的 ID，并且作为递归查询的起点。第二个子查询从 tree_table 表中自联结，以查找当前节点的所有子节点。然后，使用 JOIN 操作将这些子节点与 tree_table 表中的原始记录匹配，从而得到当前节点下的所有子节点。
请注意将查询中的 [当前节点的 ID] 替换为你要查询的节点的 ID。

SELECT t1.id
FROM tree_table t1
JOIN (
  SELECT id FROM tree_table WHERE id = [当前节点的ID]
  UNION ALL
  SELECT t2.id FROM tree_table t2 JOIN tree_table t3 ON t2.parent_id = t3.id
  WHERE t3.id = [当前节点的ID]
) t4
ON t1.id = t4.id;

3. 函数

编写一个存储过程或函数来实现递归查询；将查询中的 [当前节点的 ID] 替换为你要查询的节点的 ID。

DELIMITER $$

CREATE FUNCTION get_all_children (node_id INT) RETURNS TEXT
BEGIN
  DECLARE child_ids TEXT DEFAULT '''';
  DECLARE child_id INT;
  
  SELECT GROUP_CONCAT(id) INTO child_ids FROM tree_table WHERE parent_id = node_id;
  
  IF child_ids IS NOT NULL THEN
    SET child_ids = CONCAT(child_ids, '','', get_all_children(child_id));
  END IF;
  
  RETURN child_ids;
END$$

DELIMITER ;

这个函数使用递归的方式来查询当前节点下的所有子节点。首先，它查找所有直接子节点的 ID，并使用 GROUP_CONCAT 函数将这些 ID 连接成一个逗号分隔的字符串。然后，对于每个子节点，函数递归调用自己来查找它的子节点，并将它们的 ID 也连接到返回值中。最后，函数返回一个包含当前节点下所有子节点 ID 的逗号分隔字符串。

请注意将函数中的 tree_table 和 id 和 parent_id 字段名称替换为你实际使用的表和字段名称。你可以使用以下语句来调用这个函数：

SELECT get_all_children([当前节点的ID]);

将查询中的 [当前节点的 ID] 替换为你要查询的节点的 ID。

JAVA 代码

Hutool 工具

//创建配置
	   TreeNodeConfig treeNodeConfig = new TreeNodeConfig();
       // 自定义属性名 都要默认值的
       treeNodeConfig.setIdKey("id");
       treeNodeConfig.setNameKey("name");
       treeNodeConfig.setParentIdKey("parent_id");
       treeNodeConfig.setChildrenKey("child");
       // 最大递归深度 可以不设置
       treeNodeConfig.setDeep(3);
       /* * 形参: * list – 源数据集合 * rootId – 最顶层父id值 一般为 0 之类 * treeNodeConfig – 配置 * nodeParser – 转换器 * 返回值: List */
       // 构建树结构
       List<Tree<String>> build = TreeUtil.build(list, "0", treeNodeConfig, (treeNode, tree) -> {
   
           tree.setId(String.valueOf(treeNode.get("area_id")));
           tree.setParentId(String.valueOf(treeNode.get("parent_id")));
           tree.setName((CharSequence) treeNode.get("area_name"));
       });

执行多次 SQL

这个 Java 方法使用递归的方式来查询当前节点下的所有子节点。首先，它查询所有直接子节点的 ID。然后，对于每个子节点，方法递归调用自己来查找它的子节点。最后，方法返回一个包含当前节点下所有子节点 ID 的列表。

请注意将示例代码中的 tree_table 和 id 和 parent_id 字段名称替换为你实际使用的表和字段名称，以及将数据库连接的 URL、用户名和密码替换为你的实际连接信息。

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TreeUtils {
   
  
  public static List<Integer> getAllChildren(int nodeId, Connection conn) throws SQLException {
   
    List<Integer> childIds = new ArrayList<>();
    
    // 查询所有直接子节点
    PreparedStatement stmt = conn.prepareStatement("SELECT id FROM tree_table WHERE parent_id = ?");
    stmt.setInt(1, nodeId);
    ResultSet rs = stmt.executeQuery();
    
    // 对于每个子节点，递归调用自己来查找它的子节点
    while (rs.next()) {
   
      int childId = rs.getInt("id");
      childIds.add(childId);
      childIds.addAll(getAllChildren(childId, conn));
    }
    
    rs.close();
    stmt.close();
    
    return childIds;
  }

  // 使用示例
  public static void main(String[] args) {
   
    Connection conn = null;
    try {
   
      // 创建数据库连接
      conn = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase", "root", "password");
      
      // 查询当前节点的所有子节点
      List<Integer> childIds = getAllChildren([当前节点的ID], conn);
      
      // 输出所有子节点的ID
      System.out.println(childIds);
    } catch (SQLException e) {
   
      e.printStackTrace();
    } finally {
   
      try {
   
        if (conn != null) {
   
          conn.close();
        }
      } catch (SQLException e) {
   
        e.printStackTrace();
      }
    }
  }
}

执行一次 SQL

这个 Java 方法首先执行 SQL 语句查询所有节点，然后将结果集转换为列表。接着使用递归的方式来查询树结构。它遍历列表中的每个节点，如果节点的父 ID 等于指定的父 ID，则将该节点作为当前节点，递归查询该节点的子节点，并将子节点作为嵌套的子树添加到当前节点。最后，方法返回一个包含根节点的树结构的 Map 对象。

请注意将示例代码中的 tree_table 和 id 和 parent_id 字段名称替换为你实际使用的表和字段名称，以及将数据库连接的 URL、用户名和密码

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TreeUtils {
   
  
  public static Map<String, Object> buildTree(List<Map<String, Object>> list, int parentId) {
   
    Map<String, Object> node = new HashMap<>();
    
    for (Map<String, Object> item : list) {
   
      int id = (int) item.get("id");
      int pid = (int) item.get("parent_id");
      
      if (pid == parentId) {
   
        // 设置当前节点的属性
        node.put("id", id);
        node.put("name", item.get("name"));
        
        // 递归查询当前节点的子节点
        List<Map<String, Object>> children = new ArrayList<>();
        for (Map<String, Object> child : list) {
   
          int childPid = (int) child.get("parent_id");
          if (childPid == id) {
   
            children.add(buildTree(list, id));
          }
        }
        
        // 如果有子节点，则将子节点添加到当前节点
        if (!children.isEmpty()) {
   
          node.put("children", children);
        }
      }
    }
    
    return node;
  }

  // 使用示例
  public static void main(String[] args) {
   
    Connection conn = null;
    try {
   
      // 创建数据库连接
      conn = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase", "root", "password");
      
      // 执行SQL语句查询所有节点
      PreparedStatement stmt = conn.prepareStatement("SELECT * FROM tree_table");
      ResultSet rs = stmt.executeQuery();
      
      // 将结果集转换为列表
      List<Map<String, Object>> list = new ArrayList<>();
      while (rs.next()) {
   
        Map<String, Object> item = new HashMap<>();
        item.put("id", rs.getInt("id"));
        item.put("name", rs.getString("name"));
        item.put("parent_id", rs.getInt("parent_id"));
        list.add(item);
      }
      
      // 构建树结构
      Map<String, Object> tree = buildTree(list, 0);
      
      // 输出树结构
      System.out.println(tree);
      
      rs.close();
      stmt.close();
    } catch (SQLException e) {
   
      e.printStackTrace();
    } finally {
   
      try {
   
        if (conn != null) {
   
          conn.close();
        }
      } catch (SQLException e) {
   
        e.printStackTrace();
      }
    }
  }
}

PHP 代码

这个 PHP 函数使用递归的方式来查询当前节点下的所有子节点。首先，它查询所有直接子节点的 ID。然后，对于每个子节点，函数递归调用自己来查找它的子节点。最后，函数返回一个包含当前节点下所有子节点 ID 的数组。

请注意将示例代码中的 tree_table 和 id 和 parent_id 字段名称替换为你实际使用的表和字段名称。如果你使用的是其他语言或框架，可以根据类似的逻辑编写递归函数或方法来实现递归查询。

function get_all_children($node_id, $mysqli) {
   
  $child_ids = array();
  
  // 查询所有直接子节点
  $result = $mysqli->query("SELECT id FROM tree_table WHERE parent_id = $node_id");
  
  // 对于每个子节点，递归调用自己来查找它的子节点
  while ($row = $result->fetch_assoc()) {
   
    $child_id = $row[''id''];
    $child_ids[] = $child_id;
    $child_ids = array_merge($child_ids, get_all_children($child_id, $mysqli));
  }
  
  return $child_ids;
}

// 使用示例
$mysqli = new mysqli(''host'', ''username'', ''password'', ''database'');
$node_id = 1; // 替换为你要查询的节点的ID
$child_ids = get_all_children($node_id, $mysqli);
print_r($child_ids);

JOOQ - 递归查询

如何解决JOOQ - 递归查询

我有以下数据库结构：

文件

file_id
文件名
folder_id

文件夹

folder_id
文件夹名称
parent_folder_id

我正在尝试以这种方式动态构建文件路径：

查询： - 返回文件和所在文件夹。

dsl.select()
   .from( FILE)
   .join( FOLDER)
   .on( FILE.FOLDER_ID.eq( FOLDER.FOLDER_ID) )
   .fetch();

然后我用简单的 Java 获取父文件夹：

List<Folder> parentFolders= new ArrayList<>();
Long parentFolderId = record.get( FOLDER.PARENT_FOLDER_ID );
while ( parentFolderId != null )
{
    Folder parentFolder = folderService.get(parentFolderId );
    parentFolders.add( 0,parentFolder );
    parentFolderId = parentFolder.getParentFolderId();
}

输出看起来像这样：

Folder -> Folder -> Folder -> File

也许可以在这样的类中获取：

Long fileId;
String fileName;
Long fileId;
String fileName;
List<Folder> parents;

JOOQ 有没有办法做到这两步？这个想法是编写一个查询来查找父文件夹。仅使用 JOOQ 就可以做到这一点吗？

mybatis：递归查询，关联查询传入多个参数

需求是：递归查询资源

1. 资源类 EntityBaseResource：

public final class EntityBaseResource {
  private Long resID = 0l;

  private String resName = "";

  private String urlPath = "";

  private String parentResID = "";

  private String iconClass = "fa fa-circle-o";

  private short level = 1;

  private String resType = "";

  private String permission = "";

  private String belongTo = "";

  private String isPublished = "";

  private short seq = 1;

  private String isValid = "1";

  // 子资源
  private List<EntityBaseResource> children;

  public final Long getResID() {
    return resID;
  }

  public final void setResID(Long resID) {
    this.resID = resID;
  }

  public final String getResName() {
    return resName;
  }

  public final void setResName(String resName) {
    this.resName = resName == null ? Constant.BLANK : resName.trim();
  }

  public final String getUrlPath() {
    return urlPath;
  }

  public final void setUrlPath(String urlPath) {
    this.urlPath = urlPath == null ? Constant.BLANK : urlPath.trim();
  }

  public final String getParentResID() {
    return parentResID;
  }

  public final void setParentResID(String parentResID) {
    this.parentResID = parentResID == null ? Constant.BLANK : parentResID.trim();
  }

  public final String getIconClass() {
    return iconClass;
  }

  public final void setIconClass(String iconClass) {
    this.iconClass = iconClass == null ? Constant.BLANK : iconClass.trim();
  }

//省略get/set方法

2.DAO

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.csget.entity.base.EntityBaseResource;

public interface DaoBaseResource {
    int deleteByPrimaryKey(Long resID);

    int insert(EntityBaseResource record);

    EntityBaseResource selectByPrimaryKey(Long resID);
    
    List<EntityBaseResource> selectRecursionRes(@Param("resID") Long resID, @Param("belongTo") String belongTo);

    int updateByPrimaryKey(EntityBaseResource record);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.csget.dao.base.DaoBaseResource">

    <!-- 基础属性 -->
    <resultMap id="BaseResultMap" type="com.csget.entity.base.EntityBaseResource">
        <id column="resID" jdbcType="BIGINT" property="resID" />
        <result column="resName" jdbcType="VARCHAR" property="resName" />
        <result column="urlPath" jdbcType="VARCHAR" property="urlPath" />
        <result column="parentResID" jdbcType="VARCHAR"
            property="parentResID" />
        <result column="iconClass" jdbcType="VARCHAR"
            property="iconClass" />    
        <result column="level" jdbcType="TINYINT" property="level" />
        <result column="resType" jdbcType="VARCHAR" property="resType" />
        <result column="permission" jdbcType="VARCHAR"
            property="permission" />
        <result column="seq" jdbcType="TINYINT" property="seq" />
        <result column="isValid" jdbcType="CHAR" property="isValid" />
    </resultMap>
    <!-- 递归资源，继承基础属性 -->
    <resultMap id="resAll" extends="BaseResultMap" type="com.csget.entity.base.EntityBaseResource">
        <collection property="children" ofType="resAll"
            column="{resID=resID,belongTo=belongTo}" select="selectRecursionRes" />
    </resultMap>

    <!-- 递归资源 -->
    <select id="selectRecursionRes" resultMap="resAll">
        SELECT resID, resName, urlPath, parentResID, iconClass, belongTo FROM t_base_resource
        WHERE parentResID=#{resID} and belongTo=#{belongTo} and isValid=''1'' ORDER BY seq ASC
    </select>
</mapper>

DAO 在调用时，通过注解传入参数

递归查询时多个参数参数，会自动调用查询结果中的字段值

同时递归查询，resultMap 采用的继承属性，这样可以避免一些不必要的查询，例如如果你只需要查询一条记录，不需要查询它下面的子集。

关于mysql 递归查询主要是对于层级关系的查询和mysql如何实现递归查询效果的介绍现已完结，谢谢您的耐心阅读，如果想了解更多关于DB2：递归查询、Java Php Mysql 递归查询查询当前节点下的所有子节点包含当前节点返回列表、JOOQ - 递归查询、mybatis：递归查询，关联查询传入多个参数的相关知识，请在本站寻找。

本文标签：