MySQL实现含多对多关联的复杂层级树数据查询方案咨询

数据库结构调整 #

不用拆成两个关联表，单关联表加字段的方案维护成本最低，还能直接通过数据库约束保证业务规则：

• 首先新建development_level基础表存储3种D类型数据，核心字段包含主键id、类型名称level_name，可按需添加排序权重、描述等自定义字段。
• 直接修改原有program_outcome_unit_lookup关联表，新增level_id字段，外键关联development_level表的主键id。
• 给关联表加唯一约束UNIQUE KEY outcome_unit_unique (outcome_id, unit_id)，从数据库层面强制实现「单个U在单个O分支下仅能关联1个D」的规则，避免脏数据。

如果你不想修改原表，也可以直接删掉原表，新建三字段关联表存储outcome_id、level_id、unit_id三个关联关系，效果完全一致。

适配现有PHP逻辑的MySQL查询 #

你现有PHP代码是靠global_id识别节点、parent_global_id挂载父子关系，不需要修改PHP代码，只需要写查询把四个层级的节点统一查成扁平结果集即可，用UNION ALL拼接各层节点是最稳妥、性能最好的方式，不需要写复杂递归：

-- 查询顶层根节点（对应原有结构的program层级）
SELECT 
  CONCAT('program:', p.id) AS global_id,
  p.program_name AS name,
  NULL AS parent_global_id
FROM program p

UNION ALL

-- 查询O层（program_outcome）节点，父级为对应根节点
SELECT 
  CONCAT('program:', po.program_id, ',outcome:', po.id) AS global_id,
  po.outcome_name AS name,
  CONCAT('program:', po.program_id) AS parent_global_id
FROM program_outcome po

UNION ALL

-- 查询D层（development_level）节点，父级为对应分支下的O节点
-- 加DISTINCT去重，避免同一O下相同D节点重复生成
SELECT DISTINCT
  CONCAT('program:', po.program_id, ',outcome:', po.id, ',level:', dl.id) AS global_id,
  dl.level_name AS name,
  CONCAT('program:', po.program_id, ',outcome:', po.id) AS parent_global_id
FROM program_outcome_unit_lookup polu
JOIN program_outcome po ON po.id = polu.outcome_id
JOIN development_level dl ON dl.id = polu.level_id

UNION ALL

-- 查询U层（unit）节点，父级为对应O分支下关联的D节点
SELECT 
  CONCAT('program:', po.program_id, ',outcome:', po.id, ',level:', dl.id, ',unit:', u.id) AS global_id,
  u.unit_name AS name,
  CONCAT('program:', po.program_id, ',outcome:', po.id, ',level:', dl.id) AS parent_global_id
FROM program_outcome_unit_lookup polu
JOIN program_outcome po ON po.id = polu.outcome_id
JOIN development_level dl ON dl.id = polu.level_id
JOIN unit u ON u.id = polu.unit_id

逻辑说明 #

• 查询输出的字段完全适配你现有PHP处理逻辑，不需要调整任何PHP代码，执行后自动生成根→O→D→U的四级树结构。
• 同一O分支下多个U绑定同一个D时，D节点只会生成一次，不会出现重复中间层。
• 同一U在不同O分支下可以绑定相同或不同类型的D，会分别挂载到对应分支的D节点下，完全匹配多分支结构要求。

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