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

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

需求

数据库中有个字段如下

如何将其转换为如下形式：

1.分析

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

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

2.实现

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

with t1 as(selectdistinct substring_index(substring_index(area,'-',help_topic_id+1),'-',-1) areafrom province,mysql.help_topicwhere help_topic_id<=length(area)-length(replace(area,'-',''))
)  , t2 as(select row_number() over() id,areafrom t1
) , t3 as(selectsubstring_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)) p5from province
) , t4 as(select p2 area,id pid from t3 inner join t2 on t2.area=t3.p1 where p2 is not nullunionselect p3 area,id pid from t3 inner join t2 on t2.area=t3.p2 where p3 is not nullunion select p4 area,id pid from t3 inner join t2 on t2.area=t3.p3 where p4 is not nullunionselect p5 area,id pid from t3 inner join t2 on t2.area=t3.p3 where p5 is not nullunionselect p1 area,NULL pid from t3 inner join t2 on t2.area=t3.p1 where p1 is not null
)
selectt2.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 TransferSource Server         : MySQLSource Server Type    : MySQLSource Server Version : 80019Source Host           : localhost:3306Source Schema         : testTarget Server Type    : MySQLTarget Server Version : 80019File Encoding         : 65001Date: 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;