MySQL中将一个字符串字段按层级树状展开

水善利万物而不争，处众人之所恶，故几于道💦

文章目录

- - 需求
  - 1.分析
  - 2.实现
  - 3.思路刨析
  - 表结构和数据

需求

数据库中有个字段如下
在这里插入图片描述

如何将其转换为如下形式：
在这里插入图片描述

1.分析

1.他的层级个数是不确定的，也就是说有的有2层有的有5层；

2.而且还有可能有同一层有重复的，或者是不同层相同元素有不同的父类，简单来说就是一对多的关系，比如大同市可能在山西省下面有个大同市，我在青岛市下面也有个大同市，还有子类和父类一样的。如下图
在这里插入图片描述

2.实现

废话不多说直接上结果，表名叫province，字段名叫area

with t1 as(
	select
		distinct substring_index(substring_index(area,'-',help_topic_id+1),'-',-1) area
	from province,mysql.help_topic
	where help_topic_id<=length(area)-length(replace(area,'-',''))
)  , t2 as(
	select 
		row_number() over() id,
		area
	from t1
) , t3 as(
		select
			substring_index(area,'-',1) p1,
			if(substring_index(substring_index(area,'-',1),'-',-1)=substring_index(substring_index(area,'-',2),'-',-1),NULL,substring_index(substring_index(area,'-',2),'-',-1)) p2,
			if(substring_index(substring_index(area,'-',2),'-',-1)=substring_index(substring_index(area,'-',3),'-',-1),NULL,substring_index(substring_index(area,'-',3),'-',-1)) p3,
			if(substring_index(substring_index(area,'-',3),'-',-1)=substring_index(substring_index(area,'-',4),'-',-1),NULL,substring_index(substring_index(area,'-',4),'-',-1)) p4,
			if(substring_index(substring_index(area,'-',4),'-',-1)=substring_index(substring_index(area,'-',5),'-',-1),NULL,substring_index(substring_index(area,'-',5),'-',-1)) p5
		from province
) , t4 as(
		select p2 area,id pid from t3 inner join t2 on t2.area=t3.p1 where p2 is not null
		union
		select p3 area,id pid from t3 inner join t2 on t2.area=t3.p2 where p3 is not null
		union 
		select p4 area,id pid from t3 inner join t2 on t2.area=t3.p3 where p4 is not null
		union
		select p5 area,id pid from t3 inner join t2 on t2.area=t3.p3 where p5 is not null
		union
		select p1 area,NULL pid from t3 inner join t2 on t2.area=t3.p1 where p1 is not null
)
select
	t2.area,t2.id,t4.pid
from t4 inner join t2 on t2.area=t4.area

3.思路刨析

第一步：我们需要拿到如下结果，为每个元素生成一个id做准备
在这里插入图片描述
第二步：为每个元素生成一个id，我这里直接用行号了

第三步：每个元素有行号还不行，还必须有它的层级关系，因此需要拆分层级，我这里方法感觉不是很好，需要手动写最大的层数，（也尝试着用CTE循环去写了，但是没写出来😢有会写的哥们可以放在评论区交流一下，或者解决这个问题的其他巧妙方法也可以😁）
在这里插入图片描述
这里有人会有疑问，这个层级拆开后和原来的层级关系对不上，少了一部分重复的

这是因为这个需求不需要重复的，而且重复的元素本来就有pid了，再加一条是它自己也不合理，因此才用判断去掉了。

第四步：经过第二步和第三步，我们已经得到了最重要的两张临时表了，因此这步开始就可以取pid了，我的思路是父类和id表关联，父类的id就是子类的pid，所以这部分的结果会得出所有元素的pid
在这里插入图片描述
第五步：得出所有元素的pid后只需要和t2表关联再取出id就可以了，最终就得到了我们想要的结果。

如果你有更好的方法来解决这个问题或者对我的方法有优化结果的，可以放在评论区，互相交流一下，浇个朋友😁 我会认真查看的！！！
其中一棵树展开效果是这样的
在这里插入图片描述

表结构和数据

/*
 Navicat Premium Data Transfer

 Source Server         : MySQL
 Source Server Type    : MySQL
 Source Server Version : 80019
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80019
 File Encoding         : 65001

 Date: 16/11/2024 13:54:45
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for province
-- ----------------------------
DROP TABLE IF EXISTS `province`;
CREATE TABLE `province`  (
  `area` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of province
-- ----------------------------
INSERT INTO `province` VALUES ('山西省-太原市-迎泽区-迎泽区');
INSERT INTO `province` VALUES ('山西省-大同市-平城区-迎宾街-a区');
INSERT INTO `province` VALUES ('山东省-青岛市-大同市');

SET FOREIGN_KEY_CHECKS = 1;