概述

前幾日有客戶咨詢關于mysql實作遞歸查詢的方法，當時簡單了解了一下，覺得遞歸查詢邏輯層面一種特殊查詢方式。但是後來才發現這是一種很常見的查詢需求，例如某些評論樓層的折疊顯示、各類流程圖等用遞歸查詢都能實作。但是MySQL本身而言是沒有實作遞歸查詢功能，但是可以通過一些特殊的方法來實作此功能，本文就簡單測試一些MySQL實作遞歸查詢的方法

測試環境

測試環境是RDS for MySQL 5.7，測試的表的主要邏輯就是省份--城市--市區，為了實作這個邏輯，先要準備好表與資料，如下

測試表

CREATE TABLE `recursion_test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `parent_id` int(11) NOT NULL,
 `name` varchar(32) NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='遞歸測試表';

主鍵遞增，ID與父ID，然後就是名字。表的邏輯需要通過遞歸查詢才能實作

插入測試資料

插入資料如下

INSERT INTO recursion_test VALUES(1,1,'浙江省');
INSERT INTO recursion_test VALUES(2,2,'江蘇省');
INSERT INTO recursion_test VALUES(3,3,'安徽省');

INSERT INTO recursion_test VALUES(4,1,'杭州市');
INSERT INTO recursion_test VALUES(5,1,'甯波市');
INSERT INTO recursion_test VALUES(6,1,'金華市');


INSERT INTO recursion_test VALUES(7,2,'南京市');
INSERT INTO recursion_test VALUES(8,2,'蘇州市');
INSERT INTO recursion_test VALUES(9,2,'徐州市');


INSERT INTO recursion_test VALUES(10,3,'合肥市');
INSERT INTO recursion_test VALUES(11,3,'蕪湖市');
INSERT INTO recursion_test VALUES(12,3,'池州市');


INSERT INTO recursion_test VALUES(13,4,'西湖區');
INSERT INTO recursion_test VALUES(14,4,'濱江區');
INSERT INTO recursion_test VALUES(15,4,'餘杭區');

INSERT INTO recursion_test VALUES(16,5,'海曙區');
INSERT INTO recursion_test VALUES(17,5,'江北區');
INSERT INTO recursion_test VALUES(18,5,'鎮海區');

INSERT INTO recursion_test VALUES(19,6,'婺城區');
INSERT INTO recursion_test VALUES(20,6,'金東區');
INSERT INTO recursion_test VALUES(21,6,'永康市');

INSERT INTO recursion_test VALUES(22,7,'玄武區');
INSERT INTO recursion_test VALUES(23,7,'秦淮區');
INSERT INTO recursion_test VALUES(24,7,'建邺區');

INSERT INTO recursion_test VALUES(25,8,'滄浪區');
INSERT INTO recursion_test VALUES(26,8,'平江區');
INSERT INTO recursion_test VALUES(27,8,'虎丘區');

INSERT INTO recursion_test VALUES(28,9,'雲龍區');
INSERT INTO recursion_test VALUES(29,9,'鼓樓區');
INSERT INTO recursion_test VALUES(30,9,'泉山區');

INSERT INTO recursion_test VALUES(31,10,'蜀山區');
INSERT INTO recursion_test VALUES(32,10,'廬陽區');
INSERT INTO recursion_test VALUES(33,10,'瑤海區');

INSERT INTO recursion_test VALUES(34,11,'鏡湖區');
INSERT INTO recursion_test VALUES(35,11,'鸠江區');
INSERT INTO recursion_test VALUES(36,11,'弋江區');

INSERT INTO recursion_test VALUES(37,12,'貴池區');
INSERT INTO recursion_test VALUES(38,12,'九華山區');
INSERT INTO recursion_test VALUES(39,12,'青陽');

OK，準備好無趣的資料，開始試着做做遞歸查詢

開始測試

使用表連接配接

若确定所需查詢樹的最大深度。則可以直接使用left join來實作，每有一級遞歸就做一次join。例如ID=父_ID,選取相應的字段就可以按照遞歸順序查詢出來

我們這個表的邏輯遞歸層隻有三層，理論上隻要做兩次表連接配接即可查詢，如下，查詢

mysql>SELECT t1.name as '省份',t2.name as '城市',t3.name as '市區'
FROM recursion_test t1
LEFT JOIN recursion_test t2 ON t1.id = t2.parent_id
LEFT JOIN recursion_test t3 ON t2.id = t3.parent_id
WHERE t1.id = '1' and t2.id <> 1;
+----------------+----------------+----------------+
| 省份 | 城市 | 市區 |
+----------------+----------------+----------------+
| 浙江省 | 杭州市 | 西湖區 |
| 浙江省 | 杭州市 | 濱江區 |
| 浙江省 | 杭州市 | 餘杭區 |
| 浙江省 | 甯波市 | 海曙區 |
| 浙江省 | 甯波市 | 江北區 |
| 浙江省 | 甯波市 | 鎮海區 |
| 浙江省 | 金華市 | 婺城區 |
| 浙江省 | 金華市 | 金東區 |
| 浙江省 | 金華市 | 永康市 |
+----------------+----------------+----------------+

臨時表+存儲過程

第一個存儲過程負責将每個節點的資料寫到臨時表中，遞歸查詢到最底層的節點

CREATE PROCEDURE `findtestList`() 
 COMMENT '遞歸查詢' 
BEGIN
  DECLARE v_test VARCHAR(20) DEFAULT '';
  DECLARE done INTEGER DEFAULT 0;
    -- 查詢結果放入遊标中
  DECLARE C_test CURSOR FOR SELECT d.id
                           FROM recursion_test d
                           WHERE d.parent_id = testId;
  DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
  SET @@max_sp_recursion_depth = 10;
    
    -- 傳入的組織id寫入臨時表
  INSERT INTO tmp_test VALUES (testId);
  OPEN C_test;
  FETCH C_test INTO v_test;
  WHILE (done=0)
  DO
        -- 遞歸調用，查找下級
    CALL findtestList(v_test);
    FETCH C_test INTO v_test;
  END WHILE;
  CLOSE C_test;
END

第二個存儲過程是負責建立和删除臨時表，并且調用第一個存儲過程進行表的資料的輸出

CREATE DEFINER=`root`@`%` PROCEDURE `recursion_testList`(
  IN testid VARCHAR(20)
)
    DETERMINISTIC
    COMMENT '臨時表'
BEGIN
 DROP TEMPORARY TABLE IF EXISTS tmp_test;
    -- 建立臨時表
    CREATE TEMPORARY TABLE tmp_test(testid VARCHAR(20));
    -- 清空臨時表資料
    DELETE FROM tmp_test;
    -- 發起調用
    CALL findtestList(testId);
    -- 從臨時表查詢結果
    select * from recursion_test where id in (SELECT * FROM tmp_test ORDER BY testid);
END

做幾次測試查詢

mysql>call recursion_testList(2)
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 2 | 0 | 江蘇省 |
| 7 | 2 | 南京市 |
| 22 | 7 | 玄武區 |
| 23 | 7 | 秦淮區 |
| 24 | 7 | 建邺區 |
| 8 | 2 | 蘇州市 |
| 25 | 8 | 滄浪區 |
| 26 | 8 | 平江區 |
| 27 | 8 | 虎丘區 |
| 9 | 2 | 徐州市 |
| 28 | 9 | 雲龍區 |
| 29 | 9 | 鼓樓區 |
| 30 | 9 | 泉山區 |
+--------------+---------------------+----------------+


mysql>call recursion_testList(8)
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 8 | 2 | 蘇州市 |
| 25 | 8 | 滄浪區 |
| 26 | 8 | 平江區 |
| 27 | 8 | 虎丘區 |
+--------------+---------------------+----------------+

使用函數

使用自定義函數也可以實作遞歸查詢，個人覺得自定義函數實作遞歸查詢最好的方法，靈活多變。

自上而下實作查詢，比如查詢一個城市，顯示這個城市下所有的區域等

CREATE DEFINER=`root`@`%` FUNCTION `findtest_down`(rootId INT) RETURNS varchar(4000) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE sTemp VARCHAR(4000);
  DECLARE sTempChd VARCHAR(4000);
  SET sTemp = '$';
  SET sTempChd = CAST(rootId as CHAR);
  WHILE sTempChd is not null DO
    SET sTemp = CONCAT(sTemp,',',sTempChd);
    SELECT GROUP_CONCAT(id) INTO sTempChd FROM recursion_test
        WHERE FIND_IN_SET(parent_id,sTempChd)>0;
  END WHILE;
  RETURN sTemp;
END

其中CONCAT和FIND_IN_SET函數的作用是：

GROUP_CONCAT(expr)

該函數會從expr中連接配接所有非NULL的字元串。如果沒有非 NULL 的字元串，那麼它就會傳回NULL。

注意事項：GROUP_CONCAT查詢結果預設最大長度限制為1024，該值是系統變量group_concat_max_len的預設值，可以通過SET [GLOBAL | SESSION] group_concat_max_len = val;更改該值。

FIND_IN_SET(str,strlist)

該函數傳回一個1～N的值表示str在strlist中的位置。

該函數結合WHERE使用對結果集進行過過濾(查找str包含在strlist結果集裡面的記錄)

可以直接查詢某個省下的所有城市和時區

mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_down(1));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 1 | 0 | 浙江省 |
| 4 | 1 | 杭州市 |
| 5 | 1 | 甯波市 |
| 6 | 1 | 金華市 |
| 13 | 4 | 西湖區 |
| 14 | 4 | 濱江區 |
| 15 | 4 | 餘杭區 |
| 16 | 5 | 海曙區 |
| 17 | 5 | 江北區 |
| 18 | 5 | 鎮海區 |
| 19 | 6 | 婺城區 |
| 20 | 6 | 金東區 |
| 21 | 6 | 永康市 |
+--------------+---------------------+----------------+

查詢一下ID為6 金華市和ID為7南京的下屬市區

mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_down(6));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 6 | 1 | 金華市 |
| 19 | 6 | 婺城區 |
| 20 | 6 | 金東區 |
| 21 | 6 | 永康市 |
+--------------+---------------------+----------------+
傳回行數：[4]，耗時：9 ms.
mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_down(7));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 7 | 2 | 南京市 |
| 22 | 7 | 玄武區 |
| 23 | 7 | 秦淮區 |
| 24 | 7 | 建邺區 |
+--------------+---------------------+----------------+

自下而上，查詢一個地點的所屬城市和地區

建立函數

CREATE DEFINER=`root`@`%` FUNCTION `findtest_up`(rootId INT) RETURNS varchar(4000) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE sTemp VARCHAR(4000);
  DECLARE sTempChd VARCHAR(4000);
  
  SET sTemp = '$';
  SET sTempChd = CAST(rootId as CHAR);
  SET sTemp = CONCAT(sTemp,',',sTempChd);
  
  SELECT parent_id INTO sTempChd FROM recursion_test WHERE id = sTempChd;
  WHILE sTempChd <> 0 DO
    SET sTemp = CONCAT(sTemp,',',sTempChd);
    SELECT parent_id INTO sTempChd FROM recursion_test WHERE id = sTempChd;
  END WHILE;
  RETURN sTemp;
END

試着做幾次查詢

mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_up(39));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 3 | 0 | 安徽省 |
| 12 | 3 | 池州市 |
| 39 | 12 | 青陽 |
+--------------+---------------------+----------------+

mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_up(30));
+--------------+---------------------+----------------+
| id | parent_id | name |
+--------------+---------------------+----------------+
| 2 | 0 | 江蘇省 |
| 9 | 2 | 徐州市 |
| 30 | 9 | 泉山區 |
+--------------+---------------------+----------------+

MySQL實作遞歸查詢概述測試環境開始測試

概述

測試環境

測試表

插入測試資料

開始測試

使用表連接配接

臨時表+存儲過程

使用函數

繼續閱讀

轉詳解C#資料庫存取圖檔三大方式

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案