一、版本兼容性：确认MySQL版本是否支持递归CTE

WITH RECURSIVE 是MySQL 8.0.0版本引入的特性，若使用5.7或更早版本，执行时会直接报错”You have an error in your SQL syntax”。开发者需通过SELECT VERSION();确认数据库版本，低于8.0的版本需升级至最新稳定版。

典型错误场景：
某电商系统在MySQL 5.7上尝试递归查询商品分类树，始终报语法错误。升级至MySQL 8.0.32后，相同SQL语句可正常执行。

升级建议：

1. 测试环境验证：先在非生产环境部署MySQL 8.0
2. 数据迁移：使用mysqldump导出数据，注意字符集和排序规则
3. 配置调整：检查max_allowed_packet等参数是否适配新版本

二、语法规范：递归CTE的正确书写方式

递归CTE必须包含基础查询和递归部分，通过UNION ALL连接。常见错误包括：

1. 缺少终止条件：

   -- 错误示例：无限递归导致栈溢出
   WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte  -- 缺少n >= 100的终止条件
   ) SELECT * FROM cte;

2. 列名不匹配：
   基础查询和递归部分的列数、类型必须一致。以下写法会报”The used SELECT statements have a different number of columns”：

   WITH RECURSIVE cte(id, name) AS (
    SELECT 1, 'A'  -- 2列
    UNION ALL
    SELECT id  -- 1列
    FROM cte WHERE id < 3
   ) SELECT * FROM cte;

3. 引用错误：
   递归部分必须引用CTE名称而非表名。以下写法无效：

   -- 错误示例：递归部分错误引用原表
   WITH RECURSIVE cte AS (
    SELECT * FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.* FROM employees e  -- 应为FROM cte
    JOIN employees m ON e.manager_id = m.id
   ) SELECT * FROM cte;

正确模板：

WITH RECURSIVE cte_name(col1, col2) AS (
    -- 基础查询（非递归部分）
    SELECT init_col1, init_col2 FROM table WHERE condition
    UNION ALL
    -- 递归部分
    SELECT r.col1, r.col2 
    FROM cte_name p  -- 引用CTE自身
    JOIN table r ON p.col1 = r.parent_col
    WHERE r.depth < 10  -- 必须包含终止条件
)
SELECT * FROM cte_name;

三、权限配置：执行递归查询的权限要求

执行递归CTE需要用户具备：

1. 对基础表的SELECT权限
2. 对目标表的INSERT权限（当使用INSERT ... SELECT时）
3. CREATE ROUTINE权限（存储过程中使用）

权限检查步骤：

1. 确认当前用户：SELECT USER();
2. 查看表权限：SHOW GRANTS FOR CURRENT_USER;
3. 缺少权限时，由管理员执行：

   GRANT SELECT, INSERT ON database.* TO 'user'@'host';
   FLUSH PRIVILEGES;

四、性能优化：避免递归查询的性能陷阱

递归查询可能引发性能问题，常见原因及解决方案：

1. 缺少索引：
   递归查询常涉及自连接，确保连接字段（如parent_id）有索引。

   -- 添加索引示例
   ALTER TABLE categories ADD INDEX idx_parent (parent_id);

2. 递归深度过大：
   MySQL默认递归深度限制为1000，可通过设置cte_max_recursion_depth调整：

   SET SESSION cte_max_recursion_depth = 5000;

3. 优化建议：

   • 添加LIMIT限制结果集
   • 使用WHERE条件尽早过滤数据
   • 对大型数据集考虑分批处理

性能对比示例：

-- 低效写法：全表递归后过滤
WITH RECURSIVE cte AS (
    SELECT * FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.* FROM cte p JOIN nodes n ON p.id = n.parent_id
) SELECT * FROM cte WHERE level = 3;
-- 高效写法：递归过程中过滤
WITH RECURSIVE cte AS (
    SELECT *, 0 AS level FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.*, p.level + 1 
    FROM cte p JOIN nodes n ON p.id = n.parent_id
    WHERE p.level < 3  -- 提前终止
) SELECT * FROM cte;

五、替代方案：当WITH RECURSIVE不可用时的选择

对于无法升级MySQL 8.0的场景，可采用以下替代方案：

1. 存储过程+临时表：

   DELIMITER //
   CREATE PROCEDURE GetHierarchy(IN rootId INT)
   BEGIN
    CREATE TEMPORARY TABLE temp_tree (id INT, name VARCHAR(100));
    INSERT INTO temp_tree SELECT id, name FROM categories WHERE id = rootId;
    REPEAT
        INSERT INTO temp_tree
        SELECT c.* FROM categories c
        JOIN temp_tree t ON c.parent_id = t.id
        WHERE c.id NOT IN (SELECT id FROM temp_tree);
    UNTIL ROW_COUNT() = 0 END REPEAT;
    SELECT * FROM temp_tree;
    DROP TEMPORARY TABLE temp_tree;
   END //
   DELIMITER ;

2. 应用层递归：
   在Java等语言中实现递归逻辑，通过多次查询构建树形结构。

3. 嵌套集模型：
   改用left/right值表示层级关系，查询时使用范围条件。

六、常见错误排查清单

当遇到”WITH RECURSIVE用不了”时，按以下步骤排查：

1. 版本检查：SELECT VERSION(); 确认≥8.0
2. 语法验证：检查UNION ALL、列匹配、终止条件
3. 权限确认：SHOW GRANTS; 查看SELECT权限
4. 错误日志：查看MySQL错误日志获取详细信息
5. 简化测试：用简单数据集测试基础递归查询
6. 性能监控：执行EXPLAIN分析查询计划

通过系统化的排查和优化，90%以上的”WITH RECURSIVE用不了”问题可得到解决。对于剩余的复杂场景，建议结合具体业务需求设计替代方案。