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);
ELSE
LEAVE loop1;
END IF;
END LOOP;
SET sTemp = TRIM(',' FROM sTemp);
RETURN sTemp;
END
2. 獲取節點的父節點
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
優化后的查詢語句：
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);
總結
以上是生活随笔為你收集整理的mysql树形遍历_mysql树形结构遍历的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： python 爬虫 scrapy 和 r
- 下一篇： mysql会话命令_mysql常用命令（