mysql 显示树结构表的节点全路径

SELECT
TYPEID AS TYPEID,
pTYPEID AS 父TYPEID,
levels AS 父到子之间级数,
concat(paths, ',', TYPEID) AS 父到子路径,
pathsName
FROM (
SELECT
TYPEID,
pTYPEID,
@le := IF(pTYPEID = 0, 0,
IF(LOCATE(CONCAT('|', pTYPEID, '😂, @pathlevel) > 0,
SUBSTRING_INDEX(SUBSTRING_INDEX(@pathlevel, CONCAT('|', pTYPEID, '😂, -1), '|', 1) + 1
, @le + 1)) levels,
@pathlevel := CONCAT(@pathlevel, '|', TYPEID, ':', @le, '|') pathlevel,
@pathnodes := IF(pTYPEID = 0, '0',
CONCAT_WS(',',
IF(LOCATE(CONCAT('|', pTYPEID, '😂, @pathall) > 0,
SUBSTRING_INDEX(SUBSTRING_INDEX(@pathall, CONCAT('|', pTYPEID, '😂, -1), '|', 1)
, @pathnodes), pTYPEID)) paths,
@pathall := CONCAT(@pathall, '|', TYPEID, ':', @pathnodes, '|') pathall,
@pathnodes1 := IF(pTYPEID = 0, NAME,
CONCAT_WS('|',
IF(LOCATE(CONCAT('|', NAME, '😂, @pathallName) > 0,
SUBSTRING_INDEX(SUBSTRING_INDEX(@pathallName, CONCAT('|', NAME, '😂, -1), '|', 1)
, @pathnodes1), NAME)) pathsName,
@pathallName := CONCAT(@pathallName, '|', NAME, ':', @pathnodes1, '|') pathallName

   FROM t_basicitem_checktype,
     (SELECT
        @le := 0,
        @pathlevel := '',
        @pathall := '',
        @pathnodes := '') vv
   ORDER BY pTYPEID, TYPEID
 ) src

ORDER BY TYPEID