mysql递归函数with recursive的用法举例

mysql递归函数with recursive的用法举例

目录

AS 用法:

例子1:

例子2:

with(Common Table Expressions/CTE)用法:

语法:

例子1:

例子2:

例子3:

with的合法用法:

简单递归用法:

例子1:递归得到依次递增的序列:

例子2:递归得到不断复制的字符串

例子3:生成斐波那契数列

语法说明:

UNION ALL与UNION DISTINCT UNION ALL:

limit控制递归次数

限制递归次数/时间:

补充:MySql8使用WITH RECURSIVE进行递归查询下级节点数据

总结

AS 用法:

AS在mysql用来给列/表起别名.

有时,列的名称是一些表达式,使查询的输出很难理解。要给列一个描述性名称,可以使用列别名。

要给列添加别名,可以使用AS关键词后跟别名

例子1: SELECT [column_1 | expression] AS col_name FROM table_name;

如果别名包含空格,则必须引用以下内容:

例子2: SELECT [column_1 | expression] AS 'col name' FROM table_name; with(Common Table Expressions/CTE)用法:

with在mysql中被称为公共表达式,可以作为一个临时表然后在其他结构中调用.如果是自身调用那么就是后面讲的递归.

语法: with_clause: WITH [RECURSIVE] cte_name [(col_name [, col_name] ...)] AS (subquery) [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

cte_name :公共表达式的名称,可以理解为表名,用来表示as后面跟着的子查询

col_name :公共表达式包含的列名,可以写也可以不写

例子1: WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c; 例子2: WITH cte (col1, col2) AS ( SELECT 1, 2 UNION ALL SELECT 3, 4 ) SELECT col1, col2 FROM cte; 例子3:

这里的第一个as后面接的是子查询,第二个as表示列名,而不是子查询.

WITH cte AS ( SELECT 1 AS col1, 2 AS col2 UNION ALL SELECT 3, 4 ) SELECT col1, col2 FROM cte; with的合法用法:

在子查询(包括派生的表子查询)的开始处

SELECT ... WHERE id IN (WITH ... SELECT ...) ... SELECT * FROM (WITH ... SELECT ...) AS dt ...

同一级别只允许一个WITH子句。同一级别的WITH后面跟着WITH是不允许的,下面是非法用法:

WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...

改为合法用法:

WITH cte1 AS (SELECT 1) SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

在这里面as代表列名,sql不是顺序执行的,这一点了解的话就很好理解这个as了

简单递归用法:

首先我们引出一个问题: 什么叫做递归?

递归:给定函数初始条件,然后反复调用自身直到终止条件.

例子1:递归得到依次递增的序列: WITH RECURSIVE cte (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 5 ) SELECT * FROM cte;

运行结果:

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

官方文档中对于这个写法的解释:

At each iteration, that SELECT produces a row with a new value one greater than the value of n from the previous row set. The first iteration operates on the initial row set (1) and produces 1+1=2; the second iteration operates on the first iteration’s row set (2) and produces 2+1=3; and so forth. This continues until recursion ends, which occurs when n is no longer less than 5.

用python实现就是:

def cte(n): print(n) if n<5: cte(n+1)

也就是说,一个with recursive 由两部分组成.第一部分是非递归部分(union all上方),第二部分是递归部分(union all下方).递归部分第一次进入的时候使用非递归部分传递过来的参数,也就是第一行的数据值,进而得到第二行数据值.然后根据第二行数据值得到第三行数据值.

例子2:递归得到不断复制的字符串

这里的as表示列名,表示说这个CTE有两个列,也可以写为with cte(n,str) as (subquery)

WITH RECURSIVE cte AS ( SELECT 1 AS n, 'abc' AS str UNION ALL SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3 ) SELECT * FROM cte;

结果:

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

这里的话concat是每一次都连接一个str,这个str来自上一行的结果,但是最终输出却是每一行都没有变化的值,这是为什么?
这是因为我们在声明str的时候限制了它的字符长度,使用 类型转换CAST(‘abc’ AS CHAR(30)) 就可以得到复制的字符串了.
**注意:**这里也可能会报错,看mysql模式.在严格模式下这里会显示Error Code: 1406. Data too long for column 'str' at row 1

关于strict SQL mode和nonstrict SQL mode:mysql 严格模式 Strict Mode说明

WITH RECURSIVE cte AS ( SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str UNION ALL SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3 ) SELECT * FROM cte;

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

当然,如果上一行的值有多个,我们还可以对多个值进行重新组合得到我们想要的结果,比如下面这个例子.

例子3:生成斐波那契数列 WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS ( SELECT 1, 0, 1 UNION ALL SELECT n + 1, next_fib_n, fib_n + next_fib_n FROM fibonacci WHERE n < 10 ) SELECT * FROM fibonacci;

结果:

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+

语法说明: UNION ALL与UNION DISTINCT UNION ALL:

UNION ALL:
非递归部分和递归部分用UNION ALL分隔,那么所有的行都会被加入到最后的表中

UNION DISTINCT:
非递归部分和递归部分用UNION DISTINCT分隔,重复的行被消除。这对于执行传递闭包的查询非常有用,以避免无限循环。

limit控制递归次数

recursive(第二个select)不能使用的结构:

官网的描述:

The recursive SELECT part must not contain these constructs:

Aggregate functions such as SUM()

Window functions

GROUP BY

ORDER BY

DISTINCT

限制递归次数/时间:

当出现不符合设置情况的会报错,分为以下几种设置方法:

cte_max_recursion_depth :default 设置为1000,表达递归的层数.可以使用如下语句修改这个值:

SET SESSION cte_max_recursion_depth = 10; -- permit only shallow recursion SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

当然也可以设置为global,也就是set global cte_max_recursion_depth = 1000000;这样子就对全局的递归都有限制

max_execution_time :设置最近的递归时间

SET max_execution_time = 1000; -- impose one second timeout

MAX_EXECUTION_TIME:设置全局的递归时间

官网文档说明如下:

The cte_max_recursion_depth system variable enforces a limit on the
number of recursion levels for CTEs. The server terminates execution
of any CTE that recurses more levels than the value of this variable.

The max_execution_time system variable enforces an execution timeout
for SELECT statements executed within the current session.

The MAX_EXECUTION_TIME optimizer hint enforces a per-query execution
timeout for the SELECT statement in which it appears.

limit:限之最大行的数量

WITH RECURSIVE cte (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte LIMIT 10000 ) SELECT * FROM cte; 补充:MySql8使用WITH RECURSIVE进行递归查询下级节点数据 #查询id=62的所有子节点 WITH RECURSIVE temp AS ( SELECT * FROM tbsys_office o WHERE o.id=62 UNION ALL SELECT o.* FROM tbsys_office o,temp t WHERE t.id=o.parent_id ) SELECT * FROM temp; #查询id=80的所有父节点 WITH RECURSIVE temp AS ( SELECT * FROM tbsys_office o WHERE o.id=80 UNION ALL SELECT o.* FROM tbsys_office o,temp t WHERE t.parent_id=o.id ) SELECT * FROM temp; 总结

到此这篇关于mysql递归函数with recursive用法的文章就介绍到这了,更多相关mysql递归函数with recursive内容请搜索易知道(ezd.cc)以前的文章或继续浏览下面的相关文章希望大家以后多多支持易知道(ezd.cc)!

推荐阅读

    mac怎么安装mysql| macbook怎么下载mysql

    mac怎么安装mysql| macbook怎么下载mysql,标签,学习python 不得不学习的就是数据库,那么在开始学习数据库之前,那么今天我们先说下怎么安装m

    MySQL更新更新页面1/4。

    MySQL更新更新页面1/4。,,插入和替换 插入和替换语句的功能是将新数据插入表中,这两个语句的语法相似,它们之间的主要区别是如何处理重复的

    keep up with什么意思

    keep up with什么意思,意思,区别,本文目录keep up with什么意思keep up with是个什么意思keep up with是什么意思*keep up with 和catch u

    mysql数据库启动失败

    mysql数据库启动失败,报错,非正常,1、原因 公司服务器故障,非正常停机导致数据库启动失败。 报错信息 [ [email protected] dmp]# /etc/ini

    mysql长整型是什么

    mysql长整型是什么,整型,数据类型,语法,用户,填充,版本,MySQL长整型是“BIGINT”,是MySQL中最常用的数据类型之一,其可以用来存储较大的整数值,与

    深入理解MySQL分区

    深入理解MySQL分区,查询,数据,列表,索引,操作,按键,MySQL数据库是一个开源的关系型数据库管理系统。在一些大型数据环境中,为了更好地管理数据、

    mysql 如何查询

    mysql 如何查询,查询,数据,语句,条件,选取,排序,MySQL是一款常用的关系型数据库管理系统,被广泛应用于各种网站和应用开发。在MySQL中查询数据是

    MySQL中怎么实现分页操作

    MySQL中怎么实现分页操作,数据,显示,偏移量,分页,查询,初始,一、 背景什么是分页,就是查询时候数据量太大,一次性返回所有查询结果既耗费网络资源

    mysql怎么连接数据库

    mysql怎么连接数据库,服务,启动,数据库,命令,登录,步骤,mysql连接数据库的方法:1、通过计算机管理方式或通过命令行方式执行“net start mysql”

    mysql 查询拼接

    mysql 查询拼接,函数,字段,字符串,查询,分隔符,连接,MySQL 查询拼接在使用 MySQL 进行查询时,有时需要将多个字段或多张表中的字段进行拼接,这时

    怎么启动 mysql

    怎么启动 mysql,启动,服务器,输入,命令提示符,终端,命令,MySQL是一种广泛使用的关系型数据库管理系统。它是一种可扩展性强、性能卓越、跨平台