|
??對于數(shù)據(jù)庫中的樹形結(jié)構(gòu)數(shù)據(jù)，我們經(jīng)常會有一種需求，給定一個父節(jié)點，查詢這個父節(jié)點下所有的子節(jié)點，或者給定一個子節(jié)點，查詢這個子節(jié)點上的所有父節(jié)點。
接下來，我將介紹如何在MySql中使用函數(shù)來實現(xiàn)遞歸。
1.創(chuàng)建表
DROP TABLE IF EXISTS `address`;
CREATE TABLE `address` (
`id` bigint(20) AUTO_INCREMENT COMMENT '主鍵',
`address_name` varchar(500) DEFAULT NULL,
`parent_id` bigint(20) DEFAULT 0 COMMENT '父節(jié)點',
`level_path` varchar(2000) DEFAULT NULL COMMENT '地址路徑',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.初始化數(shù)據(jù)
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('中國',0, '中國');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('河南省',3 , '中國/河南省');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('鄭州市',4 , '中國/河南省/鄭州市');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('1級縣城',5 , '中國/河南省/鄭州市/1級縣城');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('2級縣城',5 , '中國/河南省/鄭州市/2級縣城');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('3級縣城',5 , '中國/河南省/鄭州市/3級縣城');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('A鄉(xiāng)',6, '中國/河南省/鄭州市/1級縣城/A鄉(xiāng)');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('B鄉(xiāng)',6, '中國/河南省/鄭州市/1級縣城/B鄉(xiāng)');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('C鄉(xiāng)',6, '中國/河南省/鄭州市/1級縣城/C鄉(xiāng)');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('A-1村',9, '中國/河南省/鄭州市/1級縣城/A鄉(xiāng)/A-1村');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('B-1村',10, '中國/河南省/鄭州市/1級縣城/B鄉(xiāng)/B-1村');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('C-1村',11, '中國/河南省/鄭州市/1級縣城/C鄉(xiāng)/C-1村');
3.查詢鄭州市下所有的子地區(qū)
- 在這里多說一句，如果是Oracle我們直接可以根據(jù)start with connect by prior遞歸來實現(xiàn)
SELECT *
FROM address
START WITH address_name='鄭州市'
CONNECT BY PRIOR ID=parent_id
- 如果是mysql的話，我們可以使用函數(shù)來實現(xiàn)遞歸查詢
在可視化工具navicat下創(chuàng)建函數(shù)步驟如下：
-然后如數(shù)返回值類型和長度
BEGIN
# 定義一個變量用來返回結(jié)果
DECLARE finalVar VARCHAR(2000);
# 定義一個臨時變量
DECLARE tempVar VARCHAR(2000);
# 設(shè)置默認(rèn)值
SET finalVar='$';
# 轉(zhuǎn)換入?yún)㈩愋?SET tempVar = CAST(parent_id AS CHAR);
# 循環(huán)體，如果當(dāng)前的臨時變量中沒有值，為空的情況下跳出循環(huán)，也就是說沒有子節(jié)點了
WHILE tempVar IS NOT NULL DO
# 將得到的子節(jié)點保存到變量中
SET finalVar= CONCAT(finalVar,',',tempVar);
# 根據(jù)父Id查詢所有的子節(jié)點
SELECT GROUP_CONCAT(t.id) INTO tempVar FROM address t WHERE FIND_IN_SET(t.parent_id,tempVar)>0;
# 結(jié)束循環(huán)
END WHILE;
# 返回結(jié)果,得到的是包含入?yún)⒁约跋旅娴乃凶庸?jié)點
RETURN finalVar;
END
select selectChildByParentId(5);
得到父節(jié)點5下面的所有子節(jié)點
- 此時可以查詢父節(jié)點為鄭州市下面的所有子區(qū)域了
SELECT * FROM address WHERE FIND_IN_SET(id,selectChildByParentId(5));
4. 當(dāng)然了我們也可以查詢鄭州市上的所有父節(jié)點
??同樣的我們寫一個函數(shù)用來獲取鄭州市的所有父節(jié)點。
BEGIN
# 定義一個變量用來返回結(jié)果
DECLARE finalVar VARCHAR(2000);
# 定義一個臨時變量
DECLARE tempVar BIGINT;
# 設(shè)置默認(rèn)值
SET finalVar='$';
# 轉(zhuǎn)換入?yún)㈩愋?SET tempVar =son_id;
# 循環(huán)體，如果當(dāng)前的父節(jié)點為0，那么說明已經(jīng)查詢完畢了
WHILE tempVar <> 0 DO
# 將得到的子節(jié)點保存到變量中
SET finalVar= CONCAT(finalVar,',',tempVar);
# 根據(jù)子Id查詢父Id
SELECT t.parent_id INTO tempVar FROM address t WHERE t.id=tempVar;
# 結(jié)束循環(huán)
END WHILE;
# 返回結(jié)果,得到的是包含入?yún)⒁约跋旅娴乃凶庸?jié)點
RETURN finalVar;
END
SELECT * FROM address WHERE FIND_IN_SET(id,selectParentByChildId(5));
結(jié)果如下：
本文中出現(xiàn)的find_in_set()函數(shù)可以參考：
mysql中find_in_set()函數(shù)的使用
本文參考地址：https://www.cnblogs.com/duanrantao/p/9359137.html
|
