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

有两种方式可以实现:

  1. 写一个SQL查询出叶子节点id,对应的path,在Java层面,按照#切割字符串,得到每一层的area_id,再用SQL查询出area_id对应的area_name,拼接好返回
  2. 在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 @变量名来调试,所以我先把函数主题复制到一个存储过程中,这样就可以调试了!

posted @ 2020-08-04 16:24  Ging  阅读(3934)  评论(0编辑  收藏  举报