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 的基本语法

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

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

与派生表不同，CTE 是一个子查询，可以使用其自身名称自引用。它也被称为递归 CTE，并且可以在同一查询中被多次引用。

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

• 它使用 WITH RECURSIVE 子句定义。
• 递归 CTE 必须包含终止条件。
• 我们将使用递归 CTE 进行序列生成以及遍历层次结构或树状结构数据。

MySQL 递归 CTE 语法

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

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

MySQL CTE 示例

让我们通过各种示例来理解 CTE 在 MySQL 中的工作原理。这里，我们将使用一个名为 "employees" 的表进行演示。假设此表包含以下数据

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

定义 CTE employee_in_california 后，我们在 SELECT 语句中引用它，以仅选择位于加州的员工。

执行上述语句后，将得到如下输出。这里，我们可以看到结果仅返回位于加州的员工数据。

更高级的 MySQL CTE 示例

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

表：customer

表：orders

查看以下说明，它使用 INNER JOIN 子句解释了高级 CTE 示例。

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

MySQL 递归 CTE 示例

以下示例解释了递归 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 子句的 SELECT 语句之前使用 WITH 子句，如下所示

使用 CTE 的优点

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