- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

SQL高级技巧CTE和递归查询

Ustinian_2022 发表于 2022/07/27 17:57:06 2022/07/27

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

19.1　公用表表达式

从MySQL 8.x版本开始支持公用表表达式（简称为CTE）。公用表表达式通过WITH语句实现，可以分为非递归公用表表达式和递归公用表表达式。

在常规的子查询中，派生表无法被引用两次，否则会引起MySQL的性能问题。如果使用CTE查询的话，子查询只会被引用一次，这也是使用CTE的一个重要原因。

19.1.1　非递归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()) |
+-------------+
|        2020 |
+-------------+
1 row in set (0.00 sec)

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

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

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

通过两种查询的SQL语句对比可以发现，使用CTE查询能够使SQL语义更加清晰。也可以在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 |
+------+-------+
| 2020 |     1 |
+------+-------+
1 row in set (0.00 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 |
+-----------+------------+-----------+------------+
|      2020 |          1 |      2021 |          2 |
+-----------+------------+-----------+------------+
1 row in set (0.00 sec)

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

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

19.1.2　递归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中，子查询包含两种：一种是种子查询（种子查询会初始化查询数据，并在查询中不会引用自身），一种是递归查询（递归查询是在种子查询的基础上，根据一定的规则引用自身的查询）。这两个查询之间会通过UNION、UNION ALL或者UNION DISTINCT语句连接起来。

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

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

递归CTE查询对于遍历有组织、有层级关系的数据时非常方便。例如，创建一张区域数据表t_area，该数据表中包含省市区信息。

mysql> CREATE TABLE t_area(
    -> id INT NOT NULL,
    -> name VARCHAR(30),
    -> pid INT
    -> );
Query OK, 0 rows affected (0.01 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.00 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.00 sec)

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

19.1.3　递归CTE的限制

递归CTE的查询语句中需要包含一个终止递归查询的条件。当由于某种原因在递归CTE的查询语句中未设置终止条件时，MySQL会根据相应的配置信息，自动终止查询并抛出相应的错误信息。在MySQL中默认提供了如下两个配置项来终止递归CTE。

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

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

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

mysql> WITH RECURSIVE cte_num(num) AS
    -> (
    -> SELECT 1
    -> UNION ALL
    -> SELECT n+1 FROM cte_num
    -> )
    -> SELECT * FROM cte_num;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_
depth to a larger value.

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

查看cte_max_recursion_depth参数的默认值。

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

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

接下来，验证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.00 sec)

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

查看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，也就是没有限制。这里，在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.01 sec)

已经成功将max_execution_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;
ERROR 3024 (HY000): 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语句，其他语句读者可以自行实现，不再赘述。

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

SQL高级技巧CTE和递归查询

19.1　公用表表达式

19.1.1　非递归CTE

19.1.2　递归CTE

19.1.3　递归CTE的限制

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

SQL高级技巧CTE和递归查询

19.1 公用表表达式

19.1.1 非递归CTE

19.1.2 递归CTE

19.1.3 递归CTE的限制

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

推荐阅读

相关产品

19.1　公用表表达式

19.1.1　非递归CTE

19.1.2　递归CTE

19.1.3　递归CTE的限制