mysql 递归查找父节点_MYSQL递归查询所有父节点

1、表结构：

CREATE TABLE `t_busi_system` (

`ID` varchar(64) NOT NULL COMMENT '标识',

`PARENT_ID` varchar(64) DEFAULT NULL COMMENT '父id',

`CREATE_DATE` varchar(64) DEFAULT NULL COMMENT '创建时间',

`CREATE_USER_ID` varchar(64) DEFAULT NULL COMMENT '创建人id',

`CREATE_USER_NAME` varchar(64) DEFAULT NULL COMMENT '创建人姓名',

`LAST_UPDATE_DATE` varchar(64) DEFAULT NULL COMMENT '最后更新时间',

`LAST_UPDATE_USER_ID` varchar(64) DEFAULT NULL COMMENT '最后更新人id',

`LAST_UPDATE_USER_NAME` varchar(64) DEFAULT NULL COMMENT '最后更新人姓名',

`SYSTEM_NAME` varchar(255) DEFAULT NULL COMMENT '系统名称'

PRIMARY KEY (`ID`)

)

2、SQL：

SELECT T2.id, T2.SYSTEM_NAME

FROM (

SELECT

@r AS _id,

(SELECT @r := parent_id FROM T_BUSI_SYSTEM WHERE id = _id) AS parent_id,

@l := @l + 1 AS lvl

FROM

(SELECT @r := '065efb0ee42e4c77a401bcf0f3eccf20', @l := 0) vars,

T_BUSI_SYSTEM h

WHERE @r <> 0) T1

JOIN T_BUSI_SYSTEM T2

ON T1._id = T2.id

ORDER BY T1.lvl DESC