MySQL函数-根据子节点查询所有父节点名称

背景

公司的一个业务系统中有区域表，整个区域是一个树结构，为了方便根据某一父节点查询所有叶子节点，提供了一个额外的字段path，按照分隔符存储了从根节点到当前节点的总路径。

表结构如下：

create table t_area
(
    area_id       varchar(32)  not null comment '主键' primary key,
    area_pid      varchar(32)  null comment '父级区域ID',
    area_name     varchar(30)  null comment '区域名称',
    level         varchar(2)   null comment '层级 从1开始',
    external_name varchar(30)  null comment '对外名称',
    flag          varchar(3)   null comment '特殊标记 0=没有标记 1=特殊入门',
    path          varchar(200) null
)
    comment '区域位置表';

比如，有以下路径：

A楼-13层-1301室
A楼-13层-1302室
A楼-13层-1303室

那么，

在1301的path字段存储为：A楼id#13层id#1301id
在1302的path字段存储为：A楼id#13层id#1302id
在1303的path字段存储为：A楼id#13层id#1303id

目前的需求是，需要查询给定叶子节点的完整路径名称，但是待查询的表中只存储了叶子节点的area_id

有两种方式可以实现：

写一个SQL查询出叶子节点id，对应的path，在Java层面，按照#切割字符串，得到每一层的area_id,再用SQL查询出area_id对应的area_name，拼接好返回
在MySQL中实现一个自定义函数，传入叶子节点的id，即可获取完整路径名称。

如果使用第二种方式，该业务SQL就变得很简单，考虑用函数实现。

函数实现

CREATE DEFINER=`sgsv2`@`%` FUNCTION `getFullArea`(`areaId` varchar(50)) RETURNS varchar(50) CHARSET utf8
BEGIN
	set @area_id=areaId;
-- 	select @area_id;
  select path from t_area where area_id = @area_id limit 1 into @path;
-- 	select @path;
	SET @i=2; 
	SET @count=(LENGTH(@path)-LENGTH(REPLACE(@path ,'#',''))) + 1;
	set @returnStr="";
-- 	select @count;
	
	
	WHILE @i <= @count 
	DO
  set @areaTmpId=(SUBSTRING_INDEX(SUBSTRING_INDEX(@path,'#',@i),'#',-1));
-- 	select @areaTmpId;
	select area_name from t_area where area_id = @areaTmpId into @areaTmpName;
-- 	select @areaTmpName;


	if @i > 2 Then
	set @returnStr=concat(@returnStr,'-',@areaTmpName);
	else
	   set @returnStr=concat(@returnStr,@areaTmpName);
	end if;
	
-- 	set @returnStr=concat(@returnStr,'-',@areaTmpName);
-- 	select @returnStr;
	SET @i=@i+1; 
	END WHILE; 
	
-- 	select @returnStr;
  
	RETURN  @returnStr;
END

要点：获取path后，如何遍历获取每层的area_id?

我这里的实现是，先用下面的语句，获取总area_id的个数

SET @count=(LENGTH(@path)-LENGTH(REPLACE(@path ,'#',''))) + 1;

根据获得的count数进行遍历，在遍历中获取每一个area_id，关键语句如下：

set @areaTmpId=(SUBSTRING_INDEX(SUBSTRING_INDEX(@path,'#',@i),'#',-1));

最后使用concat函数拼接得到最终的结果

set @returnStr=concat(@returnStr,'-',@areaTmpName);

技巧：可以使用存储过程来调试

在函数中，无法像存储过程一样，使用select @变量名来调试，所以我先把函数主题复制到一个存储过程中，这样就可以调试了！