MySQL 查询树结构方式

今天小编就为大家分享一篇MySQL 查询树结构方式,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教

目录

  • mysql 查询树结构

    • 1. 关于树结构
    • 2. mysql自定义函数的方式

      • 2.1 创建测试数据
      • 2.2 获取 某节点下所有子节点
      • 2.3 获取 某节点的所有父节点
    • 3. oracle数据库的方式

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

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

  • mysql 查询带树状结构的信息

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

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

mysql 查询树结构

1. 关于树结构

MySQL 查询树结构方式

此类结构的数据,通常需要表结构中含有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));

MySQL 查询树结构方式

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));

MySQL 查询树结构方式

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

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/3601/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-07-19 2:38:06
下一篇 2022-07-19 2:38:18

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!