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语句,其他语句读者可以自行实现,不再赘述。

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

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

全部回复

上滑加载中

设置昵称

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

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

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