快捷導(dǎo)航

MySQL遞歸查找樹形結(jié)構(gòu)(這個(gè)方法太實(shí)用了!)

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

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

1、數(shù)據(jù)庫中的樹形結(jié)構(gòu)

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

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

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

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

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

3、準(zhǔn)備工作

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

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

實(shí)現(xiàn)介紹兩個(gè)關(guā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.具體的實(shí)現(xiàn)(由淺入深)

1.只查具體一級下級

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

2.查詢某一個(gè)節(jié)點(diǎn)的子節(jié)點(diǎn)

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

CREATE DEFINER = `root`@`localhost` FUNCTION `getSubNodes`(param_i_id  int)
 RETURNS varchar(100)
BEGIN
 
#輸入I_ID 輸出此I_ID下的所有子節(jié)點(diǎn)(只有一級)
 
  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)查詢到某一個(gè)節(jié)點(diǎn)(這個(gè)節(jié)點(diǎn)沒有子節(jié)點(diǎn))，就結(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é)點(diǎn)
 
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;