MySQL 查询树结构方式

目录

• mysql 查询树结构
  • 1. 关于树结构
  • 2. mysql自定义函数的方式
    • 2.1 创建测试数据
    • 2.2 获取 某节点下所有子节点
    • 2.3 获取 某节点的所有父节点
  • 3. oracle数据库的方式
  • 4. 程序代码递归的方式构建树
  • 5. 通过hashmap，只需要遍历一次
• mysql 查询带树状结构的信息
  • 在oracle中可以直接用下面的语法可以进行直接查询
  • 但是在mysql中是没有这个语法的

mysql 查询树结构

1. 关于树结构

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

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

2. mysql自定义函数的方式

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

2.1 创建测试数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

create table `tree`  (    `id` bigint (11) not null ,    `pid` bigint (11) null default null ,    ` name ` varchar (255) character set utf8 collate utf8_general_ci null default null ,    primary key (`id`) using btree ) engine = innodb character set = utf8 collate = utf8_general_ci row_format = dynamic ; insert into `tree` values (1, 0, '中国' ); insert into `tree` values (2, 1, '四川省' ); insert into `tree` values (3, 2, '成都市' ); insert into `tree` values (4, 3, '武侯区' ); insert into `tree` values (5, 4, '红牌楼' ); insert into `tree` values (6, 1, '广东省' ); insert into `tree` values (7, 1, '浙江省' ); insert into `tree` values (8, 6, '广州市' );

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

create function `get_child_node`(rootid varchar (100))   returns varchar (2000)  begin   declare str varchar (2000);  declare cid varchar (100);   set str = '$' ;   set cid = rootid;   while cid is not null do        set str = concat(str, ',' , cid);        select group_concat(id) into cid from tree where find_in_set(pid, cid);   end while;   return str;   end

调用自定义函数

?

1	select * from tree where find_in_set(id, get_child_node(2));

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

create function `get_parent_node`(rootid varchar (100))   returns varchar (1000)   begin   declare fid varchar (100) default '' ;   declare str varchar (1000) default rootid;      while rootid is not null do        set fid =( select pid from tree where id = rootid);        if fid is not null then            set str = concat(str, ',' , fid);            set rootid = fid;        else            set rootid = fid;        end if;   end while;   return str;  end

调用自定义函数

?

1	select * from tree where find_in_set(id, get_parent_node(5));

3. oracle数据库的方式

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

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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

public class treenodedto {           private string id;      private string parentid;      private string name ;      private list<treenodedto> children = new arraylist<>();      public void add (treenodedto node) {          if ( "0" .equals(node.parentid)) {              this.children. add (node);          } else if (node.parentid.equals(this.id)) {              this.children. add (node);          } else {              //递归调用 add ()添加子节点              for (treenodedto tmp_node : children) {                  tmp_node. add (node);              }          }      }   }

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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

list<treenodedto> list = dbmapper.getnodelist(); arraylist<treenodedto> rootnodes = new arraylist<>(); map< integer , treenodedto> map = new hashmap<>(); for (treenodedto node :list) {      map.put(node.getid(), node);      integer parentid = node.getparentid();      // 判断是否有父节点 (没有父节点本身就是个父菜单)      if (parentid.equals( '0' )){          rootnodes. add (node);          // 找出不是父级菜单的且集合中包括其父菜单id      } else if (map.containskey(parentid)){          map.get(parentid).getchildren(). add (node);      } }

mysql 查询带树状结构的信息

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

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

?

1	start with connect by prior

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

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

?

1 2 3 4 5

create table `company_inf` (    `id` varchar (32) collate utf8mb4_unicode_ci default null ,    ` name ` varchar (255) collate utf8mb4_unicode_ci default null ,    `parent_id` varchar (32) collate utf8mb4_unicode_ci default null )

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

insert into company_inf values ( '1' , '总经理王大麻子' , '1' ); insert into company_inf values ( '2' , '研发部经理刘大瘸子' , '1' ); insert into company_inf values ( '3' , '销售部经理马二愣子' , '1' ); insert into company_inf values ( '4' , '财务部经理赵三驼子' , '1' ); insert into company_inf values ( '5' , '秘书员工j' , '1' ); insert into company_inf values ( '6' , '研发一组组长吴大棒槌' , '2' ); insert into company_inf values ( '7' , '研发二组组长郑老六' , '2' ); insert into company_inf values ( '8' , '销售人员g' , '3' ); insert into company_inf values ( '9' , '销售人员h' , '3' ); insert into company_inf values ( '10' , '财务人员i' , '4' ); insert into company_inf values ( '11' , '开发人员a' , '6' ); insert into company_inf values ( '12' , '开发人员b' , '6' ); insert into company_inf values ( '13' , '开发人员c' , '6' ); insert into company_inf values ( '14' , '开发人员d' , '7' ); insert into company_inf values ( '15' , '开发人员e' , '7' ); insert into company_inf values ( '16' , '开发人员f' , '7' );

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

?

1 2 3 4

select * from t_portal_authority start with id= '1' connect by prior id = parent_id

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

create function getchild(parentid varchar (1000)) returns varchar (1000) begin      declare otemp varchar (1000);      declare otempchild varchar (1000);      set otemp = '' ;      set otempchild =parentid;      while otempchild is not null do          if otemp != '' then              set otemp = concat(otemp, ',' ,otempchild);          else              set otemp = otempchild;          end if;          select group_concat(id) into otempchild from company_inf where parentid<>id and find_in_set(parent_id,otempchild)>0;      end while; return otemp; end

然后这样查询即可

?

1	select * from company_inf where find_in_set(id,getchild( '2' ));

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

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。

原文链接：https://blog.csdn.net/qq_34997906/article/details/94007556