如何改写含WITH RECURSIVE的函数以适配不支持该语法的MySQL 5.7？

当然可以！MySQL 5.7不支持WITH RECURSIVE递归CTE，但我们可以用循环结合字符串存储节点ID集合的方式，模拟递归遍历的逻辑，完美替代原函数的功能。以下是改写后的代码：

DELIMITER //
CREATE FUNCTION no_of_descendants(f_id INT) RETURNS INTEGER
BEGIN
    DECLARE numberOf INT;
    DECLARE current_ids TEXT;
    DECLARE child_ids TEXT;
    DECLARE child_count INT;

    -- 先检查传入的ID是否存在，不存在直接返回0
    SELECT COUNT(*) INTO numberOf FROM app_users WHERE id = f_id;
    IF numberOf = 0 THEN
        RETURN 0;
    END IF;

    -- 初始化当前待处理的父节点ID集合，总数初始为1（包含当前节点本身）
    SET current_ids = f_id;

    -- 循环遍历所有后代节点
    WHILE current_ids IS NOT NULL AND current_ids != '' DO
        -- 获取当前所有父节点的子节点ID，拼接成逗号分隔的字符串
        SELECT GROUP_CONCAT(DISTINCT id) INTO child_ids
        FROM app_users
        WHERE FIND_IN_SET(parent_id, current_ids) > 0;

        -- 统计当前批次的子节点数量
        SELECT COUNT(DISTINCT id) INTO child_count
        FROM app_users
        WHERE FIND_IN_SET(parent_id, current_ids) > 0;

        -- 如果找到子节点，更新总数和待处理的ID集合
        IF child_count > 0 THEN
            SET numberOf = numberOf + child_count;
            SET current_ids = child_ids;
        ELSE
            -- 没有子节点时退出循环
            SET current_ids = NULL;
        END IF;
    END WHILE;

    RETURN numberOf;
END //
DELIMITER ;

逻辑说明 #

1. 初始检查：先判断传入的f_id是否存在于app_users表中，不存在直接返回0，和原函数行为保持一致。
2. 循环遍历：
   • 用current_ids存储当前需要处理的父节点ID（逗号分隔的字符串），初始值为传入的f_id。
   • 每次循环查找这些父节点的所有子节点，用GROUP_CONCAT把子节点ID拼接成新的字符串，同时统计子节点数量。
   • 将子节点数量累加到总数中，然后把新的子节点ID集合作为下一轮循环的处理对象，直到没有子节点为止。
3. 返回结果：最终返回包含当前节点在内的所有后代节点总数。

注意事项 #

如果你的用户层级非常深、后代节点数量极多，可能会遇到GROUP_CONCAT的长度限制（默认最大长度为1024）。可以通过以下命令临时调整会话级别的限制：

SET SESSION group_concat_max_len = 1000000;

如果需要永久生效，可以在MySQL配置文件中添加group_concat_max_len = 1000000并重启服务。

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