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

青山师

发布于 2023-05-04 20:30:33

1.6K0

发布于 2023-05-04 20:30:33

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

示例：

id     name    type   url
1      大树    RT      root

树形结构视图：

--大树
	+--树干1
		+--树枝1
			+--树叶1
			+--树叶2
		+--树枝2
	+--树干2
		+--树枝3
			+--树叶3

建表SQL：

create table tree_node(id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200), type VARCHAR(30),url VARCHAR(200));

插入基础数据SQL：

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

查看刚刚插入的数据：

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函数得到其子节点：

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：

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 删除

mysql