MySQL 公共表表达式(CTE)

在 MySQL 中，每个语句或查询都会产生一个临时结果或关系。公共表表达式或 CTE 用于命名存在于该特定语句的执行范围内的那些临时结果集，例如 CREATE、INSERT、SELECT、UPDATE、DELETE等。

与 CTE 相关的一些关键点是：

• 它是通过使用WITH子句定义的。
• WITH 子句允许我们在单个查询中指定多个 CTE。
• CTE 可以引用属于同一 WITH 子句的其他 CTE，但这些 CTE 应该在前面定义。
• CTE 的执行范围存在于使用它的特定语句中。

一、 MySQL 公共表表达式(CTE) 语法

MySQL CTE 的语法包括名称、可选的列列表和定义公用表表达式 (CTE) 的语句/查询。定义 CTE 后，我们可以将其用作 SELECT、INSERT、UPDATE 和 DELETE 查询中的视图。

以下是MySQL中 CTE 的基本语法：

WITH cte_name (column_names) AS (query)   
SELECT * FROM cte_name;  

这是为了确保 CTE 参数中的列数必须与查询中的列数相同。如果我们没有在 CTE 参数中定义列，它将使用定义 CTE 的查询列。

与派生表类似，它不能存储为对象，一旦查询执行完成就会丢失。与派生表相比，CTE 提供了更好的可读性并且还提高了性能。

与派生表不同，CTE 是一个子查询，可以使用自己的名称进行自引用。它也称为递归 CTE，也可以在同一个查询中多次引用。

与递归 CTE 相关的一些要点是：

• 它是通过使用 WITH RECURSIVE 子句定义的。

• 递归 CTE 必须包含终止条件。
• 我们将使用递归 CTE 来生成和遍历分层或树结构数据。

二、MySQL 递归 CTE 语法 

以下是 MySQL 中递归 CTE 的基本语法：

WITH RECURSIVE cte_name (column_names) AS ( subquery )   
SELECT * FROM cte_name;  

在这里，子查询是一个 MySQL 查询，通过使用 cte_name 作为自己的名称来引用自己。

三、 MySQL 公共表表达式(CTE) 示例

让我们通过各种示例了解 CTE 在 MySQL 中是如何工作的。在这里，我们将使用“员工”表进行演示。假设此表包含以下数据：

执行以下语句以了解 CTE 的概念。在此示例中，CTE 名称为employee_in_california，定义 CTE 的子查询返回三列 emp_name、emp_age 和 city。因此，CTE employee_in_california 将返回位于加利福尼亚市的所有员工。

在定义 CTE employee_in_california 之后，我们在SELECT语句中引用了它以仅选择位于加利福尼亚的员工。

WITH employees_in_california AS (  
    SELECT * FROM employees WHERE city = 'California'   
    )   
    SELECT emp_name, emp_age, city FROM employees_in_california  
    WHERE emp_age >= 32 ORDER BY emp_name;  

四、更高级的 MySQL CTE 示例

假设我们有一个名为customer和order的表，其中包含以下数据

customer表：

order表：

请参阅下面的语句，该语句解释了使用INNER JOIN的高级 CTE 示例。

WITH total_customer_2020 AS (  
    SELECT cust_id, name, occupation FROM customer   
    INNER JOIN orders USING (cust_id)  
    ORDER BY age  
)  
SELECT * FROM orders JOIN total_customer_2020 USING (cust_id);  

执行后，我们会得到如下输出：

五、MySQL 递归 CTE 示例

以下示例解释了递归 CTE 的工作原理。考虑以下生成一系列前五个奇数的语句：

WITH RECURSIVE   
odd_num_cte (id, n) AS  
(  
SELECT 1, 1   
union all  
SELECT id+1, n+2 from odd_num_cte where id < 5   
)  
SELECT * FROM odd_num_cte;  

执行上述语句后，将给出如下输出：

上面的语句由两部分组成，一个是非递归的，另一个是递归的。

非递归：SELECT 1, 1

这部分将生成具有两列“id”和“n”以及单行的初始行。

递归：SELECT id+1, n+2 from odd_num_cte where id < 5

这部分负责将行添加到先前的输出中，直到不满足终止条件（id < 5）。当 id 达到 5 时，条件变为 false，递归过程终止。

六、WITH 语句使用

MySQL 提供了许多上下文来使用 WITH 子句来创建 CTE。让我们一一详细讨论。

首先，我们可以在 SELECT、UPDATE 和 DELETE 查询的开头使用 WITH 子句，如下所示。

WITH ... SELECT ...  
WITH ... UPDATE ...  
WITH ... DELETE ...  

其次，我们可以在子查询或派生表子查询的开头使用 WITH 子句，如下所示：

SELECT ... WHERE id IN (WITH ... SELECT ...);  
  
SELECT * FROM (WITH ... SELECT ...) AS derived_table;  

第三，我们可以在包含 SELECT 子句的 SELECT 语句之前使用 WITH 子句，如下所示：

CREATE TABLE ... WITH ... SELECT ...  
CREATE VIEW ... WITH ... SELECT ...  
INSERT ... WITH ... SELECT ...  
REPLACE ... WITH ... SELECT ...  
DECLARE CURSOR ... WITH ... SELECT ...  
EXPLAIN ... WITH ... SELECT ...  

七、使用 CTE 的好处

• 它提供了更好的查询可读性。
• 它提高了查询的性能。
• CTE 允许我们将其用作 VIEW 概念的替代方案
• 它也可以用作 CTE 的链接以简化查询。
• 它还可以用于轻松实现递归查询。