【SQL / MySQL】Hierarchical Queries (层级结构查询)

Hierarchical Queries

层级结构是表达数据的一种重要关系，在数据库设计中，如：组织机构、行政区划、菜单目录 等 都是层次关系数据的典型实例。设计方案总的来说主要是两大类。

ID/PID 设计方案

1. 原理 ：增加 parent_id 属性，存储父节点的 id ；也可增加 path 属性，存储 节点路径，优化设计；
2. 实现 ：
   ① FUNCTION

# 查询上级节点 (FUNCTION)
CREATE FUNCTION `f_getParents`(root_id BIGINT) RETURNS varchar(16383)
BEGIN
  DECLARE pTemp VARCHAR(16383) DEFAULT '';
  DECLARE cTemp BIGINT DEFAULT 0;
  WHILE root_id > 0 DO
    SET cTemp = (SELECT ${PID_NAME} FROM ${TABLE_NAME} WHERE ${ID_NAME} = root_id); 
    IF cTemp > 0 THEN
      SET pTemp = CONCAT(pTemp, ',', cTemp);
    END IF;
    SET root_id = cTemp;
  END WHILE;
  RETURN pTemp;
END;
# 调用 f_getParents
# SELECT * FROM ${TABLE_NAME} WHERE FIND_IN_SET(${ID_NAME}, f_getParents(#{id}));
SELECT t0.* FROM ${TABLE_NAME} t0, (SELECT f_getParents(#{id}) c_ids) t1 WHERE FIND_IN_SET(${ID_NAME}, c_ids);

# 查询下级节点 (FUNCTION)
CREATE FUNCTION `f_getChildren`(root_id BIGINT) RETURNS varchar(16383)
BEGIN 
  DECLARE pTemp VARCHAR(16383) DEFAULT '';  
  DECLARE cTemp VARCHAR(16383);
  DECLARE isFirst TINYINT DEFAULT 0;
  SET cTemp = CAST(root_id AS CHAR);  
  WHILE cTemp IS NOT NULL DO  
    IF isFirst > 0 THEN
      SET pTemp = CONCAT(pTemp, ',', cTemp);
    END IF;  
    SELECT GROUP_CONCAT(${ID_NAME}) INTO cTemp FROM ${TABLE_NAME} WHERE FIND_IN_SET(${PID_NAME}, cTemp) > 0;
    SET isFirst = 1;
  END WHILE;  
  RETURN pTemp;  
END;
# 调用 f_getChildren
# SELECT * FROM ${TABLE_NAME} WHERE FIND_IN_SET(${ID_NAME}, f_getChildren(#{id}));
SELECT t0.* FROM ${TABLE_NAME} t0, (SELECT f_getChildren(#{id}) c_ids) t1 WHERE FIND_IN_SET(${ID_NAME}, c_ids);

② SQL

# 查询上级节点 (SQL)
SELECT t1.*, t2.c_lv FROM ${TABLE_NAME} t1, (
  SELECT
    @id AS c_id,
    (SELECT @id := ${PID_NAME} FROM ${TABLE_NAME} WHERE ${ID_NAME} = c_id) AS ${PID_NAME},
    @lv := @lv - 1 AS c_lv
  FROM ${TABLE_NAME}, (SELECT @id := #{id}, @lv := 0) vars 
  WHERE @id > 0
) t2 WHERE t2.c_id = t1.${ID_NAME} AND t1.${ID_NAME} <> #{id}
ORDER BY c_lv, ${PID_NAME}, ${ID_NAME};

# 查询下级节点 (SQL)
SELECT t1.*, t2.c_lv FROM ${TABLE_NAME} t1, (
  SELECT
    @ids AS c_ids,
    (SELECT @ids := GROUP_CONCAT(${ID_NAME}) FROM ${TABLE_NAME} WHERE FIND_IN_SET(${PID_NAME}, @ids)) AS c1,
    @lv := @lv + 1 AS c_lv
  FROM ${TABLE_NAME}, (SELECT @ids := #{id}, @lv := 0) vars
  WHERE @ids IS NOT NULL
) t2 WHERE FIND_IN_SET(t1.${ID_NAME}, t2.c_ids) AND t1.${ID_NAME} <> #{id}
ORDER BY c_lv, ${PID_NAME}, ${ID_NAME};

③ MySQL 8.0+ CTE

# 查询上级节点 (SQL)
WITH RECURSIVE CTE AS (
	SELECT * FROM ${TABLE_NAME} WHERE ${ID_NAME} = #{id}
	UNION ALL 
	SELECT t.* FROM ${TABLE_NAME} t INNER JOIN CTE ON t.${ID_NAME} = CTE.${PID_NAME}
)
SELECT * FROM CTE ORDER BY ${ID_NAME};

# 查询下级节点 (SQL)
WITH RECURSIVE CTE AS (
	SELECT * FROM ${TABLE_NAME} WHERE ${ID_NAME} = #{id}
	UNION ALL 
	SELECT t.* FROM ${TABLE_NAME} t INNER JOIN CTE ON t.${PID_NAME} = CTE.${ID_NAME}
)
SELECT * FROM CTE ORDER BY ${ID_NAME};

左右值 设计方案

1. 原理 ：类似前序遍历，先访问父节点，再访问子节点，递增标记，每个节点访问两次，分左(lft)右(rgt)值；也可增加 level / layer 属性，储存节点的深度，优化设计；
2. 实现 ：此方案资料较多，随机列出一些供参考
   ① 左右值编码树形结构数据存储方案
   ② MySQL多层级结构-树搜索
   ③ 采用左右值编码实现无限分级树形结构