MySQL5.7不支持递归时如何查询自底向上完整层级树

users表用于存储人员汇报层级关系，表结构包含Id、FirstName、ReportingTo三个字段，示例数据如下：

需求为实现自底向上的层级查询：传入指定Id时返回完整上级链路，例如传入Id=23时，需返回结果20,12,9,6。因当前使用MySQL 5.7版本，不支持WITH Recursive递归CTE语法，原有自定义变量查询语句仅能返回单行结果，无法获取完整层级链路。

原查询语句的匹配逻辑完全倒置：初始变量@pv赋值为传入的起始Id（示例为23），但语句判断条件为find_in_set(reporting_to, @pv)，即找reporting_to等于23的行，实际表中不存在该数据，仅能在变量拼接后拿到第一级匹配结果，无法遍历完整链路。

方案1：修正自定义变量查询逻辑 #

调整匹配规则，改为先匹配id在当前变量集合内的行，再将对应行的ReportingTo字段值拼入变量集合，持续遍历直到没有匹配项即可。语句如下：

SELECT 
  id,
  FirstName,
  ReportingTo
FROM (
  -- 子查询排序保证遍历顺序稳定
  SELECT * FROM users ORDER BY id DESC
) sorted_users,
(SELECT @pv := '23') init_var
WHERE FIND_IN_SET(id, @pv) > 0
AND @pv := CONCAT(@pv, ',', ReportingTo);

如果需要排除传入的起始节点、仅保留上级链路，可以在外层增加过滤条件，或使用GROUP_CONCAT直接聚合得到逗号分隔的链路字符串：

SELECT GROUP_CONCAT(ReportingTo ORDER BY id SEPARATOR ',') AS full_report_chain
FROM (
  SELECT id, ReportingTo
  FROM (
    SELECT * FROM users ORDER BY id DESC
  ) sorted_users,
  (SELECT @pv := '23') init_var
  WHERE FIND_IN_SET(id, @pv) > 0
  AND @pv := CONCAT(@pv, ',', ReportingTo)
) t
WHERE ReportingTo IS NOT NULL;

执行上述语句传入Id=23时，返回的full_report_chain值即为20,12,9,6，符合需求。

方案2：创建自定义函数（适合高频调用场景） #

如果该类层级查询使用频率较高，可以创建自定义函数封装循环查询逻辑，后续直接调用即可，无需每次编写复杂的变量查询语句：

-- 修改语句分隔符
DELIMITER //
CREATE FUNCTION getFullReportChain(start_id INT) 
RETURNS VARCHAR(1000)
BEGIN
  DECLARE chain_result VARCHAR(1000) DEFAULT '';
  DECLARE current_check_id INT DEFAULT start_id;
  DECLARE temp_report_to INT;

  -- 循环向上查找上级
  find_parent: LOOP
    SELECT ReportingTo INTO temp_report_to 
    FROM users WHERE id = current_check_id;
    
    -- 找到顶层节点（无上级）时终止循环
    IF temp_report_to IS NULL THEN
      LEAVE find_parent;
    END IF;
    
    -- 拼接当前上级id到结果
    IF chain_result = '' THEN
      SET chain_result = temp_report_to;
    ELSE
      SET chain_result = CONCAT(chain_result, ',', temp_report_to);
    END IF;
    
    SET current_check_id = temp_report_to;
  END LOOP find_parent;

  RETURN chain_result;
END //
-- 恢复默认分隔符
DELIMITER ;

函数创建完成后，直接调用即可获取结果：

SELECT getFullReportChain(23);

调用返回结果为20,12,9,6。

注意：MySQL 5.7创建自定义函数需要提前确认log_bin_trust_function_creators参数已设置为1，否则会报函数创建权限错误。

内容的提问来源于stack exchange，提问作者Manish Sharma