MySQL 8中的公用表表达式（Common Table Expression，简称CTE）是一种强大的查询工具，它允许你在SQL语句中创建临时的结果集，这个结果集可以在同一语句的其他部分中重复使用。CTE的引入极大地方便了复杂查询的编写，尤其是处理递归关系和分步计算时。
CTE的语法格式如下：
```sql
WITH [RECURSIVE] cte_name [(col_name [, col_name] ...)] AS (
subquery
)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
```
- `WITH` 关键字标志着CTE的开始。
- `[RECURSIVE]` 是可选的，用于定义递归CTE，我们稍后会讨论。
- `cte_name` 是CTE的名称，用于后续查询中引用。
- `(col_name [, col_name] ...)` 定义CTE返回的列名，这在非递归CTE中通常是可选的。
- `AS` 关键字连接CTE的名称和子查询。
- `subquery` 是一个SQL查询，其结果作为CTE。
CTE可以在以下几种场景中使用：
1. 在`SELECT`，`UPDATE`，`DELETE`语句的开头：
```sql
WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...
```
2. 子查询的开头或派生表子查询的开头：
```sql
SELECT ... WHERE id IN (WITH ... SELECT ...)
SELECT * FROM (WITH ... SELECT ...) AS dt ...
```
3. 紧接在`SELECT`之后，用于包含`SELECT`声明的语句：
```sql
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...
```
现在我们来看一个实际的例子，假设有一个`menu`表，存储着网站的菜单结构：
```sql
CREATE TABLE `menu` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(32) DEFAULT '' COMMENT '名称',
`url` varchar(255) DEFAULT '' COMMENT 'url地址',
`pid` int(11) DEFAULT '0' COMMENT '父级ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
插入一些数据后，我们可以使用CTE来查询每个菜单的直接上级名称：
```sql
WITH cte AS (
SELECT * FROM menu
)
SELECT m.*, (SELECT cte.name FROM cte WHERE cte.id = m.pid) AS pname
FROM cte AS m;
```
这个例子中，`cte`是我们的CTE，它包含了`menu`表的所有数据。在主查询中，我们再次引用`cte`，并使用子查询获取每个菜单项的父级名称。
CTE的一个重要特性是它可以是递归的，这意味着它能够处理自我引用的关系，例如树形结构。例如，如果我们想查询所有菜单及其完整的上级路径，可以使用递归CTE：
```sql
WITH RECURSIVE cte AS (
SELECT id, name, url, pid, CAST(name AS CHAR(255)) AS path
FROM menu
WHERE pid = 0
UNION ALL
SELECT m.id, m.name, m.url, m.pid, CONCAT(cte.path, ' > ', m.name)
FROM menu AS m
JOIN cte ON m.pid = cte.id
)
SELECT * FROM cte;
```
在这个递归CTE中，基础案例是所有顶级菜单（`pid = 0`），然后递归地将每个菜单及其上级路径加入到结果集中。
总结来说，MySQL 8的公用表表达式CTE提供了更灵活的查询构造方式，能够简化复杂的查询逻辑，提高代码的可读性和可维护性。无论是处理简单的临时结果集，还是处理递归关系，CTE都是一个非常实用的工具。
