如何在MySQL存储过程中获取所有层级的下级推荐用户

在MySQL中处理层级推荐关系时，普通WHERE语句只能获取一级下级，要获取所有层级的下级，推荐使用递归公共表表达式（CTE）——这是MySQL 8.0及以上版本支持的特性，可高效遍历多级关系。

1. 示例表结构与数据 #

先创建测试表并插入示例数据：

CREATE TABLE IF NOT EXISTS users (
    name VARCHAR(50) PRIMARY KEY,
    referrer VARCHAR(50) NULL,
    FOREIGN KEY (referrer) REFERENCES users(name)
);

INSERT INTO users (name, referrer) VALUES
('Alice', NULL),
('Bob', 'Alice'),
('Carol', 'Alice'),
('Dave', 'Bob'),
('Eve', 'Bob'),
('Frank', 'Carol'),
('Grace', 'Eve'),
('Helen', NULL),
('David', 'Helen'),
('Lucas', 'David'),
('Olivia', 'Lucas'),
('Sophia', 'Lucas'),
('Benjamin', 'Bob');

2. 递归CTE查询语句 #

直接用递归CTE查询指定用户（如Alice）的所有层级下级：

WITH RECURSIVE referral_hierarchy AS (
    -- 锚点查询：获取一级直接下级
    SELECT name
    FROM users
    WHERE referrer = 'Alice'
    UNION ALL
    -- 递归查询：迭代获取下级的下级，直到无更多层级
    SELECT u.name
    FROM users u
    JOIN referral_hierarchy rh ON u.referrer = rh.name
)
SELECT name FROM referral_hierarchy;

执行后会返回预期结果：Bob、Carol、Dave、Eve、Frank、Grace、Benjamin，自动排除与Alice无关的用户。

3. 封装为存储过程 #

如果需要重复使用该逻辑，可封装成带参数的存储过程：

DELIMITER //

CREATE PROCEDURE GetAllDownlineUsers(IN p_referrer VARCHAR(50))
BEGIN
    WITH RECURSIVE referral_hierarchy AS (
        SELECT name
        FROM users
        WHERE referrer = p_referrer
        UNION ALL
        SELECT u.name
        FROM users u
        JOIN referral_hierarchy rh ON u.referrer = rh.name
    )
    SELECT name FROM referral_hierarchy;
END //

DELIMITER ;

调用方式：

CALL GetAllDownlineUsers('Alice');

关键说明 #

• 递归CTE原理：锚点成员返回初始的一级下级，递归成员通过关联锚点结果集，不断获取下一级用户，直到没有匹配记录时停止递归。
• 版本要求：必须使用MySQL 8.0或更高版本，低版本不支持递归CTE。
• 循环防护：如果数据存在循环推荐（如A推荐B，B推荐A），会导致无限递归，可通过添加递归深度限制避免，例如：

  WITH RECURSIVE referral_hierarchy AS (
      SELECT name, 1 AS level
      FROM users
      WHERE referrer = 'Alice'
      UNION ALL
      SELECT u.name, rh.level + 1
      FROM users u
      JOIN referral_hierarchy rh ON u.referrer = rh.name
      WHERE rh.level < 10 -- 限制最大递归深度为10
  )
  SELECT name FROM referral_hierarchy;
  

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