MySQL递归sql语句WITH表达式实现方法代码

前言:

这里一般来说需要编一个故事但是我懒

mysql递归CTE: 8.0版本以上才有WITH AS,8.0以下版本的话请绕行----->不是说8.0以下不能写递归只是不是这个文章的写法,所以看了也没用不用浪费时间

文档原话:

文档英文原话:

官方文档链接

先上可以cv的,不着急写需求的可以往下看看或者看不懂的话可以往下看看

sql语句

# n: 迭代次数
# id, name, parentId: 想要查询的字段,根据自己需求进行修改
# cte_test_paths: 储存区名字是自己创建的这个地方需要和最后的SELECT * FROM cte_test_paths WHERE n = 1;中的表名相同
WITH RECURSIVE cte_test_paths (n, id, name, parentId) AS
                   (
                       # 0 AS n的0表示第一次递归从零开始计数,因为一般来说第一次递归会查询最高级,一般情况下的最高级,而不是真正的第一级,可以根据业务变更
                       SELECT 0 AS n,
                              id,
                              name,
                              parentId
                              # cte_test: 你需要取递归的表,这里需要注意的是递归公用表表达式'cte_test_paths'在递归查询块中既不能包含聚合函数也不能包含窗口函数
                       from cte_test
                       # WHERE后接需要查询的条件,比如这里是第一代的id,也就是第一代的标志,
                       WHERE id = 1
                       UNION ALL
                       SELECT n + 1, e.id, e.name, e.parentId
                       FROM cte_test_paths AS etp
                                # 这里联表的条件是递归的上级id和下级id的关系,需要根据自己的实际环境进行修改
                                JOIN cte_test AS e ON etp.id = e.parentId
                       # 这里的 n<1 是为了限制迭代次数避免无限迭代浪费性能,比如说我只需要查询两代了的话,但是不做代数限制,却查了所有代,这是没有必要的浪费
                       WHERE n < 1)
SELECT *
FROM cte_test_paths
# 这里之所以做条件查询的原因是因为,我只想看到第一代(因为我的第一代是不包含根代,这里的根代的意思就是一个第一代都要属于他子节点的最顶级,所以根据上述 0 AS n 第一代并不是0而是1)
WHERE n = 1;

文档翻译的

递归公用表表达式

CTE 可以指代自身或其他 CTE：

• 自引用 CTE 是递归的。

• CTE 可以指在同一WITH子句中较早定义的 CTE ，但不能指稍后定义的CTE 。

  此约束排除了相互递归的 CTE，其中 cte1引用cte2 和cte2引用 cte1。其中一个引用必须是稍后定义的 CTE，这是不允许的。

• 给定查询块中的 CTE 可以指在更外层的查询块中定义的 CTE，但不能指在更内层的查询块中定义的 CTE。

为了解析对同名对象的引用，派生表隐藏了 CTE；和 CTE 隐藏基表、 TEMPORARY表和视图。名称解析通过在同一查询块中搜索对象，然后在没有找到具有该名称的对象的情况下依次进行外部块来进行。

与派生表一样，CTE 不能包含 MySQL 8.0.14 之前的外部引用。这是 MySQL 8.0.14 中解除的 MySQL 限制，而不是 SQL 标准的限制。有关特定于递归 CTE 的其他语法注意事项，请参阅 递归公用表表达式

递归公用表表达式是具有引用其自身名称的子查询的表达式。例如：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

执行时，该语句会产生以下结果，即包含简单线性序列的单列：

递归 CTE 具有以下结构：

• 如果WITH子句中的任何CTE引用自身，则with子句必须以 WITH RECURSIVE开头。(如果没有CTE引用自身，则允许递归，但不是必需的。)

  如果您忘记了递归CTE的RECURSIVE，则可能会导致此错误:

  ERROR 1146 (42S02): Table 'cte_name' doesn't exist
  

• 递归 CTE 子查询有两个部分，由UNION [ALL\] 或分隔 UNION DISTINCT：

  SELECT ...      -- return initial row set
  UNION ALL
  SELECT ...      -- return additional row sets
  

  第一个SELECT生成CTE的初始行或多行，并且不引用CTE名称。第二个SELECT通过引用其FROM子句中的CTE名称来生成其他行和递归。当此部分不生成新行时，递归结束。因此，递归CTE由非递归SELECT部分和递归SELECT部分组成。

  每个SELECT部分本身可以是多个SELECT 语句的联合。

• CTE结果列的类型SELECT仅从非递归部分的列类型推断出来 ，列都是可以为空的。对于类型确定，递归SELECT部分将被忽略。

• 如果非递归部分和递归部分由 分隔 UNION DISTINCT，则消除重复行。这对于执行传递闭包的查询很有用，以避免无限循环。

• 递归部分的每次迭代仅对前一次迭代产生的行进行操作。如果递归部分有多个查询块，则每个查询块的迭代按未指定的顺序进行调度，并且每个查询块对自上次迭代结束后由其上一次迭代或其他查询块生成的行进行操作。

前面显示的递归 CTE 子查询具有此非递归部分，它检索单个行以生成初始行集：

SELECT 1

CTE 子查询也有这个递归部分：

SELECT n + 1 FROM cte WHERE n < 5

在每次迭代中，该SELECT生成一行，其新值大于上一行集中的值n。第一次迭代对初始行集 (1) 进行操作，并产生1 + 1 = 2; 第二次迭代对第一次迭代的行集 (2) 进行操作并产生2 + 1 = 3; 等等。这一直持续到递归结束，当n不小于5时发生。

如果CTE的递归部分比非递归部分产生更宽的列值，则可能需要加宽非递归部分中的列以避免数据截断。考虑以下陈述:

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3 
  # 别查了CONCAT拼接字符串函数
)
SELECT * FROM cte;

在非严格 SQL 模式下，该语句产生以下输出：

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

的str列值都是 'abc'因为非递归 SELECT确定列宽。因此，str递归产生的更广泛的值SELECT 被截断。

在严格的 SQL 模式下，该语句会产生错误：

ERROR 1406 (22001): Data too long for column 'str' at row 1

要解决此问题，使语句不会产生截断或错误，请CAST() 在非递归中使用SELECT以使str列更宽：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

现在语句产生这个结果，没有截断：

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

列是按名称而不是位置访问的(具体看一下下方例子就明白了)，这意味着递归部分中的列可以访问非递归部分中具有不同位置的列，如本 CTE 所示：

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 1 AS p, -1 AS q
  UNION ALL
  SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;

因为p一行是q从前一行派生的 ，反之亦然，正负值在输出的每一行中交换位置：(这里不明白的话自己算一下就知道了)

+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+

一些语法约束适用于递归 CTE 子查询：

• 递归SELECT部分不得包含以下结构：

  • 聚合函数，例如 SUM()
  • Window functions
  • GROUP BY
  • ORDER BY
  • DISTINCT

  在MySQL 8.0.19之前，递归CTE的递归选择部分也不能使用LIMIT子句。在MySQL 8.0.19中取消了此限制，现在在这种情况下支持LIMIT以及可选的OFFSET子句。对结果集的影响与在最外层选择中使用限制时相同，但也更有效，由于将其与递归选择一起使用，因此一旦生成了请求的行数，就会停止生成行数。

  这些约束不适用于递归CTE的非递归选择部分。对DISTINCT的禁止仅适用于工会成员; 允许使用UNION DISTINCT。

• 递归SELECT部分必须仅在其FROM子句中引用 CTE 一次，而不能在任何子查询中引用。它可以引用CTE以外的表，并将它们与CTE连接起来。如果在这样的连接中使用，CTE 不得位于LEFT JOIN的右侧.

这些约束来自 SQL 标准，除了 MySQL 特定的ORDER BY、 LIMIT（MySQL 8.0.18 及更早版本）和 DISTINCT排除项.

对于递归CTE，EXPLAIN 递归SELECT 部分部分在Extra列中显示Recursive的输出行。

EXPLAIN显示的成本估算表示每次迭代的成本，可能与总成本有很大不同。优化器无法预测迭代次数，因为它无法预测WHERE子句在什么时候变为false。

CTE实际成本也可能受到结果集大小的影响。产生许多行的CTE可能需要足够大的内部临时表才能从内存转换为磁盘格式，并且可能会遭受性能损失。如果是这样，增加允许的内存内临时表大小可能会提高性能; 请参阅第8.4.4节 “MySQL中的内部临时表使用”。

限制公用表表达式递归

对于递归CTE来说，递归选择部分包括终止递归的条件是很重要的。作为防止失控的递归CTE的开发技术，您可以通过限制执行时间来强制终止:

• 该cte_max_recursion_depth 系统变量对CTE的递归级别数量进行限制。服务器终止任何递归级别超过此变量值的 CTE 的执行。

• 所述max_execution_time 系统变量强制用于执行超时 SELECT在当前会话中执行的语句。

• 该MAX_EXECUTION_TIME 优化器提示强制为每个查询执行超时SELECT在它出现的语句。

  ps:下面两个时间我也是在没看明白,要是有大佬看见的话麻烦解释一二

假设在没有递归执行终止条件的情况下错误地编写了递归 CTE：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT * FROM cte;

默认情况下， cte_max_recursion_depth值为 1000，导致 CTE 在递归超过 1000 个级别时终止。应用程序可以更改会话值以根据其要求进行调整：

SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

您还可以设置全局 cte_max_recursion_depth值以影响随后开始的所有会话。

对于执行并因此缓慢递归或在有理由将cte_max_recursion_depth值设置得非常高的上下文中的查询， 防止深度递归的另一种方法是设置每个会话超时。为此，请在执行 CTE 语句之前执行如下语句：

SET max_execution_time = 1000; -- impose one second timeout

或者，在 CTE 语句本身中包含优化器提示：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

从 MySQL 8.0.19 开始，您还可以 LIMIT在递归查询中使用来强加要返回到最外层的最大行数 SELECT，例如：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;

除了或代替设置时间限制，您还可以执行此操作。因此，以下 CTE 在返回一万行或运行一秒（1000 毫秒）后终止，以先发生者为准：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

如果没有执行时间限制的递归查询进入无限循环，您可以使用 KILL QUERY. 在会话本身内，用于运行查询的客户端程序可能会提供终止查询的方法。例如，在 mysql 中，输入Control+C 会 中断当前语句。

创建测试单表数据

• 测试表cte_test创建

  create table cte_test
  (
      id       bigint  not null comment 'ID',
      parentId bigint  not null comment '爹id',
      name     char(3) null comment '名字',
      unique (id)
  )
      comment '递归测试表';
  

• 插入测试数据

  INSERT INTO cte_test (id, parentId, name) VALUES (1, 0, '用户1');
  INSERT INTO cte_test (id, parentId, name) VALUES (2, 1, '用户2');
  INSERT INTO cte_test (id, parentId, name) VALUES (3, 1, '用户3');
  INSERT INTO cte_test (id, parentId, name) VALUES (4, 1, '用户4');
  INSERT INTO cte_test (id, parentId, name) VALUES (5, 4, '用户5');
  INSERT INTO cte_test (id, parentId, name) VALUES (6, 4, '用户6');
  INSERT INTO cte_test (id, parentId, name) VALUES (7, 6, '用户7');
  INSERT INTO cte_test (id, parentId, name) VALUES (8, 7, '用户8');
  

• 查询第一代的所有用户

  • sql

    WITH RECURSIVE cte_test_paths (n, id, name, parentId) AS
                       (
                           SELECT 0 AS n, id, name, parentId
                           from cte_test
                           WHERE id = 1
                           UNION ALL
                           SELECT n + 1, e.id, e.name, e.parentId
                           FROM cte_test_paths AS etp
                                    JOIN cte_test AS e ON etp.id = e.parentId
                           WHERE n < 1)
    SELECT *
    FROM cte_test_paths
    WHERE n = 1;
    

  • 数据

    

• 查询所有用户和代数

  • sql

    WITH RECURSIVE cte_test_paths (n, id, name, parentId) AS
                       (
                           SELECT 0 AS n, id, name, parentId
                           from cte_test
                           WHERE id = 1
                           UNION ALL
                           SELECT n + 1, e.id, e.name, e.parentId
                           FROM cte_test_paths AS etp
                                    JOIN cte_test AS e ON etp.id = e.parentId
                       )
    SELECT *
    FROM cte_test_paths;
    

  • 数据

    

总结