MySQL遞歸查找樹形結構(這個方法太實用了!)

更新時間：2022年11月09日 10:21:59 作者：肥肥技術宅

對于數(shù)據(jù)庫中的樹形結構數(shù)據(jù),如部門表,有時候,我們需要知道某部門的所有下屬部分或者某部分的所有上級部門,這時候就需要用到mysql的遞歸查詢,下面這篇文章主要給大家介紹了關于MySQL遞歸查找樹形結構的相關資料,需要的朋友可以參考下

1、數(shù)據(jù)庫中的樹形結構

數(shù)據(jù)庫中存貯的數(shù)據(jù)，以ID和P_ID(父id)，來存貯樹形結構

這樣如果需要查找某個節(jié)點的子節(jié)點，就可以尋找P_ID。如果要查找所有子節(jié)點，就需要遍歷所有的子節(jié)點的子節(jié)點。

如果要判斷是否為同級的節(jié)點，就可以查找是否有相同的節(jié)點。

2、MySQL中如何查找相應的數(shù)據(jù)

這里，我采用的是一個存儲函數(shù)。在查詢時可以直接使用。當然，為了以后查詢方便，也可以在一個視圖使用。

3、準備工作

數(shù)據(jù)庫表，為了方便，只有三個字段，能夠說明情況即可。

CREATE TABLE `city` (
`i_id`  int(11) NOT NULL AUTO_INCREMENT ,
`p_id`  int(11) NULL DEFAULT NULL ,
`c_name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`i_id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=11
ROW_FORMAT=COMPACT;

隨便插入幾條數(shù)據(jù)

實現(xiàn)介紹兩個關鍵的函數(shù)

group_concat（column_name）: 多條記錄合成一條記錄

SELECT GROUP_CONCAT(i_id)
FROM city
WHERE p_id='2'

find_in_set（column_name， strlist）在多條記錄中查詢特定列

SELECT * FROM city
WHERE FIND_IN_SET(p_id,'1,4')

4.具體的實現(xiàn)(由淺入深)

1.只查具體一級下級

SELECT GROUP_CONCAT(i_id) AS ids
FROM city
WHERE p_id=1;

2.查詢某一個節(jié)點的子節(jié)點

在存貯函數(shù)之前，要注意一點。雖然參數(shù)名是 param_i_id，但查詢時要讓p_id=param_i_id

CREATE DEFINER = `root`@`localhost` FUNCTION `getSubNodes`(param_i_id  int)
 RETURNS varchar(100)
BEGIN
 
#輸入I_ID 輸出此I_ID下的所有子節(jié)點(只有一級)
 
  DECLARE strSubIds VARCHAR(100);
  DECLARE strPid VARCHAR(100);
  SET strSubIds = '$';
  SET strPid =cast(param_i_id as CHAR);
  SELECT GROUP_CONCAT(i_id) INTO strSubIds FROM city WHERE p_id=strPid;
  RETURN strSubIds;
END;

這樣，就可以在查詢語句中使用

3.查詢所有下級

查到了一級，只要循環(huán)查詢到某一個節(jié)點(這個節(jié)點沒有子節(jié)點)，就結束

CREATE DEFINER = `root`@`localhost` FUNCTION `getAllSubNodes`(`param_i_id` int)
 RETURNS varchar(100)
BEGIN
 
DECLARE strAllSubIds VARCHAR(100);
DECLARE strTempPid VARCHAR(100);
 
#先得到第一級,也可以掉用getSubNodes(param_i_id);
 
# SELECT GROUP_CONCAT(i_id) INTO strAllSubIds FROM city WHERE p_id=param_i_id;
 SET strAllSubIds = getSubNodes(param_i_id);
SET strTempPid = strAllSubIds;
 
#根據(jù) strTempPid 判斷是否還有子節(jié)點
 
WHILE strTempPid is not null DO
SELECT group_concat(i_id) INTO strTempPid FROM city WHERE FIND_IN_SET(p_id,strTempPid)>0;
 
#需要對strTempPid判斷,非空用 , 連接
 
IF (strTempPid is not NULL) THEN
SET strAllSubIds = concat(strAllSubIds,',',strTempPid);
 
END IF;
END WHILE;
RETURN strAllSubIds;
END;