同事問我MySQL怎么遞回查詢，我懵逼了

前言

最近在做的業務場景涉及到了資料庫的遞回查詢，我們公司用的 Oracle ，眾所周知，Oracle 自帶有遞回查詢的功能，所以實作起來特別簡單，

但是，我記得 MySQL 是沒有遞回查詢功能的，那 MySQL 中應該怎么實作呢？

于是，就有了這篇文章，

文章主要知識點：

• Oracle 遞回查詢， start with connect by prior 用法
• find_in_set 函式
• concat，concat_ws，group_concat 函式
• MySQL 自定義函式
• 手動實作 MySQL 遞回查詢

Oracle 遞回查詢

在 Oracle 中是通過 start with connect by prior 語法來實作遞回查詢的，

按照 prior 關鍵字在子節點端還是父節點端，以及是否包含當前查詢的節點，共分為四種情況，

prior 在子節點端（向下遞回）

第一種情況： start with 子節點id = ' 查詢節點 ' connect by prior 子節點id = 父節點id

select * from dept start with id='1001' connet by prior id=pid;

這里，按照條件 id='1001' 對當前節點以及它的子節點遞回查詢，查詢結果包含自己及所有子節點，

第二種情況： start with 父節點id= ' 查詢節點 ' connect by prior 子節點id = 父節點 id

select * from dept start with pid='1001' connect by prior id=pid;

這里，按照條件 pid='1001' 對當前節點的所有子節點遞回查詢，查詢結果只包含它的所有子節點，不包含自己，

其實想一想也對，因為開始條件是以父節點為根節點，且向下遞回，自然不包含當前節點，

prior 在父節點端（向上遞回）

第三種情況： start with 子節點id= ' 查詢節點 ' connect by prior 父節點id = 子節點id

select * from dept start with id='1001' connect by prior pid=id;

這里按照條件 id='1001' ，對當前節點及其父節點遞回查詢，查詢結果包括自己及其所有父節點，

第四種情況： start with 父節點id= ' 查詢節點 ' connect by prior 父節點id = 子節點id

select * from dept start with pid='1001' connect by prior pid=id;

這里按照條件 pid='1001'，對當前節點的第一代子節點以及它的父節點遞回查詢，查詢結果包括自己的第一代子節點以及所有父節點，（包括自己）

其實這種情況也好理解，因為查詢開始條件是以 父節點為根節點，且向上遞回，自然需要把當前父節點的第一層子節點包括在內，

以上四種情況初看可能會讓人迷惑，容易記混亂，其實不然，

我們只需要記住 prior 的位置在子節點端，就向下遞回，在父節點端就向上遞回，

• 開始條件若是子節點的話，自然包括它本身的節點，
• 開始條件若是父節點的話，則向下遞回時，自然不包括當前節點，而向上遞回，需要包括當前節點及其第一代子節點，

MySQL 遞回查詢

可以看到，Oracle 實作遞回查詢非常的方便，但是，在 MySQL 中并沒有幫我們處理，因此需要我們自己手動實作遞回查詢，

為了方便，我們創建一個部門表，并插入幾條可以形成遞回關系的資料，

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `pid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1000', '總公司', NULL);
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1001', '北京分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1002', '上海分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1003', '北京研發部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1004', '北京財務部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1005', '北京市場部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1006', '北京研發一部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1007', '北京研發二部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1008', '北京研發一部一小組', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1009', '北京研發一部二小組', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1010', '北京研發二部一小組', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1011', '北京研發二部二小組', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1012', '北京市場一部', '1005');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1013', '上海研發部', '1002');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1014', '上海研發一部', '1013');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1015', '上海研發二部', '1013');

沒錯，剛才 Oracle 遞回，就是用的這張表，

另外，在這之前，我們需要復習一下幾個 MYSQL中的函式，后續會用到，

find_in_set 函式

函式語法：find_in_set(str,strlist)

str 代表要查詢的字串 ， strlist 是一個以逗號分隔的字串，如 ('a,b,c')，

此函式用于查找 str 字串在字串 strlist 中的位置，回傳結果為 1 ~ n ，若沒有找到，則回傳0，

舉個栗子：

select FIND_IN_SET('b','a,b,c,d'); 

結果回傳 2 ，因為 b 所在位置為第二個子串位置，

此外，在對表資料進行查詢時，它還有一種用法，如下：

select * from dept where FIND_IN_SET(id,'1000,1001,1002'); 

結果回傳所有 id 在 strlist 中的記錄，即 id = '1000' ，id = '1001' ，id = '1002' 三條記錄，

看到這，對于我們要解決的遞回查詢，不知道你有什么啟發沒，

以向下遞回查詢所有子節點為例，我想，是不是可以找到一個包含當前節點和所有子節點的以逗號拼接的字串 strlist，傳進 find_in_set 函式，就可以查詢出所有需要的遞回資料了，

那么，現在問題就轉化為怎樣構造這樣的一個字串 strlist ，

這就需要用到以下字串拼接函式了，

concat，concat_ws，group_concat 函式

一、字串拼接函式中，最基本的就是 concat 了，它用于連接N個字串，如，

select CONCAT('M','Y','S','Q','L') from dual; 

結果為 'MYSQL' 字串，

二、concat 是以逗號為默認的分隔符，而 concat_ws 則可以指定分隔符，第一個引數傳入分隔符，如以下劃線分隔，

三、group_concat 函式更強大，可以分組的同時，把欄位以特定分隔符拼接成字串，

用法：group_concat( [distinct] 要連接的欄位 [order by 排序欄位 asc/desc ] [separator '分隔符'] )

可以看到有可選引數，可以對將要拼接的欄位值去重，也可以排序，指定分隔符，若沒有指定，默認以逗號分隔，

對于 dept 表，我們可以把表中的所有 id 以逗號拼接，（這里沒有用到 group by 分組欄位，則可以認為只有一組）

MySQL 自定義函式，實作遞回查詢

可以發現以上已經把字串拼接的問題也解決了，那么，問題就變成怎樣構造有遞回關系的字串了，

我們可以自定義一個函式，通過傳入根節點id，找到它的所有子節點，

以向下遞回為例， （講解自定義函式寫法的同時，講解遞回邏輯）

delimiter $$ 
drop function if exists get_child_list$$ 
create function get_child_list(in_id varchar(10)) returns varchar(1000) 
begin 
	declare ids varchar(1000) default ''; 
	declare tempids varchar(1000); 
 
	set tempids = in_id; 
	while tempids is not null do 
		set ids = CONCAT_WS(',',ids,tempids); 
		select GROUP_CONCAT(id) into tempids from dept where FIND_IN_SET(pid,tempids)>0;  
	end while; 
	return ids; 
end  
$$ 
delimiter ; 

（1） delimiter $$ ，用于定義結束符，我們知道 MySQL 默認的結束符為分號，表明指令結束并執行，但是在函式體中，有時我們希望遇到分號不結束，因此需要暫時把結束符改為一個隨意的其他值，我這里設定為 $$，意思是遇到 $$ 才結束，并執行當前陳述句，

（2）drop function if exists get_child_list$$ ，若函式 get_child_list 已經存在了，則先洗掉它，注意這里需要用 當前自定義的結束符 $$ 來結束并執行陳述句， 因為，這里需要和下邊的函式體單獨區分開來，

（3）create function get_child_list 創建函式，并且引數傳入一個根節點的子節點id，需要注意一定要注明引數的型別和長度，如這里是 varchar(10)，returns varchar(1000) 用來定義回傳值引數型別，

（4）begin 和 end 中間包圍的就是函式體，用來寫具體的邏輯，

（5）declare 用來宣告變數，并且可以用 default 設定默認值，

這里定義的 ids 即作為整個函式的回傳值，是用來拼接成最終我們需要的以逗號分隔的遞回串的，

而 tempids 是為了記錄下邊 while 回圈中臨時生成的所有子節點以逗號拼接成的字串，

（6） set 用來給變數賦值，此處把傳進來的根節點賦值給 tempids ，

（7） while do ... end while; 回圈陳述句，回圈邏輯包含在內，注意，end while 末尾需要加上分號，

回圈體內，先用 CONCAT_WS 函式把最終結果 ids 和 臨時生成的 tempids 用逗號拼接起來，

然后以 FIND_IN_SET(pid,tempids)>0 為條件，遍歷在 tempids 中的所有 pid ，尋找以此為父節點的所有子節點 id ，并且通過 GROUP_CONCAT(id) into tempids 把這些子節點 id 都用逗號拼接起來，并覆寫更新 tempids ，

等下次回圈進來時，就會再次拼接 ids ，并再次查找所有子節點的所有子節點，回圈往復，一層一層的向下遞回遍歷子節點，直到判斷 tempids 為空，說明所有子節點都已經遍歷完了，就結束整個回圈，

這里，用 '1000' 來舉例，即是：（參看圖1的表資料關系）

第一次回圈：
  tempids=1000	ids=1000	tempids=1001,1002 (1000的所有子節點)
第二次回圈：
  tempids=1001,1002	 ids=1000,1001,1002	 tempids=1003,1004,1005,1013 (1001和1002的所有子節點)
第三次回圈：
  tempids=1003,1004,1005,1013 
  ids=1000,1001,1002,1003,1004,1005,1013 
  tempids=1003和1004和1005及1013的所有子節點
...
最后一次回圈，因找不到子節點，tempids=null，就結束回圈，

（8）return ids; 用于把 ids 作為函式回傳值回傳，

（9）函式體結束以后，記得用結束符 $$ 來結束整個邏輯，并執行，

（10）最后別忘了，把結束符重新設定為默認的結束符分號 ，

自定義函式做好之后，我們就可以用它來遞回查詢我們需要的資料了，如，我查詢北京研發部的所有子節點，

以上是向下遞回查詢所有子節點的，并且包括了當前節點，也可以修改邏輯為不包含當前節點，我就不演示了，

手動實作遞回查詢（向上遞回）

相對于向下遞回來說，向上遞回比較簡單，

因為向下遞回時，每一層遞回一個父節點都對應多個子節點，

而向上遞回時，每一層遞回一個子節點只對應一個父節點，關系比較單一，

同樣的，我們可以定義一個函式 get_parent_list 來獲取根節點的所有父節點，

delimiter $$ 
drop function if exists get_parent_list$$ 
create function get_parent_list(in_id varchar(10)) returns varchar(1000) 
begin 
	declare ids varchar(1000); 
	declare tempid varchar(10); 
	 
	set tempid = in_id; 
	while tempid is not null do 
		set ids = CONCAT_WS(',',ids,tempid); 
		select pid into tempid from dept where id=tempid; 
	end while; 
	return ids; 
end 
$$ 
delimiter ; 
 

查找北京研發二部一小組，以及它的遞回父節點，如下：

注意事項

我們用到了 group_concat 函式來拼接字串，但是，需要注意它是有長度限制的，默認為 1024 位元組，可以通過 show variables like "group_concat_max_len"; 來查看，

注意，單位是位元組，不是字符，在 MySQL 中，單個字母占1個位元組，而我們平時用的 utf-8下，一個漢字占3個位元組，

這個對于遞回查詢還是非常致命的，因為一般遞回的話，關系層級都比較深，很有可能超過最大長度，（盡管一般拼接的都是數字字串，即單位元組）

所以，我們有兩種方法解決這個問題：

1. 修改 MySQL 組態檔 my.cnf ，增加 group_concat_max_len = 102400 #你要的最大長度 ，

2. 執行以下任意一個陳述句，SET GLOBAL group_concat_max_len=102400; 或者 SET SESSION group_concat_max_len=102400;

   他們的區別在于，global是全域的，任意打開一個新的會話都會生效，但是注意，已經打開的當前會話并不會生效，而 session 是只會在當前會話生效，其他會話不生效，

   共同點是，它們都會在 MySQL 重啟之后失效，以組態檔中的配置為準，所以，建議直接修改組態檔，102400 的長度一般也夠用了，假設一個id的長度為10個位元組，也能拼上一萬個id了，

除此之外，使用 group_concat 函式還有一個限制，就是不能同時使用 limit ，如，

本來只想查5條資料來拼接，現在不生效了，

不過，如果需要的話，可以通過子查詢來實作，

標籤：Java

上一篇：JDBC(3)-資料庫事務

下一篇：精講RestTemplate第2篇-多種底層HTTP客戶端類別庫的切換