MySQL 公用表达式的实现示例

目录

• 公用表表达式
• 非递归CTE
• 递归CTE
• 递归CTE的限制
• 生成列
• 创建表时指定生成列
• 为已有表添加生成列
• 修改已有的生成列
• 删除生成列
• 总结

公用表表达式和生成列是mysql 8.x版本中新增的特性。本篇文章将简单介绍MySQL中新增的公用表表达式和生成列。

公用表表达式

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

非递归CTE

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

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

WITH
      cte_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 RECURSIVE
      cte_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 ALL
  SELECT 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 VandroidARCHAR(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 NULL
  UNION ALL
  SELECT a.id, a.name, CONCAT(ad.path, '->', a.id)
  FROM area_depth AS ad 
  JOIN t_area AS a
  ON 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 1
  UNION ALL
  SELECT 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_execphpution_time的值设置为1s。

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

mysql> WITH RECURSIVE cte(n) AS
  (
  SELECT 1
  UNION ALL
  SELECT 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 phpINTO 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_copythonlumn
  (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_column
  MODIFY COLUMN c DOUBLE
  GENERATED 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列的值的结果数据。

删除生python成列

删除生成列可以使用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 公用表达式的实现示例的文章就介绍到这了,更多相关MySQL 公用表达式内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！