层级数据Schema优化与树形结构构建技术咨询

你的核心问题在于：现有Schema中，节点的子节点关联是全局绑定到节点ID的，而非绑定到「父节点-子节点」的具体关联关系。也就是说，同一个节点ID不管挂在哪个父节点下，它的子节点都是从Mapping表中查parent_id等于该节点ID的所有记录，自然会出现不同父节点下的同名/同ID节点子节点被合并的情况。

一、Schema修改方案（彻底解决问题） #

要实现「同一节点在不同父节点下拥有不同子节点」，必须引入节点实例的概念——让每个「父节点下的子节点」成为一个独立的条目，每个条目可以拥有自己的子节点链。推荐的Schema设计如下：

1. 拆分节点元数据与树形实例 #

• categories表：仅存储节点的通用元数据（名称、描述等），不涉及层级关系

  CREATE TABLE categories (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(100) NOT NULL UNIQUE, -- 确保节点名称全局唯一（可选）
      description TEXT -- 其他通用属性
  );
  

• tree_nodes表：存储树形结构中的每个节点实例，关联到categories的元数据，同时记录父实例ID

  CREATE TABLE tree_nodes (
      id INT PRIMARY KEY AUTO_INCREMENT,
      category_id INT NOT NULL,
      parent_node_id INT NULL, -- 根节点为NULL
      FOREIGN KEY (category_id) REFERENCES categories(id),
      FOREIGN KEY (parent_node_id) REFERENCES tree_nodes(id)
  );
  

设计优势 #

• 同一个category（比如Foundations）可以对应多个tree_nodes实例，分别挂在不同的父节点下
• 每个tree_nodes实例拥有独立的子节点链，完全隔离不同父节点下的子树
• 避免重复存储节点元数据，维护更方便

示例数据 #

假设你需要：

• 父节点A下的Foundations有子节点Math
• 父节点B下的Foundations有子节点Physics

数据存储如下：

1. categories表：

   id	name	description
   1	Foundations	...
   2	A	...
   3	B	...
   4	Math	...
   5	Physics	...

2. tree_nodes表：

   id	category_id	parent_node_id
   100	2	NULL
   101	3	NULL
   102	1	100
   103	1	101
   104	4	102
   105	5	103

二、用递归CTE查询目标树形结构 #

基于修改后的Schema，使用递归CTE可以轻松生成任意层级的树形结构，且不会出现子节点合并的问题。

1. 查询完整树形（带层级与路径） #

WITH RECURSIVE full_tree AS (
    -- 递归起点：所有根节点（parent_node_id为NULL）
    SELECT
        tn.id AS node_instance_id,
        c.id AS category_id,
        c.name AS node_name,
        tn.parent_node_id,
        1 AS level,
        ARRAY[c.name] AS path -- 用数组存储路径，方便排序与展示
    FROM tree_nodes tn
    JOIN categories c ON tn.category_id = c.id
    WHERE tn.parent_node_id IS NULL

    UNION ALL

    -- 递归步骤：遍历子节点实例
    SELECT
        child_tn.id AS node_instance_id,
        child_c.id AS category_id,
        child_c.name AS node_name,
        child_tn.parent_node_id,
        parent.level + 1 AS level,
        parent.path || child_c.name AS path
    FROM tree_nodes child_tn
    JOIN categories child_c ON child_tn.category_id = child_c.id
    JOIN full_tree parent ON child_tn.parent_node_id = parent.node_instance_id
)
SELECT * FROM full_tree ORDER BY path;

2. 查询指定父节点下的子树 #

比如要查询根节点A（tree_nodes.id=100）下的完整子树：

WITH RECURSIVE target_subtree AS (
    -- 递归起点：目标父节点的直接子节点
    SELECT
        tn.id AS node_instance_id,
        c.id AS category_id,
        c.name AS node_name,
        tn.parent_node_id,
        1 AS level,
        ARRAY[c.name] AS path
    FROM tree_nodes tn
    JOIN categories c ON tn.category_id = c.id
    WHERE tn.parent_node_id = 100

    UNION ALL

    SELECT
        child_tn.id AS node_instance_id,
        child_c.id AS category_id,
        child_c.name AS node_name,
        child_tn.parent_node_id,
        parent.level + 1 AS level,
        parent.path || child_c.name AS path
    FROM tree_nodes child_tn
    JOIN categories child_c ON child_tn.category_id = child_c.id
    JOIN target_subtree parent ON child_tn.parent_node_id = parent.node_instance_id
)
SELECT * FROM target_subtree ORDER BY path;

三、不修改Schema的临时方案（仅用于查询，无法真正实现需求） #

如果暂时无法修改Schema，只能通过记录完整路径来区分同一节点的不同出现，但无法实现同一节点在不同父节点下有不同子节点（因为子节点仍绑定到全局节点ID）。示例查询如下：

假设原有Schema为：

• categories(id, name)
• category_mappings(parent_id, child_id)

查询指定父节点（id=2）下的子树：

WITH RECURSIVE limited_tree AS (
    SELECT
        cm.child_id AS node_id,
        c.name AS node_name,
        cm.parent_id,
        1 AS level,
        ARRAY[c.name] AS path
    FROM category_mappings cm
    JOIN categories c ON cm.child_id = c.id
    WHERE cm.parent_id = 2

    UNION ALL

    SELECT
        cm.child_id AS node_id,
        c.name AS node_name,
        cm.parent_id,
        parent.level + 1 AS level,
        parent.path || c.name AS path
    FROM category_mappings cm
    JOIN categories c ON cm.child_id = c.id
    JOIN limited_tree parent ON cm.parent_id = parent.node_id
)
SELECT * FROM limited_tree ORDER BY path;

注意：这个方案仅能展示不同路径下的节点，但如果同一个节点ID在不同路径下出现，它的子节点还是全局相同的，无法满足你的核心需求。

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