前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >【MySQL基础】MySql如何根据输入的id获得树形结构的子节点列表:使用自连+SUBSTRING_INDEX函数

【MySQL基础】MySql如何根据输入的id获得树形结构的子节点列表:使用自连+SUBSTRING_INDEX函数

作者头像
青山师
发布2023-05-04 20:30:33
1.6K0
发布2023-05-04 20:30:33
举报
文章被收录于专栏:IT当时语_青山师_JAVA技术栈

有如下树形结构:RT-ST-SST-SSST共四层,RT是根节点,往后依次是一代子节点,二代子节点,三代子节点。 如何根据当前节点的id,获得其子节点呢?这是一个SQL问题。加入传入的id为1(即根节点),使用自连+SUBSTRING_INDEX函数得到其子节点:

示例:

代码语言:javascript
复制
id     name    type   url
1      大树    RT      root

树形结构视图:

代码语言:javascript
复制
--大树
	+--树干1
		+--树枝1
			+--树叶1
			+--树叶2
		+--树枝2
	+--树干2
		+--树枝3
			+--树叶3

建表SQL:

代码语言:javascript
复制
create table tree_node(id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200), type VARCHAR(30),url VARCHAR(200));

插入基础数据SQL:

代码语言:javascript
复制
insert into tree_node(name, type, url) values('大树', 'RT', 'root');

insert into tree_node(name, type, url) values('树干1', 'ST', 'root/tree_main_line1');
insert into tree_node(name, type, url) values('树干2', 'ST', 'root/tree_main_line2');

insert into tree_node(name, type, url) values('树枝1', 'SST', 'root/tree_main_line1/tree_branche1');
insert into tree_node(name, type, url) values('树枝2', 'SST', 'root/tree_main_line1/tree_branche2');
insert into tree_node(name, type, url) values('树枝3', 'SST', 'root/tree_main_line2/tree_branche3');

insert into tree_node(name, type, url) values('树叶1', 'SSST', 'root/tree_main_line1/tree_branche1/tree_leaf1');
insert into tree_node(name, type, url) values('树叶2', 'SSST', 'root/tree_main_line1/tree_branche1/tree_leaf2');
insert into tree_node(name, type, url) values('树叶3', 'SSST', 'root/tree_main_line2/tree_branche3/tree_leaf3');

查看刚刚插入的数据:

代码语言:javascript
复制
mysql> select * from tree_node;
+----+---------+------+-----------------------------------------------+
| id | name    | type | url                                           |
+----+---------+------+-----------------------------------------------+
|  1 | 大树    | RT   | root                                          |
|  2 | 树干1   | ST   | root/tree_main_line1                          |
|  3 | 树干2   | ST   | root/tree_main_line2                          |
|  4 | 树枝1   | SST  | root/tree_main_line1/tree_branche1            |
|  5 | 树枝2   | SST  | root/tree_main_line1/tree_branche2            |
|  6 | 树枝3   | SST  | root/tree_main_line2/tree_branche3            |
|  7 | 树叶1   | SSST | root/tree_main_line1/tree_branche1/tree_leaf1 |
|  8 | 树叶2   | SSST | root/tree_main_line1/tree_branche1/tree_leaf2 |
|  9 | 树叶3   | SSST | root/tree_main_line2/tree_branche3/tree_leaf3 |
+----+---------+------+-----------------------------------------------+

加入传入的id为1(即根节点),使用自连+SUBSTRING_INDEX函数得到其子节点:

代码语言:javascript
复制
mysql> select tree1.* from tree_node as tree1
    -> join tree_node as tree2
    -> on SUBSTRING_INDEX(tree1.url,'/',1) = tree2.url
    -> and (length(tree1.url) - length(replace(tree1.url, '/', ''))) = 1
    -> where tree2.id = 1;
+----+---------+------+----------------------+
| id | name    | type | url                  |
+----+---------+------+----------------------+
|  2 | 树干1   | ST   | root/tree_main_line1 |
|  3 | 树干2   | ST   | root/tree_main_line2 |
+----+---------+------+----------------------+

解析SQL:

代码语言:javascript
复制
select tree1.* from tree_node as tree1
join tree_node as tree2
on SUBSTRING_INDEX(tree1.url,'/',1) = tree2.url
and (length(tree1.url) - length(replace(tree1.url, '/', ''))) = 1
where tree2.id = 1;


##返回"树干1"的父节点的url
select SUBSTRING_INDEX('root/tree_main_line1','/',1) 

##将"/"替换为""
mysql> select replace('root/tree_main_line1', '/', '');
+------------------------------------------+
| replace('root/tree_main_line1', '/', '') |
+------------------------------------------+
| roottree_main_line1                      |
+------------------------------------------+

##利用length差获得"/"的个数为1,则认为是子节点
mysql> select length('root/tree_main_line1') - length(replace('root/tree_main_line1', '/', ''));
+-----------------------------------------------------------------------------------+
| length('root/tree_main_line1') - length(replace('root/tree_main_line1', '/', '')) |
+-----------------------------------------------------------------------------------+
|                                                                                 1 |
+-----------------------------------------------------------------------------------+
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2016-11-15,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档