MySql中使用函數實作遞歸查詢子節點和父節點

對于資料庫中的樹形結構資料，我們經常會有一種需求，給定一個父節點，查詢這個父節點下所有的子節點，或者給定一個子節點，查詢這個子節點上的所有父節點。

接下來，我将介紹如何在MySql中使用函數來實作遞歸。

1.建立表

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 '父節點',
  `level_path` varchar(2000) DEFAULT NULL COMMENT '位址路徑',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
           

2.初始化資料

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鄉',6, '中國/河南省/鄭州市/1級縣城/A鄉');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('B鄉',6, '中國/河南省/鄭州市/1級縣城/B鄉');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('C鄉',6, '中國/河南省/鄭州市/1級縣城/C鄉');


INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('A-1村',9, '中國/河南省/鄭州市/1級縣城/A鄉/A-1村');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('B-1村',10, '中國/河南省/鄭州市/1級縣城/B鄉/B-1村');
INSERT INTO `address` (address_name,parent_id,level_path) VALUES ('C-1村',11, '中國/河南省/鄭州市/1級縣城/C鄉/C-1村');
           

3.查詢鄭州市下所有的子地區

在這裡多說一句，如果是Oracle我們直接可以根據start with connect by prior遞歸來實作

SELECT *
FROM address
START WITH address_name='鄭州市'
CONNECT BY PRIOR ID=parent_id
           

如果是mysql的話，我們可以使用函數來實作遞歸查詢

在可視化工具navicat下建立函數步驟如下：

打開資料庫連接配接找到函數

右鍵->建立函數->選擇函數

輸入參數清單，這裡是函數的參數，可以是多個

然後如數傳回值類型和長度

然後點選完成，接下來就可以寫自定義函數的邏輯了

函數邏輯如下，然後儲存起一個函數名稱即可 selectChildByParentId

BEGIN
# 定義一個變量用來傳回結果
DECLARE finalVar VARCHAR(2000);
# 定義一個臨時變量
DECLARE tempVar VARCHAR(2000);

# 設定預設值
SET finalVar='$';
# 轉換入參類型
SET tempVar = CAST(parent_id AS CHAR);

# 循環體，如果目前的臨時變量中沒有值，為空的情況下跳出循環，也就是說沒有子節點了
WHILE tempVar IS NOT NULL DO

# 将得到的子節點儲存到變量中
SET finalVar= CONCAT(finalVar,',',tempVar);
# 根據父Id查詢所有的子節點
SELECT GROUP_CONCAT(t.id) INTO tempVar FROM address t WHERE FIND_IN_SET(t.parent_id,tempVar)>0;

# 結束循環
END WHILE;
# 傳回結果,得到的是包含入參以及下面的所有子節點
RETURN finalVar;
END
           

開始調用該函數：

得到父節點5下面的所有子節點

此時可以查詢父節點為鄭州市下面的所有子區域了

4. 當然了我們也可以查詢鄭州市上的所有父節點

同樣的我們寫一個函數用來擷取鄭州市的所有父節點。

函數如下：

BEGIN
# 定義一個變量用來傳回結果
DECLARE finalVar VARCHAR(2000);
# 定義一個臨時變量
DECLARE tempVar BIGINT;

# 設定預設值
SET finalVar='$';
# 轉換入參類型
SET tempVar =son_id;


# 循環體，如果目前的父節點為0，那麼說明已經查詢完畢了
WHILE tempVar <> 0 DO

# 将得到的子節點儲存到變量中
SET finalVar= CONCAT(finalVar,',',tempVar);
# 根據子Id查詢父Id
SELECT t.parent_id INTO tempVar FROM address t WHERE t.id=tempVar;

# 結束循環
END WHILE;
# 傳回結果,得到的是包含入參以及下面的所有子節點
RETURN finalVar;
END
           

查詢鄭州市的所有父節點

結果如下