課題：如何利用mysql遍歷樹形結(jié)構(gòu)(獲取一個節(jié)點(diǎn)的所有子節(jié)點(diǎn)/父節(jié)點(diǎn))

方案：自定義mysql函數(shù)

樹形表結(jié)構(gòu)：

CREATE TABLE `tbl_tree` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`father_id` bigint(20) NOT NULL,

PRIMARY KEY (`id`),

KEY `father_id` (`father_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

自定義mysql函數(shù)函數(shù)：

1. 獲取節(jié)點(diǎn)的子節(jié)點(diǎn)

CREATE FUNCTION `getChildLst`(rootId INT)

RETURNS varchar(1000)

BEGIN

DECLARE sTemp VARCHAR(1000);

DECLARE sTempChd VARCHAR(1000);

SET sTemp = '';

SET sTempChd =cast(rootId as CHAR);

loop1:LOOP

SELECT group_concat(id) INTO sTempChd FROM tbl_tree where FIND_IN_SET(father_id,sTempChd)>0;

IF sTempChd is not null THEN

SET sTemp = concat(sTemp,',',sTempChd);

ELSE

LEAVE loop1;

END IF;

END LOOP;

SET sTemp = TRIM(',' FROM sTemp);

RETURN sTemp;

END

2. 獲取節(jié)點(diǎn)的父節(jié)點(diǎn)

CREATE FUNCTION `getFatherLst`(rootId INT)

RETURNS varchar(1000)

BEGIN

DECLARE sTemp VARCHAR(1000);

DECLARE sTempPrt VARCHAR(1000);

SET sTemp = '';

SET sTempPrt = cast(rootId as CHAR);

loop1:LOOP

SELECT group_concat(father_id) INTO sTempPrt FROM tbl_tree where FIND_IN_SET(id,sTempChd)>0;

IF sTempPrt is not null THEN

SET sTemp = concat(sTemp,',',sTempPrt);

ELSE

LEAVE loop1;

END IF;

END LOOP;

SET sTemp = TRIM(',' FROM sTemp);

RETURN sTemp;

END

優(yōu)化后的查詢語句：

1. 獲取子節(jié)點(diǎn)

select A.* from tbl_tree A inner join (select getChildLst(1) as idArr) TEMP where FIND_IN_SET(A.id, TEMP.idArr);1. 獲取父節(jié)點(diǎn)

select A.* from tbl_tree A inner join (select getFatherLst(15000) as idArr) TEMP where FIND_IN_SET(A.id, TEMP.idArr);

總結(jié)

以上是生活随笔為你收集整理的mysql树形遍历_mysql树形结构遍历的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。