mysql树形遍历_mysql树形结构遍历

课题：如何利用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)

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);