MySQL中高效获取用户分级授权结构的方法

在MySQL中获取用户分级授权结构是一个常见的需求，特别是在构建多层级权限管理系统时。以下是几种高效实现的方法：

1. 使用递归公用表表达式(CTE)

MySQL 8.0+支持递归CTE，非常适合处理层级数据：

WITH RECURSIVE user_hierarchy AS (
    -- 基础查询：获取顶级用户
    SELECT id, username, parent_id, 1 AS level
    FROM users
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归查询：获取子用户
    SELECT u.id, u.username, u.parent_id, uh.level + 1
    FROM users u
    JOIN user_hierarchy uh ON u.parent_id = uh.id
)
SELECT * FROM user_hierarchy
ORDER BY level, id;

2. 使用存储过程

对于MySQL 5.7及以下版本，可以使用存储过程：

DELIMITER //
CREATE PROCEDURE GetUserHierarchy(IN root_id INT)
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_hierarchy (
        id INT,
        username VARCHAR(255),
        parent_id INT,
        level INT
    );

    INSERT INTO temp_hierarchy
    SELECT id, username, parent_id, 0
    FROM users
    WHERE id = root_id;

    SET @level = 0;

    REPEAT
        SET @level = @level + 1;

        INSERT INTO temp_hierarchy
        SELECT u.id, u.username, u.parent_id, @level
        FROM users u
        JOIN temp_hierarchy th ON u.parent_id = th.id
        WHERE th.level = @level - 1;

        SET @rows = ROW_COUNT();
    UNTIL @rows = 0 END REPEAT;

    SELECT * FROM temp_hierarchy ORDER BY level, id;
    DROP TEMPORARY TABLE temp_hierarchy;
END //
DELIMITER ;

3. 使用预计算路径方法

对于大型层级结构，可以预先计算并存储路径：

-- 添加path列存储路径
ALTER TABLE users ADD COLUMN path VARCHAR(1000);

-- 更新路径（需要定期维护）
UPDATE users u
JOIN users p ON u.parent_id = p.id
SET u.path = CONCAT(p.path, ',', u.id)
WHERE u.parent_id IS NOT NULL;

-- 查询某个用户的所有下级
SELECT * FROM users
WHERE path LIKE '1,%'  -- 假设1是根用户ID
ORDER BY path;

4. 使用嵌套集模型

另一种高效方法是使用嵌套集模型：

-- 表结构
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255),
    lft INT NOT NULL,
    rgt INT NOT NULL
);

-- 查询某个节点及其所有子节点
SELECT child.*
FROM users AS parent, users AS child
WHERE child.lft BETWEEN parent.lft AND parent.rgt
AND parent.id = :user_id
ORDER BY child.lft;

性能优化建议

1. 索引优化：确保parent_id和path列上有适当的索引
2. 缓存结果：对于不经常变化的层级结构，考虑缓存查询结果
3. 批量处理：避免在循环中执行单条SQL语句
4. 限制深度：对于特别深的层级，考虑设置最大深度限制

选择哪种方法取决于您的MySQL版本、数据量大小和层级深度。对于现代MySQL版本(8.0+)，递归CTE通常是首选方案。