mysql树形结构遍历

来源：互联网发布：殷保华一线法公式源码编辑：程序博客网时间：2024/05/21 21:50

课题：如何利用mysql遍历树形结构（获取一个节点的所有子节点/父节点）

方案：自定义mysql函数

树形表结构：

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函数函数：

1. 获取节点的子节点

CREATE FUNCTION `getChildLst`(rootId INT)RETURNS varchar(1000)BEGINDECLARE sTemp VARCHAR(1000);DECLARE sTempChd VARCHAR(1000);SET sTemp = '';SET sTempChd =cast(rootId as CHAR);loop1:LOOPSELECT group_concat(id) INTO sTempChd FROM tbl_tree where FIND_IN_SET(father_id,sTempChd)>0;IF sTempChd is not null THENSET sTemp = concat(sTemp,',',sTempChd);ELSELEAVE loop1;END IF;END LOOP;SET sTemp = TRIM(',' FROM sTemp);RETURN sTemp;END

2. 获取节点的父节点

CREATE FUNCTION `getFatherLst`(rootId INT)RETURNS varchar(1000)BEGINDECLARE sTemp VARCHAR(1000);DECLARE sTempPrt VARCHAR(1000);SET sTemp = '';SET sTempPrt = cast(rootId as CHAR);loop1:LOOPSELECT group_concat(father_id) INTO sTempPrt FROM tbl_tree where FIND_IN_SET(id,sTempChd)>0;IF sTempPrt is not null THENSET sTemp = concat(sTemp,',',sTempPrt);ELSELEAVE loop1;END IF;END LOOP;SET sTemp = TRIM(',' FROM sTemp);RETURN sTemp;END

优化后的查询语句：

1. 获取子节点

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

1. 获取父节点

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

0 0