如何在 MySQL 4 中高效过滤掉无有效父级权限的层级权限数据-php教程-PHP中文网

如何在 MySQL 4 中高效过滤掉无有效父级权限的层级权限数据

本文介绍在老旧 mysql 4 环境下，通过 left join 和 exists 子查询组合，精准剔除缺失直接或间接父权限的子权限记录，兼顾性能与兼容性，适用于深层不超过 4 级的权限树过滤场景。

本文介绍在老旧 mysql 4 环境下，通过 left join 和 exists 子查询组合，精准剔除缺失直接或间接父权限的子权限记录，兼顾性能与兼容性，适用于深层不超过 4 级的权限树过滤场景。

在权限系统中，常需确保子权限仅在父权限存在时才生效（例如“写文件”必须以“读文件”为前提）。但当从多表聚合权限后，原始结果可能包含孤立的子权限（如 can_moonwalk 的父权限 4 未出现在当前结果集中），这类数据必须被安全剔除——尤其在 MySQL 4 这类不支持 CTE、递归查询或窗口函数的老版本中，需依赖经典 JOIN 与子查询策略实现高效过滤。

核心思路是：仅保留两类权限记录

根权限（parent_id = 0）；
非根权限，但其所有向上路径中的祖先权限均存在于当前结果集内（即至少直接父权限必须存在）。

由于层级深度有限（≤4），我们无需递归，只需验证直接父权限是否存在于本次聚合结果中。注意：题目要求的是“在当前 JOIN 产生的临时权限集合中，父权限必须存在”，而非全局 permissions 表中存在——因此不能简单用 WHERE p.parent_id = 0 OR p.parent_id IN (...)，而应让父权限也参与同一查询上下文。

✅ 正确做法：使用 LEFT JOIN 关联自身表，检查父权限是否被包含在本次聚合结果中：

kashen-mysql DDLtoExcel

解析 MySQL 8.0 的 DDL 脚本，并将表结构导出为 Excel 格式。适用于用户需将 MySQL 的 `CREATE TABLE` 语句转换为结构化的 Excel 文件场景，导出内容包含字段名、数据类型、长度及精度等信息。支持多张表的批量处理，每张表对应 Excel 中的一个独立工作表。适用于数据库文档编写、数据字典生成及模式分析等任务。

下载

UPDATE aggregat_table at
  JOIN toto ON ...
  JOIN titi ON ...
  JOIN tutu ON ...
  JOIN permissions p 
    ON p.id = toto.id 
    OR p.id = titi.id 
    OR p.id = tutu.id
  -- 关键：LEFT JOIN 自身表，匹配 p 的 parent_id 是否也在本次结果中
  LEFT JOIN permissions p_parent 
    ON p_parent.id = p.parent_id
    AND (p_parent.id = toto.id 
         OR p_parent.id = titi.id 
         OR p_parent.id = tutu.id)
WHERE 
  p.parent_id = 0  -- 根权限保留
  OR p_parent.id IS NOT NULL; -- 非根权限，且其父权限也在本次结果中

⚠️ 注意事项：

避免 OR 条件导致索引失效：p.id = toto.id OR p.id = titi.id OR p.id = tutu.id 在 MySQL 4 中无法高效使用索引。推荐改写为 UNION ALL 子查询预聚合 ID 列表（若业务允许），或确保 permissions.id 有主键（PRIMARY KEY(id)），并为 parent_id 添加索引（INDEX(parent_id)）以加速 p_parent 关联。
EXISTS 替代方案（更清晰，MySQL 4 支持）：若 LEFT JOIN 写法复杂，可用以下等价逻辑（推荐）：

UPDATE aggregat_table at
  JOIN toto ON ...
  JOIN titi ON ...
  JOIN tutu ON ...
  JOIN permissions p 
    ON p.id = toto.id 
    OR p.id = titi.id 
    OR p.id = tutu.id
WHERE 
  p.parent_id = 0
  OR EXISTS (
    SELECT 1 FROM permissions p2
    WHERE p2.id = p.parent_id
      AND (
        p2.id = toto.id 
        OR p2.id = titi.id 
        OR p2.id = tutu.id
      )
  );

? 总结：

根权限（parent_id = 0）天然合法，始终保留；
子权限合法性取决于其 parent_id 是否同时满足两个条件：① 在 permissions 表中存在；② 且该父权限本身也由 toto/titi/tutu 提供（即属于本次聚合结果集）；
通过 EXISTS + 多重 OR 条件可清晰表达这一业务约束，虽牺牲部分索引效率，但在低深度、高基数可控场景下仍具实用性；
务必为 permissions(id) 设置主键，并为 parent_id 建立索引，这是 MySQL 4 下提升关联性能的关键基础。