MySQL: with as与with RECURSIVE如何混合使用？

本文主要探讨mysql中with普通cte与递归cte如何混合使用。

一、with用法系列文章

关于with用法与with RECURSIVE的用法可以参考本人的另外两篇博文。

• 《sql中with as用法/with-as 性能调优/with用法》
• 《MYSQL的（WITH RECURSIVE）递归查询》

二、前言

在使用with RECURSIVE 递归查询的过程中，发现有一段sql是公共的，因此想把这部分sql提取出去，当做临时表。 with as子查询就可以当做临时表，所以我就在想能不能先用with as把公共部分查询成临时表，后面再跟着with RECURSIVE 递归查询。即with as与with RECURSIVE 混合使用。

经测试后发现先普通CTE再递归CTE时sql报错 ，所以我想知道是否能混合使用，本文就是来讨论这个问题。

sql示例如下：

-- 查询任务2子节点
WITH RECURSIVE cte AS (
    SELECT r.id,r.project_code, r.code, r.name,r.parent_project_code,r.parent_code
    FROM t_ds_process_dependent_relation r
    inner join t_ds_process_definition d on r.project_code = d.project_code and r.code = d.code and r.version = d.version
    WHERE r.code = 18418446171042 -- 任务2
    
    UNION ALL
    
    SELECT t.id,t.project_code, t.code, t.name,t.parent_project_code,t.parent_code
    FROM t_ds_process_dependent_relation t
    inner join t_ds_process_definition d on t.project_code = d.project_code and t.code = d.code and t.version = d.version
    INNER JOIN cte c ON t.parent_project_code = c.project_code and t.parent_code = c.code
)
SELECT * FROM cte;

在我的设想里，我想把公共部分提取成普通CTE, 然后在递归CTE中引用，但是这种语法在mysql中是错误的

错误SQL如下:

with relation as ( -- 普通CTE
    SELECT r.id,r.project_code, r.code, r.name,r.parent_project_code,r.parent_code
    FROM t_ds_process_dependent_relation r
    inner join t_ds_process_definition d on r.project_code = d.project_code and r.code = d.code and r.version = d.version
 ),
  RECURSIVE cte AS ( -- 递归CTE
    SELECT id,project_code, code, name,parent_project_code,parent_code
    FROM relation
    WHERE code = 18418446171042 -- 任务2
    UNION ALL
    SELECT t.id,t.project_code, t.code, t.name,t.parent_project_code,t.parent_code
    FROM relation t
    INNER JOIN cte c ON t.parent_project_code = c.project_code and t.parent_code = c.code
)
SELECT * FROM cte;

三、MySQL 普通CTE与递归CTE混合使用的严格规则

在 MySQL 中，不可以 先定义普通 CTE 再定义递归 CTE。这是 MySQL 与某些其他数据库（如 PostgreSQL）的一个重要语法差异。

MySQL 的严格规则

• 必须将 RECURSIVE 关键字紧跟在 WITH 之后

• 第一个 CTE 必须是递归 CTE（如果使用了 RECURSIVE 关键字）

• 所有 CTE（包括普通 CTE）都必须放在同一个 WITH RECURSIVE 块中

正确写法示例：

WITH RECURSIVE
    -- 必须先定义递归CTE
    recursive_cte AS (
        -- 基础部分
        SELECT ...
        
        UNION ALL
        
        -- 递归部分
        SELECT ... FROM recursive_cte ...
    ),
    
    -- 然后才能定义普通CTE
    normal_cte AS (
        SELECT ... FROM ...
    )

-- 主查询
SELECT ... FROM recursive_cte JOIN normal_cte ...

错误写法示例：

-- 这样写会报错！
WITH
    normal_cte AS (SELECT ...),  -- 先普通CTE
    RECURSIVE                   -- 后RECURSIVE
    recursive_cte AS (SELECT ...)
SELECT ...

四、解决方案

如果确实需要先处理普通 CTE 再处理递归 CTE，可以考虑以下方法：

4.1、方法1：嵌套查询

WITH RECURSIVE
    -- 将普通CTE的逻辑嵌入到递归CTE的基础部分
    recursive_cte AS (
        -- 基础部分包含普通CTE逻辑
        WITH normal_cte AS (SELECT ...)
        SELECT ... FROM normal_cte WHERE ...
        
        UNION ALL
        
        -- 递归部分
        SELECT ... FROM recursive_cte ...
    )
SELECT ... FROM recursive_cte;

4.2、方法2：使用临时表

-- 先创建临时表存储普通CTE结果
CREATE TEMPORARY TABLE temp_normal AS
SELECT ... FROM ...;

-- 然后使用递归CTE
WITH RECURSIVE recursive_cte AS (
    SELECT ... FROM temp_normal ...
)
SELECT ... FROM recursive_cte;

-- 最后删除临时表
DROP TEMPORARY TABLE temp_normal;

4.3、方法3：分开执行（应用层处理）

-- 第一个查询：执行普通CTE
SET @var = (SELECT ... FROM ...);

-- 第二个查询：执行递归CTE
WITH RECURSIVE recursive_cte AS (
    SELECT ... WHERE ... = @var
)
SELECT ... FROM recursive_cte;