MySQL如何实现查询树结构(mysql,开发技术)

MySQL 查询树结构

1. 关于树结构

此类结构的数据，通常需要表结构中含有id 、parentId等自关联字段，有时为了提高查询效率还可增加更多冗余字段，如index，index的值为所有父级目录的id字符串集合。

关于树结构数据的组装，常见的写法是在程序中通过递归的方式去构建出一颗完整的树，单纯通过sql的方式其实并不常用，下面分别给出两种方式的例子。

2. MySQL自定义函数的方式

什么是MySQL自定义函数：聚合函数，日期函数之类的都是MySQL的函数，此处我们定义的函数可同他们一样使用，不过只能在定义的数据库中使用，自定义函数和存储过程类似，不同的是，函数只会返回一个值，不允许返回一个结果集。

2.1 创建测试数据

CREATETABLE`tree`(`id`bigint(11)NOTNULL,`pid`bigint(11)NULLDEFAULTNULL,`name`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULL,PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBCHARACTERSET=utf8COLLATE=utf8_general_ciROW_FORMAT=Dynamic;INSERTINTO`tree`VALUES(1,0,'中国');INSERTINTO`tree`VALUES(2,1,'四川省');INSERTINTO`tree`VALUES(3,2,'成都市');INSERTINTO`tree`VALUES(4,3,'武侯区');INSERTINTO`tree`VALUES(5,4,'红牌楼');INSERTINTO`tree`VALUES(6,1,'广东省');INSERTINTO`tree`VALUES(7,1,'浙江省');INSERTINTO`tree`VALUES(8,6,'广州市');

2.2 获取 某节点下所有子节点

CREATEFUNCTION`GET_CHILD_NODE`(rootIdvarchar(100))RETURNSvarchar(2000)BEGINDECLAREstrvarchar(2000);DECLAREcidvarchar(100);SETstr='$';SETcid=rootId;WHILEcidisnotnullDOSETstr=concat(str,',',cid);SELECTgroup_concat(id)INTOcidFROMtreewhereFIND_IN_SET(pid,cid);ENDWHILE;RETURNstr;END

调用自定义函数

select*fromtreewhereFIND_IN_SET(id,GET_CHILD_NODE(2));

2.3 获取 某节点的所有父节点

CREATEFUNCTION`GET_PARENT_NODE`(rootIdvarchar(100))RETURNSvarchar(1000)BEGINDECLAREfidvarchar(100)default'';DECLAREstrvarchar(1000)defaultrootId;WHILErootIdisnotnulldoSETfid=(SELECTpidFROMtreeWHEREid=rootId);IFfidisnotnullTHENSETstr=concat(str,',',fid);SETrootId=fid;ELSESETrootId=fid;ENDIF;ENDWHILE;returnstr;END

调用自定义函数

select*fromtreewhereFIND_IN_SET(id,GET_PARENT_NODE(5));

3. Oracle数据库的方式

只需要使用start with connect by prior语句即可完成递归的树查询，详情请自己查阅相关资料。

4. 程序代码递归的方式构建树

这里我就不给出完整代码了，递归的方式很简单，就是先查出所有树节点，然后通过一个TreeNode类中的add方法递归把所有子节点给加进来。核心代码如下：

publicclassTreeNodeDTO{privateStringid;privateStringparentId;privateStringname;privateList<TreeNodeDTO>children=newArrayList<>();publicvoidadd(TreeNodeDTOnode){if("0".equals(node.parentId)){this.children.add(node);}elseif(node.parentId.equals(this.id)){this.children.add(node);}else{ //递归调用add()添加子节点for(TreeNodeDTOtmp_node:children){tmp_node.add(node);}}}}

5. 通过hashMap，只需要遍历一次

就可以完成树的生成：五星推荐

List<TreeNodeDTO>list=dbMapper.getNodeList();ArrayList<TreeNodeDTO>rootNodes=newArrayList<>();Map<Integer,TreeNodeDTO>map=newHashMap<>();for(TreeNodeDTOnode:list){map.put(node.getId(),node);IntegerparentId=node.getParentId();//判断是否有父节点(没有父节点本身就是个父菜单)if(parentId.equals('0')){rootNodes.add(node);//找出不是父级菜单的且集合中包括其父菜单ID}elseif(map.containsKey(parentId)){map.get(parentId).getChildren().add(node);}}

MySQL 查询带树状结构的信息

在Oracle中有函数应用直接能够查询出树状的树状结构信息，例如有下面树状结构的组织成员架构，那么如果我们想查其中一个节点下的所有节点信息

在Oracle中可以直接用下面的语法可以进行直接查询

STARTWITHCONNECTBYPRIOR

但是在Mysql中是没有这个语法的

而如果你也是想要查询这样的数据结构信息该怎么做呢？我们可以自定义函数。我们将上面的信息初始化信息进数据库中。首先先创建一张表用于存储这些信息，ID为存储自身的ID信息，PARENT_ID存储父ID信息

CREATETABLE`company_inf`(`ID`varchar(32)COLLATEutf8mb4_unicode_ciDEFAULTNULL,`NAME`varchar(255)COLLATEutf8mb4_unicode_ciDEFAULTNULL,`PARENT_ID`varchar(32)COLLATEutf8mb4_unicode_ciDEFAULTNULL)

然后将图中的信息初始化表中

INSERTINTOcompany_infVALUES('1','总经理王大麻子','1');INSERTINTOcompany_infVALUES('2','研发部经理刘大瘸子','1');INSERTINTOcompany_infVALUES('3','销售部经理马二愣子','1');INSERTINTOcompany_infVALUES('4','财务部经理赵三驼子','1');INSERTINTOcompany_infVALUES('5','秘书员工J','1');INSERTINTOcompany_infVALUES('6','研发一组组长吴大棒槌','2');INSERTINTOcompany_infVALUES('7','研发二组组长郑老六','2');INSERTINTOcompany_infVALUES('8','销售人员G','3');INSERTINTOcompany_infVALUES('9','销售人员H','3');INSERTINTOcompany_infVALUES('10','财务人员I','4');INSERTINTOcompany_infVALUES('11','开发人员A','6');INSERTINTOcompany_infVALUES('12','开发人员B','6');INSERTINTOcompany_infVALUES('13','开发人员C','6');INSERTINTOcompany_infVALUES('14','开发人员D','7');INSERTINTOcompany_infVALUES('15','开发人员E','7');INSERTINTOcompany_infVALUES('16','开发人员F','7');

例如我们想要查询研发部门经理刘大瘸子下的所有员工，在Oracle中我们可以这样写

SELECT*FROMT_PORTAL_AUTHORITYSTARTWITHID='1'CONNECTBYPRIORID=PARENT_ID

而在Mysql中我们需要下面这样自定义函数

CREATEFUNCTIONgetChild(parentIdVARCHAR(1000))RETURNSVARCHAR(1000)BEGINDECLAREoTempVARCHAR(1000);DECLAREoTempChildVARCHAR(1000);SEToTemp='';SEToTempChild=parentId;WHILEoTempChildisnotnullDOIFoTemp!=''THENSEToTemp=concat(oTemp,',',oTempChild);ELSESEToTemp=oTempChild;ENDIF;SELECTgroup_concat(ID)INTOoTempChildFROMcompany_infwhereparentId<>IDandFIND_IN_SET(parent_id,oTempChild)>0;ENDWHILE;RETURNoTemp;END

然后这样查询即可

SELECT*FROMcompany_infWHEREFIND_IN_SET(ID,getChild('2'));

此时查看查询出来的信息就是刘大瘸子下所有的员工信息了