MySQL 公用表达式

公用表表达式和生成列是MySQL 8.x版本中新增的特性。

本篇文章将简单介绍MySQL中新增的公用表表达式和生成列。

目录

公用表表达式

非递归CTE

子查询

CTE查询

CTE定义多个字段

重用上次查询结果

递归CTE

递归CTE子查询类型

递归CTE的限制

终止递归CTE配置项

查看cte_max_recursion_depth

设置cte_max_recursion_depth

查看max_execution_time

设置max_execution_time

生成列

创建表时指定生成列

为已有表添加生成列

修改已有的生成列

删除生成列

总结

---

公用表表达式

从MySQL 8.x版本开始支持公用表表达式（简称为CTE）。公用表表达式通过WITH语句实现，可以分为非递归公用表表达式和递归公用表表达式。在常规的子查询中，派生表无法被引用两次，否则会引起MySQL的性能问题。如果使用CTE查询的话，子查询只会被引用一次，这也是使用CTE的一个重要原因。

非递归CTE

MySQL 8.0之前，想要进行数据表的复杂查询，需要借助子查询语句实现，但SQL语句的性能低下，而且子查询的派生表不能被多次引用。CTE的出现极大地简化了复杂SQL的编写，提高了数据查询的性能。

非递归CTE的语法格式如下：

WITHcte_name [(col_name [, col_name] ...)] AS (subquery)[, cte_name [(col_name [, col_name] ...)] AS (subquery)] …
SELECT [(col_name [, col_name] ...)] FROM cte_name;

可以对比子查询与CTE的查询来加深对CTE的理解。

子查询

例如：在MySQL命令行中执行如下SQL语句来实现子查询的效果。

mysql> SELECT * FROM  (SELECT YEAR(NOW())) AS year;
+-------------+
| YEAR(NOW()) |
+-------------+
|        2025 |
+-------------+
1 row in set (0.01 sec)

上面的SQL语句使用子查询获取当前年份的信息。

CTE查询

使用CTE实现查询的效果如下：

mysql> WITH year AS (SELECT YEAR(NOW())) SELECT * FROM year;
+-------------+
| YEAR(NOW()) |
+-------------+
|        2025 |
+-------------+
1 row in set (0.01 sec)

通过两种查询的SQL语句对比可以发现，使用CTE查询能够使SQL语义更加清晰。

CTE定义多个字段

也可以在CTE语句中定义多个查询字段，如下：

mysql> WITH cte_year_month (year, month) AS(SELECT YEAR(NOW()) AS year, MONTH(NOW()) AS month)SELECT * FROM cte_year_month;
+------+-------+
| year | month |
+------+-------+
| 2025 |     8 |
+------+-------+
1 row in set (0.02 sec)

重用上次查询结果

CTE可以重用上次的查询结果，多个CTE之间还可以相互引用：

mysql> WITH cte1(cte1_year, cte1_month) AS(SELECT YEAR(NOW()) AS cte1_year, MONTH(NOW()) AS cte1_month),cte2(cte2_year, cte2_month) AS(SELECT (cte1_year+1) AS cte2_year, (cte1_month + 1) AS cte2_month FROM cte1) SELECT * FROM cte1 JOIN cte2;
+-----------+------------+-----------+------------+
| cte1_year | cte1_month | cte2_year | cte2_month |
+-----------+------------+-----------+------------+
|      2025 |          8 |      2026 |          9 |
+-----------+------------+-----------+------------+
1 row in set (0.01 sec)

上面的SQL语句中，在cte2的定义中引用了cte1。

注意：在SQL语句中定义多个CTE时，每个CTE之间需要用逗号进行分隔。

递归CTE

递归CTE的子查询可以引用自身，相比非递归CTE的语法格式多一个关键字RECURSIVE。

WITH RECURSIVEcte_name [(col_name [, col_name] ...)] AS (subquery)[, cte_name [(col_name [, col_name] ...)] AS (subquery)] …
SELECT [(col_name [, col_name] ...)] FROM cte_name;

递归CTE子查询类型

在递归CTE中，子查询包含两种：

种子查询：种子查询会初始化查询数据，并在查询中不会引用自身，

递归查询：递归查询是在种子查询的基础上，根据一定的规则引用自身的查询。

这两个查询之间会通过UNION、UNION ALL或者UNION DISTINCT语句连接起来。

例如：使用递归CTE在MySQL命令行中输出1～8的序列。

mysql> WITH RECURSIVE cte_num(num) AS( SELECT 1 UNION ALLSELECT num + 1 FROM cte_num WHERE num < 8)SELECT * FROM cte_num;
+-----+
| num |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
+-----+
8 rows in set (0.02 sec)

递归CTE查询对于遍历有组织、有层级关系的数据时非常方便。

例如，创建一张区域数据表t_area，该数据表中包含省市区信息。

mysql> CREATE TABLE t_area(id INT NOT NULL,name VARCHAR(30),pid INT);
Query OK, 0 rows affected (0.02 sec)

向t_area数据表中插入测试数据。

mysql> INSERT INTO t_area (id, name, pid)VALUES(1, '河北省', NULL),(2, '邯郸市', 1),(3, '邯山区', 2),(4, '复兴区', 2),(5, '河南省', NULL),(6, '郑州市', 5),(7, '中原区', 6);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

SQL语句执行成功，查询t_area数据表中的数据。

mysql> SELECT * FROM t_area;
+----+--------+------+
| id | name   | pid  |
+----+--------+------+
|  1 | 河北省 | NULL |
|  2 | 邯郸市 |    1 |
|  3 | 邯山区 |    2 |
|  4 | 复兴区 |    2 |
|  5 | 河南省 | NULL |
|  6 | 郑州市 |    5 |
|  7 | 中原区 |    6 |
+----+--------+------+
7 rows in set (0.03 sec)

接下来，使用递归CTE查询t_area数据表中的层级关系。

mysql> WITH RECURSIVE area_depth(id, name, path) AS(SELECT id, name, CAST(id AS CHAR(300))FROM t_area WHERE pid IS NULLUNION ALLSELECT a.id, a.name, CONCAT(ad.path, '->', a.id)FROM area_depth AS ad JOIN t_area AS aON ad.id = a.pid)SELECT * FROM area_depth ORDER BY path;
+----+--------+---------+
| id | name   | path    |
+----+--------+---------+
|  1 | 河北省 | 1       |
|  2 | 邯郸市 | 1->2    |
|  3 | 邯山区 | 1->2->3 |
|  4 | 复兴区 | 1->2->4 |
|  5 | 河南省 | 5       |
|  6 | 郑州市 | 5->6    |
|  7 | 中原区 | 5->6->7 |
+----+--------+---------+
7 rows in set (0.02 sec)

其中，path列表示查询出的每条数据的层级关系。

递归CTE的限制

递归CTE的查询语句中需要包含一个终止递归查询的条件。

当由于某种原因在递归CTE的查询语句中未设置终止条件时，

MySQL会根据相应的配置信息，自动终止查询并抛出相应的错误信息。

终止递归CTE配置项

在MySQL中默认提供了如下两个配置项来终止递归CTE。

cte_max_recursion_depth：如果在定义递归CTE时没有设置递归终止条件，当达到此参数设置的执行次数后，MySQL报错。

max_execution_time：表示SQL语句执行的最长毫秒时间，当SQL语句的执行时间超过此参数设置的值时，MySQL报错。

例如：未设置查询终止条件的递归CTE, MySQL会抛出错误信息并终止查询。

mysql>  WITH RECURSIVE cte_num (n) AS(SELECT 1UNION ALLSELECT n+1 FROM cte_num)SELECT * FROM cte_num;
Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

从输出结果可以看出，当没有为递归CTE设置终止条件时，MySQL默认会在第1001次查询时抛出错误信息并终止查询。

查看cte_max_recursion_depth

查看cte_max_recursion_depth参数的默认值。

mysql> SHOW VARIABLES LIKE 'cte_max%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| cte_max_recursion_depth | 1000  |
+-------------------------+-------+
1 row in set (0.02 sec)

结果显示，cte_max_recursion_depth参数的默认值为1000，所以MySQL会在第1001次查询时抛出错误并终止查询。

设置cte_max_recursion_depth

接下来，验证MySQL是如何根据max_execution_time配置项终止递归CTE。

首先，为了演示max_execution_time参数的限制，

需要将cte_max_recursion_depth参数设置为一个很大的数字，

这里在MySQL会话级别中设置。

mysql> SET SESSION cte_max_recursion_depth=999999999;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'cte_max%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| cte_max_recursion_depth | 999999999 |
+-------------------------+-----------+
1 row in set (0.02 sec)

已经成功将cte_max_recursion_depth参数设置为999999999。

查看max_execution_time

查看MySQL中max_execution_time参数的默认值。

mysql> SHOW VARIABLES LIKE 'max_execution%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 0     |
+--------------------+-------+
1 row in set (0.00 sec)

在MySQL中max_execution_time参数的值为毫秒值，默认为0，也就是没有限制。

设置max_execution_time

在MySQL会话级别将max_execution_time的值设置为1s。

mysql> SET SESSION max_execution_time=1000; 
Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE 'max_execution%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 1000  |
+--------------------+-------+
1 row in set (0.02 sec)

已经成功将max_execution_time的值设置为1s。

当SQL语句的执行时间超过max_execution_time设置的值时，MySQL报错。

mysql> WITH RECURSIVE cte(n) AS(SELECT 1UNION ALLSELECT n+1 FROM CTE)SELECT * FROM cte;
Query execution was interrupted, maximum statement execution time exceeded

MySQL提供的终止递归的机制（cte_max_recursion_depth和max_execution_time），有效地预防了无限递归的问题。

注意：虽然MySQL默认提供了终止递归的机制，但是在使用MySQL的递归CTE时，建议还是根据实际的需求，在CTE的SQL语句中明确设置递归终止的条件。

另外，CTE支持SELECT/INSERT/UPDATE/DELETE等语句，这里只演示了SELECT语句，其他语句可以自行实现。

生成列

MySQL中生成列的值是根据数据表中定义列时指定的表达式计算得出的，主要包含两种类型：VIRSUAL生成列和SORTED生成列，其中VIRSUAL生成列是从数据表中查询记录时，计算该列的值；SORTED生成列是向数据表中写入记录时，计算该列的值并将计算的结果数据作为常规列存储在数据表中。

通常，使用的比较多的是VIRSUAL生成列，原因是VIRSUAL生成列不占用存储空间。

创建表时指定生成列

例如，创建数据表t_genearted_column，数据表中包含DOUBLE类型的字段a、b和c，其中c字段是由a字段和b字段计算得出的，如下：

mysql>  CREATE TABLE t_genearted_column(a DOUBLE,b DOUBLE,c DOUBLE AS (a * a + b * b));
Query OK, 0 rows affected (0.07 sec)

向t_genearted_column数据表中插入数据。

mysql> INSERT INTO t_genearted_column(a, b)VALUES(1, 1),(2, 2),(3, 3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查询t_genearted_column数据表中的数据。

mysql> SELECT * FROM t_genearted_column;
+---+---+----+
| a | b | c  |
+---+---+----+
| 1 | 1 |  2 |
| 2 | 2 |  8 |
| 3 | 3 | 18 |
+---+---+----+
3 rows in set (0.02 sec)

结果显示，在向t_genearted_column数据表中插入数据时，并没有向c字段中插入数据，

c字段的值是由a字段的值和b字段的值计算得出的。

如果在向t_genearted_column数据表插入数据时包含c字段，则向c字段插入数据时，必须使用DEFAULT，否则MySQL报错。

mysql> INSERT INTO t_genearted_column(a, b, c)VALUES(4, 4, 32);
3105 - The value specified for generated column 'c' in table 't_genearted_column' is not allowed.

MySQL报错，报错信息为不能为生成的列手动赋值。

使用DEFAULT关键字代替具体的值。

mysql>  INSERT INTO t_genearted_column(a, b, c)VALUES(4, 4, DEFAULT);
Query OK, 1 row affected (0.00 sec)

SQL语句执行成功，查询t_genearted_column数据表中的数据。

mysql> SELECT * FROM t_genearted_column;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    2 |
|    2 |    2 |    8 |
|    3 |    3 |   18 |
|    4 |    4 |   32 |
+------+------+------+
4 rows in set (0.00 sec)

已经成功为c字段赋值。

也可以在创建表时明确指定VIRSUAL生成列。

mysql> CREATE TABLE t_column_virsual (a DOUBLE,b DOUBLE,c DOUBLE GENERATED ALWAYS AS (a + b) VIRTUAL);
Query OK, 0 rows affected (0.02 sec)

向t_column_virsual数据表中插入数据并查询结果。

mysql> INSERT INTO t_column_virsual(a, b)VALUES(1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t_column_virsual;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 2 |
+---+---+---+
1 row in set (0.02 sec)

为已有表添加生成列

可以使用ALTER TABLE ADD COLUMN语句为已有的数据表添加生成列。

例如：创建数据表t_add_column。

mysql> CREATE TABLE t_add_column(a DOUBLE,b DOUBLE);
Query OK, 0 rows affected (0.10 sec)

向数据表中插入数据。

mysql> INSERT INTO t_add_column (a, b) VALUES (2, 2);
Query OK, 1 row affected (0.01 sec)

为t_add_column数据表添加生成列。

mysql> ALTER TABLE t_add_column ADD COLUMN c DOUBLE GENERATED ALWAYS AS(a * a + b * b) STORED;
Query OK, 1 row affected (0.15 sec)
Records: 1  Duplicates: 0  Warnings: 0

SQL语句执行成功，查询t_add_column数据表中的数据。

mysql> SELECT * FROM t_add_column;
+---+---+---+
| a | b | c |
+---+---+---+
| 2 | 2 | 8 |
+---+---+---+
1 row in set (0.02 sec)

结果：当数据表中存在数据时，为数据表添加生成列，会自动根据已有的数据计算该列的值，并存储到该列中。

修改已有的生成列

例如：修改t_add_column数据表的生成列c，将其计算规则修改为a * b。

mysql> ALTER TABLE t_add_columnMODIFY COLUMN c DOUBLEGENERATED ALWAYS AS (a * b)STORED;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

查询t_add_column数据表中的数据。

mysql>  SELECT * FROM t_add_column;
+---+---+---+
| a | b | c |
+---+---+---+
| 2 | 2 | 4 |
+---+---+---+
1 row in set (0.02 sec)

c列的值此时已经被修改为a列的值乘以b列的值的结果数据。

删除生成列

删除生成列可以使用ALTER TABLE DROP COLUMN语句实现。

例如：删除t_add_column数据表中的生成列c。

mysql> ALTER TABLE t_add_column DROP COLUMN c;
Query OK, 1 row affected (0.10 sec)
Records: 1  Duplicates: 0  Warnings: 0

SQL语句执行成功，再次查看t_add_column数据表中的数据。

mysql> SELECT * FROM t_add_column;
+---+---+
| a | b |
+---+---+
| 2 | 2 |
+---+---+
1 row in set (0.02 sec)

结果：生成列c已经被成功删除。

总结

公用表表达式和生成列是MySQL 8.x版本中新增的特性，

本篇文章简单介绍了有关公用表表达式和生成列的知识。