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_authoritystart 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
