MariaDB中使用递归CTE查询任意嵌套层级角色关联用户方案

问题根源 #

原有写法的兼容性问题来自作用域嵌套过深：旧版本MariaDB和MySQL 8.0早期版本，仅支持递归CTE读取直接父层的变量，将CTE放在两层EXISTS子查询内部时，CTE无法读取到第二层外层的roles.id字段，因此出现报错或者结果异常。

兼容方案 #

将递归逻辑前置，先一次性查询出所有属于目标角色继承链的角色ID，再关联用户关联表和用户表即可，完全避开多层作用域穿透的问题，同时性能更好，不需要每条关联记录都触发一次递归查询。

WITH RECURSIVE role_hierarchy AS (
    -- 锚点：定位目标角色
    SELECT id, name, parent_id 
    FROM roles 
    WHERE name = 'superuser' -- 此处修改为你要查询的目标角色名
    UNION ALL
    -- 递归向下遍历所有子角色（子角色默认继承父角色权限）
    SELECT r.id, r.name, r.parent_id
    FROM roles r
    INNER JOIN role_hierarchy rh ON r.parent_id = rh.id
)
-- 关联查询持有目标继承链内任意角色的用户
SELECT DISTINCT u.*
FROM users u
INNER JOIN user_roles ur ON u.id = ur.user_id
INNER JOIN role_hierarchy rh ON ur.role_id = rh.id;

方案说明 #

• 兼容性：支持MariaDB 10.2+所有支持递归CTE的版本，同时兼容MySQL 8.0全版本，不会出现作用域相关报错
• 扩展性：支持任意深度的角色嵌套，不需要硬编码关联层级
• 验证结果：使用提供的测试数据运行，会返回符合预期的Alice、Bob两条用户记录，Charlie不在结果集内

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