在日常开发中我们经常会遇到树形结构数据的处理，如：组织机构之类的情况。在表结构通常会采用id、parent_id这种设计方案。一个常见的需求：查询某个节点下的所有子节点。
为方便后续说明，在此统一约定表名为:t_org，其定义如下：
| 字段 | 类型 | 说明 |
|---|---|---|
| id | bigint(20) NOT NULL | 机构编码 |
| parent_id | bigint(20) | 上级机构编码 |
| desc | varchar(200) | 备注 |
查询实现方案
表中现有如下测试数据
组织机构层级数确定时
可以采用自关联LEFT JOIN方式进行查询获取结果。
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';
查询结果如下；
组织机构层级数不确定时
当组织机构层级数不确定时，无法使用上述方式进行查询
可以通过自定义函数方式实现查询
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;
在上面函数中使用到了两个MySQL函数
GROUP_CONCAT(expr)
该函数会从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查询结果默认最大长度限制为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结果集里面的记录)
函数使用方式
SELECT * FROM t_org
WHERE FIND_IN_SET(id,findChildren(1)) > 0;
方案缺点
返回结果长度受VARCHAR最大长度限制，特别是当组织机构比较庞大时该方案会失效。下面我们可以使用存储过程结合临时表来解决这个问题。
存储过程+临时表
使用存储过程结合临时表的方案需要创建两个存储过程，一个用于递归查询所有节点并将数据写入临时表中，另一个负责创建临时表、清空临时表数据，触发查询调用动作。
首先，定义第一个存储过程，如下：
CREATE DEFINER=`root`@`localhost` PROCEDURE `findOrgChildList`(IN orgId VARCHAR(20))
BEGIN
DECLARE v_org VARCHAR(20) DEFAULT '';
DECLARE done INTEGER DEFAULT 0;
-- 查询结果放入游标中
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
-- 递归调用，查找下级
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;
-- 创建临时表
CREATE TEMPORARY TABLE tmp_org(org_id VARCHAR(20));
-- 清空临时表数据
DELETE FROM tmp_org;
-- 发起调用
CALL findOrgChildList(orgId);
-- 从临时表查询结果
SELECT org_id FROM tmp_org ORDER BY org_id;
END
使用方式如下
CALL findOrgList(org_id);
至此，我们在可以处理无限层级的树形结构数据。
MyBatis调用存储过程
在MyBatis中我们可以使用如下方式对存储过程进行调用
<select id="selectOrgChildList" resultType="java.lang.String" statementType="CALLABLE">
<![CDATA[
CALL findOrgList(
#{orgId,mode=IN,jdbcType=VARCHAR},
]]>
</select>
需要指定statementType为CALLABLE表示需要执行的是一个存储过程，statementType默认值为PREPARED。
总结
存储过程和函数的方式虽然简化了代码处理逻辑，但是使用函数和存储过程也有其缺点，主要体现在函数和存储过程在线上业务中其性能不容易监控，针对慢查询优化等方面从DBA角度来讲不是那么方便，所以在使用函数和存储过程时需要进行相应的权衡。
