如何在MySQL中获取用户的所有继承角色递归配对列表？

问题需求 #

需要关联user、role_assignment和role_inheritance表，查询用户因被分配角色而拥有的所有角色，包括直接分配的角色及其递归继承的所有子角色（子角色的子角色也需包含）。

数据库表结构 #

-- auto-generated definition
CREATE TABLE user
(
    id              VARCHAR(255) NOT NULL
        PRIMARY KEY,
    name            VARCHAR(255) NULL,
    email           VARCHAR(255) NOT NULL,
    CONSTRAINT user_email_unique
        UNIQUE (email)
)
    CHARSET = utf8mb4;

-- auto-generated definition
CREATE TABLE role_inheritance
(
    role_name  VARCHAR(255) NOT NULL,
    child_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (role_name, child_name),
    CONSTRAINT role_inheritance_child_name_foreign
        FOREIGN KEY (child_name) REFERENCES role (name)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT role_inheritance_role_name_foreign
        FOREIGN KEY (role_name) REFERENCES role (name)
            ON UPDATE CASCADE ON DELETE CASCADE
)
    CHARSET = utf8mb4;

CREATE INDEX role_inheritance_child_name_index
    ON role_inheritance (child_name);

CREATE INDEX role_inheritance_role_name_index
    ON role_inheritance (role_name);

-- auto-generated definition
CREATE TABLE role_assignment
(
    user_id   VARCHAR(255) NOT NULL,
    role_name VARCHAR(255) NOT NULL,
    scope     VARCHAR(255) NULL,
    PRIMARY KEY (user_id, role_name),
    CONSTRAINT role_assignment_role_name_foreign
        FOREIGN KEY (role_name) REFERENCES role (name)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT role_assignment_user_id_foreign
        FOREIGN KEY (user_id) REFERENCES user (id)
            ON UPDATE CASCADE ON DELETE CASCADE
)
    CHARSET = utf8mb4;

CREATE INDEX role_assignment_role_name_index
    ON role_assignment (role_name);

CREATE INDEX role_assignment_user_id_index
    ON role_assignment (user_id);


-- auto-generated definition
CREATE TABLE role
(
    name VARCHAR(255) NOT NULL
        PRIMARY KEY
)
    CHARSET = utf8mb4;

测试数据 #

角色表 #

角色继承表 #

角色分配表 #

期望查询结果 #

错误尝试的SQL #

以下SQL在MySQL 9中会被弃用，且查询结果错误（Chris的记录错误包含admin和functionalities）：

SELECT roles.name AS role_name, @id := u.id AS user_id
FROM user u
         CROSS JOIN (WITH RECURSIVE
                         user_roles  AS (SELECT role_name
                                         FROM role_assignment where user_id = @id),
                         inheritance AS (SELECT child_name
                                         FROM role_inheritance
                                         WHERE role_name IN
                                               (SELECT role_name
                                                FROM user_roles)
                                         UNION ALL
                                         SELECT ri.child_name
                                         FROM inheritance i,
                                              role_inheritance ri
                                         WHERE ri.role_name = i.child_name)
                     SELECT *
                     FROM role r
                     WHERE r.name IN (SELECT child_name
                                      FROM inheritance)
                            OR r.name IN (SELECT role_name
                                          FROM user_roles)) AS roles
ORDER BY user_id

正确解决方案 #

使用递归CTE关联用户与角色分配，递归获取所有继承角色，同时包含直接分配的角色：

WITH RECURSIVE user_role_hierarchy AS (
    -- 初始步骤：获取用户直接分配的角色
    SELECT 
        ra.user_id,
        ra.role_name AS inherited_role
    FROM role_assignment ra
    UNION ALL
    -- 递归步骤：获取当前角色的所有子角色
    SELECT 
        urh.user_id,
        ri.child_name AS inherited_role
    FROM user_role_hierarchy urh
    JOIN role_inheritance ri ON urh.inherited_role = ri.role_name
)
-- 关联角色表确保存在的角色，去重并排序
SELECT 
    r.name AS role_name,
    urh.user_id
FROM user_role_hierarchy urh
JOIN role r ON urh.inherited_role = r.name
GROUP BY r.name, urh.user_id -- 防止重复（若存在循环继承）
ORDER BY urh.user_id, r.name;

思路说明 #

1. 初始CTE：从role_assignment获取用户直接分配的角色，作为递归的起点。
2. 递归CTE：通过role_inheritance关联当前层级的角色，获取其所有子角色，直到没有更多子角色为止。
3. 最终查询：关联role表确保角色存在，通过GROUP BY去重（避免循环继承导致的重复记录），最后按用户ID和角色名称排序。

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