mysql查询根据id查询父节点，返回拼接好的字符串（父节点1-父节点2-当前节点），或者只返回最顶级父节点名称。

本文链接：https://blog.csdn.net/qq_38993002/article/details/108570870

一、获取拼接好的父节点字符串

CREATE DEFINER=`root`@`%` FUNCTION `getVehicleComponentParentName`(`nodeId` int) RETURNS varchar(400) CHARSET utf8
BEGIN
	DECLARE parentId INT;
  DECLARE parentName VARCHAR(400);
	DECLARE pId INT;
	DECLARE num INT;
	SET parentId = 99999;
	SET parentName = '';
	SET pId = `nodeId` ;
select count(1) INTO num from vehicle_component where id = pId;
if num = 0 then set parentId = 0;
else 
	select parent_id INTO parentId from vehicle_component where id = pId;
	select `name` INTO parentName from vehicle_component where id = pId;
end if;
WHILE parentId <> 0 DO
	set pId = parentId;
	select parent_id INTO parentId from vehicle_component where id = pId;
	select CONCAT_WS('-', `name`, parentName) INTO parentName from vehicle_component where id = pId;
END WHILE;
	RETURN parentName;
END

二、获取最顶级父节点

CREATE DEFINER=`root`@`%` FUNCTION `getVehicleComponentParentName2`(`nodeId` int) RETURNS varchar(400) CHARSET utf8
BEGIN
	DECLARE parentId INT;
  DECLARE parentName VARCHAR(400);
	DECLARE pId INT;
	DECLARE num INT;
	SET parentId = 99999;
	SET parentName = '';
	SET pId = `nodeId` ;
select count(1) INTO num from vehicle_component where id = pId;
if num = 0 then set parentId = 0;
else 
	select parent_id INTO parentId from vehicle_component where id = pId;
	select `name` INTO parentName from vehicle_component where id = pId;
end if;
WHILE parentId <> 0 DO
	set pId = parentId;
	select parent_id INTO parentId from vehicle_component where id = pId;
	select name INTO parentName from vehicle_component where id = pId;
END WHILE;
	RETURN parentName;
END

数据库中表结构：

CREATE TABLE `vehicle_component` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) unsigned DEFAULT NULL COMMENT '父级部件id',
  `type` varchar(64) DEFAULT NULL COMMENT '部件类型,关联字典表code=VEHICLE_COMPONENT_TYPE',
  `structure_type` varchar(64) DEFAULT NULL COMMENT '结构类型，关联字典表pcode=VEHICLE_STRUCTURE_NODE_TYPE',
  `level` varchar(32) DEFAULT NULL COMMENT '层级',
  `code` varchar(64) DEFAULT NULL COMMENT '部件编码',
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `material_number` varchar(64) DEFAULT NULL COMMENT '物料编码(备用)',
  `count` int(11) DEFAULT NULL COMMENT '数量(备用)',
  `remark` varchar(256) DEFAULT NULL COMMENT '备注',
  `status` tinyint(3) DEFAULT '1' COMMENT '状态  1：正常   9：删除',
  `creater_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新人',
  `create_user` int(11) DEFAULT NULL COMMENT '创建人',
  `update_user` int(11) DEFAULT NULL COMMENT '更新人',
  `sort` tinyint(2) unsigned DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=140 DEFAULT CHARSET=utf8 COMMENT='车辆部件表';

使用：

1.首先在数据库中创建对应的自定义函数“getVehicleComponentParentName”或“getVehicleComponentParentName2”；

2.调用时，直接使用如：select getVehicleBindComponentParentName(139) from vehicle_component；

其中139是传入的当前节点的id。

备注：

1.mysql查看自定义函数命令：show create function "函数名"

2.mysql创建自定义函数命令格式：

create function ym_date(mydate date)
    returns varchar(15)
    begin
        return date_format(mydate,'%Y-%m');
    end

3.mysql删除自定义函数命令：drop function "函数名"