查询树中某节点所有的后代节点

最新推荐文章于 2023-04-26 16:26:59 发布

最新推荐文章于 2023-04-26 16:26:59 发布 · 1.3k 阅读

文章标签：

#iBATIS #MySQL #数据结构 #DAO #SQL

J2EE 专栏收录该内容

9 篇文章

订阅专栏

本文介绍两种查询数据库中树型结构的方法：递归函数和MySQL函数。递归函数通过递归调用自身获取子节点，而MySQL函数则一次性获取所有子孙节点的ID。

在数据库的设计里面，经常使用parentId字段来构建树型的表结构，如部门、知识点等，数据举例：物电学院->自动化系->08自动化->08自动化一班，类似于这样的数据结构。如何查询某个节点下的所有子孙节点，在程序设计中有两种方法。以如下表结构做说明：

CREATE TABLE `basic_depart` (
  `departId` char(32) CHARACTER SET latin1 NOT NULL COMMENT '部门ID',
  `parentId` char(32) CHARACTER SET latin1 DEFAULT NULL COMMENT '父ID',
  `nodeType` tinyint(4) DEFAULT NULL COMMENT '0：无下级；1：有下级',
  `name` varchar(50) DEFAULT NULL COMMENT '部门名称',
  `telephone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `sortOrder` tinyint(4) DEFAULT NULL COMMENT '排序',
  `remark` varchar(1000) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`departId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1、写一个递归函数来递归查询

public List<Depart> getDepartTree(String parentId) throws SQLException {
  List<Depart> departList = dao.queryList("depart.getChildDepart", parentId);

  if (departList != null && departList.size() > 0) {
   Iterator<Depart> departIterator = departList.iterator();
   while (departIterator.hasNext()) {
    Depart depart = departIterator.next();
    depart.setSubDepartList(getDepartTree(depart.getDepartId()));
   }
  }

  return departList;
 }

递归函数比较简单，记住递归的2个要素：递归结束的条件和递归公式，一般不会有啥问题。

2、参考网上一哥们的思路，写一个求出所有子孙节点ID和自己的ID的mysql function

DROP FUNCTION IF EXISTS oes.depart_getChilds;
CREATE FUNCTION oes.`depart_getChilds`(rootId varchar(32)) RETURNS varchar(10000) CHARSET latin1
BEGIN
 DECLARE sTemp VARCHAR(10000);
  DECLARE sTempChd VARCHAR(10000);

  SET sTemp = rootId;
  SET sTempChd = rootId;
   
  WHILE sTempChd is not null DO
    SELECT group_concat(departId) INTO sTempChd FROM basic_depart where FIND_IN_SET(parentId,sTempChd) > 0;
    IF sTempChd is not null THEN 
     SET sTemp = concat(sTemp,',',sTempChd);
    END IF;
  END WHILE;
  RETURN sTemp;

END;

然后在程序或者ibatis里面设置查询条件类似下面：

select * from basic_depart where find_in_set(departId,depart_getChilds('root'))

这里稍微说一下，我数据库的全套charset和collation都是UTF8。

1.depart_getChilds函数RETURNS varchar(10000) CHARSET latin1，为什么要给返回值定义为charset latin1呢，因为貌似find_in_set函数的2个参数要求是latin1编码的（更正：不是find_in_set函数的问题，是departId字段的collation的设置问题）。不如此，就会报incorrect mix of collation的错误，为这个，浪费了我一个下午的时间。

2.本来我想写一个通用的mysql函数，处理所有树型结构的table，把table的名称，parentId字段名称，Id字段名称通通作为参数传递给getChilds函数。结果天不遂人愿，mysql不支持在function和procedure里面使用动态sql，要命3000。

3.为什么要第2个方法呢，一个是简单，一个是因为做ibatis的sqlmap的时候用得上，比如：

<select id="getList" resultMap="questionRM">
  select * from ed_question where parentId is null
  <dynamic prepend="and">
   <isNotEmpty property="question.knowledgeId" prepend="and">
    find_in_set(knowledgeId , knowledge_getChilds(#question.knowledgeId#))
   </isNotEmpty>
   <isNotEmpty property="question.quesTypeId" prepend="and">
    quesTypeId = #question.quesTypeId#
   </isNotEmpty>
   <isNotEmpty property="question.status" prepend="and">
    status = #question.status#
   </isNotEmpty>
   <isNotEmpty property="question.difficulty" prepend="and">
    difficulty = #question.difficulty#
   </isNotEmpty>
   <isNotEmpty property="question.title" prepend="and">
    title like concat('%',#question.title#,'%')
   </isNotEmpty>
  </dynamic>
  order by createTime desc
 </select>