RBAC权限系统中查询菜单树的方法

RBAC权限系统中查询菜单树的方法

在RBAC权限系统中，菜单树的查询是基于permissions表中的层级关系（parent_id字段）实现的。以下提供几种常用的查询菜单树的方法，包括纯SQL查询和结合代码的方式。

一、纯SQL方法查询菜单树

1. 递归CTE查询全量菜单树（MySQL 8.0+）

WITH RECURSIVE menu_tree AS (
  -- 基础查询：根节点
  SELECT 
    id, 
    permission_name, 
    permission_code, 
    type, 
    parent_id, 
    path, 
    icon, 
    component, 
    sort_order,
    1 AS level,  -- 层级标识
    CAST(id AS CHAR) AS path_ids  -- 路径ID，用于排序
  FROM permissions 
  WHERE parent_id = 0 AND type = 1 AND status = 1
  
  UNION ALL
  
  -- 递归查询：子节点
  SELECT 
    p.id, 
    p.permission_name, 
    p.permission_code, 
    p.type, 
    p.parent_id, 
    p.path, 
    p.icon, 
    p.component, 
    p.sort_order,
    mt.level + 1 AS level,
    CONCAT(mt.path_ids, ',', p.id) AS path_ids
  FROM permissions p
  JOIN menu_tree mt ON p.parent_id = mt.id
  WHERE p.type = 1 AND p.status = 1
)
SELECT id, permission_name, permission_code, type, parent_id, path, icon, component, sort_order, level
FROM menu_tree
ORDER BY path_ids;  -- 按路径排序，确保树结构正确

2. 查询特定用户的权限菜单树

WITH RECURSIVE user_menu_tree AS (
  -- 先查询用户拥有的所有权限ID
  SELECT DISTINCT p.id 
  FROM users u
  JOIN user_roles ur ON u.id = ur.user_id
  JOIN roles r ON ur.role_id = r.id
  JOIN role_permissions rp ON r.id = rp.role_id
  JOIN permissions p ON rp.permission_id = p.id
  WHERE u.username = 'admin' AND r.status = 1 AND p.status = 1
),
-- 再递归构建菜单树
menu_hierarchy AS (
  SELECT 
    p.id, 
    p.permission_name, 
    p.permission_code, 
    p.type, 
    p.parent_id, 
    p.path, 
    p.icon, 
    p.component, 
    p.sort_order,
    1 AS level,
    CAST(p.id AS CHAR) AS path_ids
  FROM permissions p
  JOIN user_menu_tree umt ON p.id = umt.id
  WHERE p.parent_id = 0 AND p.type = 1
  
  UNION ALL
  
  SELECT 
    p.id, 
    p.permission_name, 
    p.permission_code, 
    p.type, 
    p.parent_id, 
    p.path, 
    p.icon, 
    p.component, 
    p.sort_order,
    mh.level + 1 AS level,
    CONCAT(mh.path_ids, ',', p.id) AS path_ids
  FROM permissions p
  JOIN menu_hierarchy mh ON p.parent_id = mh.id
  JOIN user_menu_tree umt ON p.id = umt.id
  WHERE p.type = 1
)
SELECT id, permission_name, permission_code, type, parent_id, path, icon, component, sort_order, level
FROM menu_hierarchy
ORDER BY path_ids;

二、代码实现菜单树构建

1. Java实现（以Spring Boot为例）

实体类定义

public class Permission {
    private Long id;
    private String permissionName;
    private String permissionCode;
    private Integer type;
    private Long parentId;
    private String path;
    private String icon;
    private String component;
    private Integer sortOrder;
    private List<Permission> children = new ArrayList<>();
    
    // getters and setters
}

Service层实现

@Service
public class PermissionService {
    
    @Autowired
    private PermissionMapper permissionMapper;
    
    /**
     * 获取全量菜单树
     */
    public List<Permission> getMenuTree() {
        // 查询所有启用的菜单权限
        List<Permission> allMenus = permissionMapper.selectAllEnabledMenus();
        return buildMenuTree(allMenus, 0L);
    }
    
    /**
     * 获取指定用户的菜单树
     */
    public List<Permission> getUserMenuTree(Long userId) {
        // 查询用户拥有的所有菜单权限
        List<Permission> userMenus = permissionMapper.selectUserEnabledMenus(userId);
        return buildMenuTree(userMenus, 0L);
    }
    
    /**
     * 递归构建菜单树
     */
    private List<Permission> buildMenuTree(List<Permission> menus, Long parentId) {
        List<Permission> tree = new ArrayList<>();
        
        for (Permission menu : menus) {
            if (menu.getParentId().equals(parentId)) {
                // 递归查找子菜单
                menu.setChildren(buildMenuTree(menus, menu.getId()));
                tree.add(menu);
            }
        }
        
        // 按排序号排序
        tree.sort(Comparator.comparing(Permission::getSortOrder));
        return tree;
    }
}

Mapper接口（MyBatis示例）

@Mapper
public interface PermissionMapper {
    
    // 查询所有启用的菜单权限
    @Select("SELECT * FROM permissions WHERE type = 1 AND status = 1 ORDER BY sort_order ASC")
    List<Permission> selectAllEnabledMenus();
    
    // 查询用户拥有的启用菜单权限
    @Select("""
        SELECT DISTINCT p.* 
        FROM users u
        JOIN user_roles ur ON u.id = ur.user_id
        JOIN roles r ON ur.role_id = r.id
        JOIN role_permissions rp ON r.id = rp.role_id
        JOIN permissions p ON rp.permission_id = p.id
        WHERE u.id = #{userId} AND r.status = 1 AND p.status = 1 AND p.type = 1
        ORDER BY p.sort_order ASC
    """)
    List<Permission> selectUserEnabledMenus(@Param("userId") Long userId);
}

2. MySQL 5.x兼容性方案

对于不支持CTE的MySQL 5.x版本，可以使用以下方法：

-- 查询所有菜单，在应用层构建树结构
SELECT 
  id, 
  permission_name, 
  permission_code, 
  type, 
  parent_id, 
  path, 
  icon, 
  component, 
  sort_order
FROM permissions 
WHERE type = 1 AND status = 1
ORDER BY parent_id, sort_order;

三、查询优化建议

1. 使用缓存：菜单树数据相对稳定，可以缓存查询结果

   @Cacheable(value = "menuTree", key = "#userId")
   public List<Permission> getUserMenuTree(Long userId) {
       // 实现代码
   }
   

2. 延迟加载：对于大型系统，可以考虑只加载一级菜单，点击时再加载子菜单

3. 索引优化：确保以下字段有适当的索引

   -- 确保已创建的索引
   ALTER TABLE permissions ADD INDEX idx_parent_id (parent_id);
   ALTER TABLE permissions ADD INDEX idx_type_status (type, status);
   ALTER TABLE permissions ADD INDEX idx_sort_order (sort_order);
   

4. 权限过滤优化：在大量用户的系统中，可以考虑预计算并缓存用户权限

四、前端展示菜单树的建议

1. 前端通常需要扁平化的数组，然后通过parentId构建树形结构
2. 可使用如lodash的_.groupBy和递归函数处理菜单数据
3. 对于复杂权限控制，前端需要结合权限码进行按钮级别的控制

通过上述方法，可以高效地实现RBAC权限系统中的菜单树查询和构建。选择哪种方法取决于具体的技术栈、数据规模和性能要求。