MySQL递归存储过程获取推荐分支返回NULL问题求助

我刚接触MySQL存储过程与函数，还没完全搞懂运作机制。尝试写了递归存储过程fb2_get_referrals和配套函数getPath，用来获取推荐分支展示，但调用后结果返回NULL，求排查问题。

原存储过程代码 #

DELIMITER $$
    CREATE PROCEDURE fb2_get_referrals (IN refer_id INT UNSIGNED, IN parent_level INT UNSIGNED, OUT return_path MEDIUMTEXT)
         BEGIN
            DECLARE parent_id INT UNSIGNED;
            DECLARE path_result MEDIUMTEXT;
            DECLARE referral_offset INT UNSIGNED;
            DECLARE count_user_referrals INT UNSIGNED;
            
            SET referral_offset = 0;
            SET parent_level = parent_level + 1;
            SET count_user_referrals = (SELECT COUNT(fb2_users.id) FROM fb2_users WHERE fb2_users.referral_id = refer_id);
            
            IF(count_user_referrals > 0) THEN
                label1: WHILE referral_offset < count_user_referrals DO
                    SELECT 
                        CONCAT( 
                            '{',
                            '&quot;id&quot;:',fb2_users.id,',',
                            '&quot;level&quot;:',parent_level,
                            '}'
                        ) as path,
                        fb2_users.id as pi,
                        parent_level as pl
                    INTO return_path, parent_id, parent_level
                    FROM fb2_users 
                    WHERE fb2_users.referral_id = refer_id 
                    LIMIT 1 OFFSET referral_offset;
                    
                    CALL fb2_get_referrals(parent_id, parent_level, path_result);
                    SELECT CONCAT(path_result, return_path) INTO return_path;
                   
                    SET referral_offset = referral_offset + 1;
                END WHILE label1;
            END IF;
         END$$

配套函数代码 #

DELIMITER $$
CREATE FUNCTION getPath(user_id INT) RETURNS MEDIUMTEXT DETERMINISTIC
BEGIN
    DECLARE res MEDIUMTEXT;
    CALL fb2_get_referrals(user_id,0, res);
    RETURN res;
END$$

数据表结构 #

核心问题排查 #

1. 字段名不匹配：数据表中存储推荐ID的字段是r_id，但存储过程查询时用的是fb2_users.referral_id，导致无法匹配关联数据，count_user_referrals始终为0，直接跳过循环，return_path从未被赋值，最终返回NULL。
2. 递归终止时变量未初始化：当节点没有下级推荐时，path_result未被赋值，CONCAT(path_result, return_path)会因path_result为NULL导致整个结果变为NULL。
3. 层级变量被意外覆盖：SELECT INTO语句中重新赋值parent_level，会导致后续循环和递归的层级计算错误。
4. 返回变量无初始值：return_path在存储过程开头未初始化为空字符串，无下级节点时直接返回NULL。

修复建议 #

• 修正字段名：把存储过程中的referral_id改为r_id，匹配数据表字段。
• 初始化返回变量：在存储过程开头添加SET return_path = '';。
• 处理递归空结果：递归调用后判断path_result是否为NULL，避免拼接NULL值：SET return_path = IF(path_result IS NULL, return_path, CONCAT(path_result, ',', return_path));（逗号用于分隔多个节点）。
• 保留层级变量：去掉SELECT INTO中对parent_level的赋值，递归时直接传递当前计算好的parent_level。

内容的提问来源于stack exchange，提问作者Даниял