mysql 公用表达式_mysql8 WITH AS（公用表表达式）

公用表表达式(CTE)是一个命名的临时结果集，它存在于单个语句的范围内，以后可以在该语句中引用，可能多次。以下讨论描述了如何编写使用CTE的语句。

常用表表达式

要指定公用表表达式，请使用 WITH具有一个或多个逗号分隔子句的子句。每个子句都提供一个子查询，该子查询产生一个结果集，并将一个名称与该子查询相关联。下面的示例定义名为的CTE cte1和cte2中 WITH子句，并且是指在它们的顶层SELECT下面的WITH子句：

WITH

cte1 AS (SELECT a, b FROM table1),

cte2 AS (SELECT c, d FROM table2)

SELECT b, d FROM cte1 JOIN cte2

WHERE cte1.a = cte2.c;

确定给定CTE的列名的过程如下：

如果带括号的名称列表位于CTE名称之后，则这些名称为列名称：

WITH cte (col1, col2) AS

(

SELECT 1, 2

UNION ALL

SELECT 3, 4

)

SELECT col1, col2 FROM cte;

否则，列名来自首的选择列表中SELECT的内 部分： AS (subquery)

WITH cte AS

(

SELECT 1 AS col1, 2 AS col2

UNION ALL

SELECT 3, 4

)

SELECT col1, col2 FROM cte;

WITH同一级别 仅允许一个子句。不允许在同一级别WITH后面跟随WITH，因此这是非法的：

WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...

为了使该语句合法，请使用单个 WITH子句以逗号分隔各子句：

WITH cte1 AS (...), cte2 AS (...) SELECT ...

但是，一个语句可以包含多个 WITH子句(如果它们出现在不同的级别)：

WITH cte1 AS (SELECT 1)

SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

递归公用表表达式

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

WITH RECURSIVE cte (n) AS

(

SELECT 1

UNION ALL

SELECT n + 1 FROM cte WHERE n < 5

)

SELECT * FROM cte;

限制公用表表达式递归

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

该cte_max_recursion_depth 系统变量强制执行递归级别的热膨胀系数为数量限制。服务器终止任何递归级别高于此变量值的CTE的执行。

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

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

SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

SET max_execution_time = 1000; -- impose one second timeout

WITH RECURSIVE cte (n) AS

(

SELECT 1

UNION ALL

SELECT n + 1 FROM cte

)

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

斐波那契数列生成

斐波那契数列以两个数字0和1(或1和1)开始，其后的每个数字是前两个数字的和。如果递归产生的每一行都SELECT可以访问该序列中的前两个数字，则递归公用表表达式可以生成斐波那契数列 。以下CTE使用0和1作为前两个数字来生成10数序列：

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS

(

SELECT 1, 0, 1

UNION ALL

SELECT n + 1, next_fib_n, fib_n + next_fib_n

FROM fibonacci WHERE n < 10

)

SELECT * FROM fibonacci;

CTE产生以下结果：

+------+-------+------------+

| n | fib_n | next_fib_n |

+------+-------+------------+

| 1 | 0 | 1 |

| 2 | 1 | 1 |

| 3 | 1 | 2 |

| 4 | 2 | 3 |

| 5 | 3 | 5 |

| 6 | 5 | 8 |

| 7 | 8 | 13 |

| 8 | 13 | 21 |

| 9 | 21 | 34 |

| 10 | 34 | 55 |

+------+-------+------------+