在日常開發(fā)中我們經(jīng)常會遇到樹形結(jié)構(gòu)數(shù)據(jù)的處理，如：組織機構(gòu)之類的情況。在表結(jié)構(gòu)通常會采用id、parent_id這種設(shè)計方案。一個常見的需求：查詢某個節(jié)點下的所有子節(jié)點。

為方便后續(xù)說明，在此統(tǒng)一約定表名為:t_org，其定義如下：

查詢實現(xiàn)方案

表中現(xiàn)有如下測試數(shù)據(jù)

測試數(shù)據(jù)

組織機構(gòu)層級數(shù)確定時

可以采用自關(guān)聯(lián)LEFT JOIN方式進行查詢獲取結(jié)果。

SELECT t1.id,t1.name,t2.id,t2.parent_id,t2.name,t3.id,t3.parent_id,t3.name
FROM t_org t1
LEFT JOIN t_org t2 ON t1.id = t2.parent_id
LEFT JOIN t_org t3 ON t2.id = t3.parent_id
WHERE t1.id = '1';

查詢結(jié)果如下；

查詢結(jié)果

組織機構(gòu)層級數(shù)不確定時

當組織機構(gòu)層級數(shù)不確定時，無法使用上述方式進行查詢

可以通過自定義函數(shù)方式實現(xiàn)查詢

CREATE DEFINER=`root`@`localhost` FUNCTION `findChildren`(rootId INT) RETURNS VARCHAR(4000) CHARSET utf8
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 t_org
        WHERE FIND_IN_SET(parent_id,sTempChd)>0;
  END WHILE;
  RETURN sTemp;
END;

在上面函數(shù)中使用到了兩個MySQL函數(shù)

GROUP_CONCAT(expr)
該函數(shù)會從expr中連接所有非NULL的字符串。如果沒有非 NULL 的字符串，那么它就會返回NULL。語法如下：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

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

FIND_IN_SET(str,strlist)
該函數(shù)返回一個1～N的值表示str在strlist中的位置。
該函數(shù)結(jié)合WHERE使用對結(jié)果集進行過過濾(查找str包含在strlist結(jié)果集里面的記錄)

函數(shù)使用方式

SELECT * FROM t_org
WHERE FIND_IN_SET(id,findChildren(1)) > 0;

方案缺點

返回結(jié)果長度受VARCHAR最大長度限制，特別是當組織機構(gòu)比較龐大時該方案會失效。下面我們可以使用存儲過程結(jié)合臨時表來解決這個問題。

存儲過程+臨時表

使用存儲過程結(jié)合臨時表的方案需要創(chuàng)建兩個存儲過程，一個用于遞歸查詢所有節(jié)點并將數(shù)據(jù)寫入臨時表中，另一個負責創(chuàng)建臨時表、清空臨時表數(shù)據(jù)，觸發(fā)查詢調(diào)用動作。
首先，定義第一個存儲過程，如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `findOrgChildList`(IN orgId VARCHAR(20))
BEGIN
  DECLARE v_org VARCHAR(20) DEFAULT '';
  DECLARE done INTEGER DEFAULT 0;
    -- 查詢結(jié)果放入游標中
  DECLARE C_org CURSOR FOR SELECT d.id
                           FROM t_org d
                           WHERE d.parent_id = orgId;
  DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
  SET @@max_sp_recursion_depth = 10;
    
    -- 傳入的組織id寫入臨時表
 INSERT INTO tmp_org VALUES (orgId);
  OPEN C_org;
  FETCH C_org INTO v_org;
  WHILE (done=0)
  DO
        -- 遞歸調(diào)用，查找下級
    CALL findOrgChildList(v_org);
    FETCH C_org INTO v_org;
  END WHILE;
  CLOSE C_org;
END

如上所示，邏輯比較簡單。接下來定義第二個存儲過程，如下；

CREATE DEFINER=`root`@`localhost` PROCEDURE `findOrgList`(IN orgId VARCHAR(20))
BEGIN
  DROP TEMPORARY TABLE IF EXISTS tmp_org;
    -- 創(chuàng)建臨時表
    CREATE TEMPORARY TABLE tmp_org(org_id VARCHAR(20));
    -- 清空臨時表數(shù)據(jù)
    DELETE FROM tmp_org;
    -- 發(fā)起調(diào)用
    CALL findOrgChildList(orgId);
    -- 從臨時表查詢結(jié)果
    SELECT org_id FROM tmp_org ORDER BY org_id;
END

使用方式如下

CALL findOrgList(org_id);

至此，我們在可以處理無限層級的樹形結(jié)構(gòu)數(shù)據(jù)。

MyBatis調(diào)用存儲過程

在MyBatis中我們可以使用如下方式對存儲過程進行調(diào)用

<select id="selectOrgChildList" resultType="java.lang.String" statementType="CALLABLE">
        <![CDATA[
        CALL findOrgList(
        #{orgId,mode=IN,jdbcType=VARCHAR},
        ]]>
</select>

需要指定statementType為CALLABLE表示需要執(zhí)行的是一個存儲過程，statementType默認值為PREPARED。

總結(jié)

存儲過程和函數(shù)的方式雖然簡化了代碼處理邏輯，但是使用函數(shù)和存儲過程也有其缺點，主要體現(xiàn)在函數(shù)和存儲過程在線上業(yè)務(wù)中其性能不容易監(jiān)控，針對慢查詢優(yōu)化等方面從DBA角度來講不是那么方便，所以在使用函數(shù)和存儲過程時需要進行相應(yīng)的權(quán)衡。

參考

MySQL遞歸查找存儲過程

MySQL 5.7 Reference Manual

MyBatis-3 Reference