MySQL中普通CTE和递归CTE的顺序问题小结

目录

• 一、with用法系列文章
• 二、前言
• 三、mysql 普通CTE与递归CTE混合使用的严格规则
• 四、解决方案
• 4.1、方法1：相互依赖的递归CTE
• 4.2、方法2：嵌套查询
• 4.3、方法3：使用临时表
• 4.4、方法4：分开执行（应用层处理）

本文主要探讨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报错 ，所以我想知道http://www.devze.com是否能混合使用，本文就是来讨论这个问题。

原生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;

如下图：sql中有部分是公共的

在我的设想里，我想把公共部分提取成普通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_co编程客栈de,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：相互依赖的递归CTE

下述示例中定义了两个递归CET， 两个CTE之间可以相互引用

WITH RECURSIVE
cte1 AS (
    SELECT id, parent_id, name, 1 AS level 
    FROM tree WHERE parent_id IS NULL
    UNION ALL
    SELECT t.id, t.parent_id, t.name, cte1.level + 1
    FROM tree t JOIN cte1 ON t.parwww.devze.coment_id = cte1.id
),
cte2 AS (
    -- 这个CTE依赖于cte1的结果
    SELECT id, COUNT(*) AS child_count 
    FROM cte1 GROUP BY id
)
SELECT cte1.*, cte2.child_count
FROM cte1 LEFT JOIN cte2 ON cte1.id = cte2.id;

按照本文中的示例，我们想先写with as语句把临时表先提取出来，然后再with recursive开始递归，但是这种写法是错误的。因此按照方法一的解决方法：把with as提取出的临时表变成with recursive的写法，但是只包含基础查询部分，不包含递归，然后在下面的递归部分中引用临时表。

即SQL如下：

with RECURSIVE
relation as ( -- 递归CTE但是只有基础查询部分，没有union all递归部分
    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
 ),
 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;

上述这种语法在Mysql8.0+版本中是支持的。

4.2、方法2：嵌套查询

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

4.3、方法3：使用临时表

-- 先创建临时表存储普通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.4、方法4：分开执行（应用层处理）

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

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

到此这篇关于MySQL中普通CTE和递归CTE的顺序问题小结的文章就介绍到这了,更多相关MySQL普通CTE和递归CTE内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！