MySQL拼接所有的父节点名称

建表语句：

CREATE TABLE dimension_tree(
    id VARCHAR(128) NOT NULL   COMMENT '主键' ,
    pid VARCHAR(128)    COMMENT '父类目id' ,
    name VARCHAR(128)    COMMENT '节点名称' ,
    updater VARCHAR(128)    COMMENT '更新人id' ,
    update_time DATETIME    COMMENT '更新时间' ,
    creater VARCHAR(32)    COMMENT '创建人' ,
    create_time DATETIME    COMMENT '创建时间' ,
    remark VARCHAR(3072)    COMMENT '描述' ,
    PRIMARY KEY (id)
) COMMENT = '目录树表';;

用'-'拼接所有的父节点名称

SELECT GROUP_CONCAT(T2.`name` ORDER BY T1.lvl DESC separator'-') as pName
FROM ( 
    SELECT 
        @r AS _id, 
        (SELECT @r := pid FROM dimension_tree WHERE id = _id) AS pid, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := '9', @l := 0) vars,  
        dimension_tree h 
    WHERE @r <> 0) T1 
JOIN dimension_tree T2 
ON T1._id = T2.id 

结果：