mysql 利用函数迭代查找所有父节点

1.表结构及测试数据

DROP TABLE IF EXISTS `tree`;
CREATE TABLE `tree` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) DEFAULT NULL,
  `tname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of tree
-- ----------------------------
INSERT INTO `tree` VALUES ('1', '0', '集团');
INSERT INTO `tree` VALUES ('2', '1', '公司');
INSERT INTO `tree` VALUES ('3', '2', '部门');
INSERT INTO `tree` VALUES ('4', '1', '公司2');

2.函数

DROP FUNCTION IF EXISTS queryTree;
CREATE FUNCTION queryTree(TID VARCHAR(4000))
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
DECLARE sTempName VARCHAR(4000);

SET sTemp='';
SET sTempName='';
SET sTempChd =TID;

SELECT tname INTO sTemp from tree where id=sTempChd;

WHILE sTempChd !="0" DO

IF sTempName !="" THEN
SET sTemp= CONCAT(sTemp,'/',sTempName);
END IF;

SELECT a.pid,b.tname
INTO sTempChd,sTempName
FROM tree a 
left join tree b on a.pid=b.id
WHERE a.id=sTempChd  ;


END WHILE;


RETURN sTemp;
END;

3.测试

select queryTree("3")

4.测试结果