MySQL单表父子层级关系设计及关联分组查询方案

一、是否需要拆分为3张表？ #

不需要拆分，单表即可满足所有关联需求。
你描述的是非常典型的固定两层层级关联场景，拆成3张表反而会带来ID全局唯一约束、跨表查询、多表维护的额外成本，没有实际收益。
直接保留单张positions表，仅需新增一个parent_id字段即可实现所有关联规则：

• 父节点：parent_id值为NULL或0，存在其他记录的parent_id指向该记录的主键position_id
• 子节点：parent_id值为其关联的唯一父节点的position_id
• 独立节点：parent_id值为NULL或0，无其他记录的parent_id指向该记录的position_id
  这种设计天然满足你的关联约束：父节点可绑定1到N个子节点，子节点仅能绑定1个父节点，独立节点无关联。只需要给parent_id字段加普通索引，就能支撑高效的关联查询。

二、组内任意节点命中即返回全组的查询实现 #

这个需求的核心逻辑是先给所有节点打组标签：每个父节点和其下所有子节点属于同一个组，独立节点自身为一个组，组ID统一取组内父节点的position_id（独立节点的组ID为自身position_id）。实现时先找到所有命中过滤规则的节点所属的组ID，再把这些组下的所有节点全部返回即可。

基础实现（兼容所有MySQL版本） #

以你提到的「position_id为质数则命中规则」为例，SQL写法如下：

SELECT p.*
FROM positions p
WHERE 
  -- 计算当前节点所属的组ID
  IF(p.parent_id IS NULL OR p.parent_id = 0, p.position_id, p.parent_id)
  IN (
    -- 查出所有命中过滤规则的节点对应的组ID
    SELECT DISTINCT IF(parent_id IS NULL OR parent_id = 0, position_id, parent_id)
    FROM positions
    WHERE -- 此处替换为你的自定义过滤规则
    is_prime(position_id) = 1
  );

如果使用MySQL 8.0及以上版本，也可以用CTE语法把逻辑拆得更清晰，执行效率和上面的写法完全一致：

WITH matched_groups AS (
  SELECT DISTINCT IF(parent_id IS NULL OR parent_id = 0, position_id, parent_id) AS group_id
  FROM positions
  WHERE is_prime(position_id) = 1 -- 自定义过滤规则
)
SELECT p.*
FROM positions p
JOIN matched_groups g
ON IF(p.parent_id IS NULL OR p.parent_id = 0, p.position_id, p.parent_id) = g.group_id;

性能优化方案（数据量大时推荐） #

如果表数据量达到十万级以上，可以新增一个存储生成列专门存组ID，同时给该列加索引，避免查询时实时计算IF判断，查询性能可以和普通单表过滤持平：

-- 新增存储生成列存组ID
ALTER TABLE positions 
ADD COLUMN group_id BIGINT GENERATED ALWAYS AS (
  IF(parent_id IS NULL OR parent_id = 0, position_id, parent_id)
) STORED;
-- 给组ID加索引
ALTER TABLE positions ADD INDEX idx_group_id(group_id);

加完字段后查询可以直接简化为：

SELECT p.*
FROM positions p
WHERE p.group_id IN (
  SELECT DISTINCT group_id
  FROM positions
  WHERE is_prime(position_id) = 1 -- 自定义过滤规则
);

补充说明：如果后续业务扩展为多层级关联（比如子节点下再挂孙节点），只需要把计算组ID的逻辑替换为递归CTE查找最顶层祖先节点的逻辑即可，整体实现思路不变。

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