前言

CTE也就是common table expressions是sql标准里的语法，很多数据库都能够支持，MySQL也在8.0版本里加入了CTE功能，本文主要简单的介绍下该语法的用法，由于笔者对server层了解不深，本文不探讨代码层

CTE与derived table最大的不同之处是

  • 可以自引用，递归使用（recursive cte
  • 在语句级别生成独立的临时表. 多次调用只会执行一次
  • 一个cte可以引用另外一个cte
  • 一个CTE语句其实和CREATE [TEMPORARY] TABLE类似，但不需要显式的创建或删除，也不需要创建表的权限。更准确的说，CTE更像是一个临时的VIEW

示例

语法：

  1. with_clause:
  2.   WITH [RECURSIVE]
  3. cte_name [(col_name [, col_name] ...)] AS (subquery)
  4.   [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

一条语句里可以创建多个cte，用逗号隔开:

  1. WITH cta1 AS (SELECT sum(k) from sbtest1 where id < 100) ,  
  2.      cta2 AS (SELECT SUM(k) from sbtest2 WHERE id < 100) 
  3.   SELECT * FROM cta1 JOIN cta2 ;
  4.   +----------+----------+
  5.   | sum(k)   | SUM(k)   |
  6.   +----------+----------+
  7.   | 49529621 | 49840812 |
  8.   +----------+----------+
  9.   1 row in set (0.00 sec)

递归CTE示例：

  1.   root@sb1 09:41:34>WITH RECURSIVE cte (n) AS
  2.   -> (
  3.       ->   SELECT 1
  4.       ->   UNION ALL
  5.       ->   SELECT n + 1 FROM cte WHERE n < 5
  6.       -> )
  7.   -> SELECT * FROM cte;
  8.   +------+
  9.   | n    |
  10.   +------+
  11.   |    1 |
  12.   |    2 |
  13.   |    3 |
  14.   |    4 |
  15.   |    5 |
  16.   +------+
  17.   5 rows in set (0.00 sec)

递归CTE需要加RECURSIVE关键字，使用Union all来产生结果

  1.   SELECT ...定义初始化值，不引用自身, 同时初始化值的列也定义了cte上的列的个数和类型，可以用cast重定义
  2.   UNION ALL
  3.   SELECT ....返回更多的值，并定义退出循环条件，这里引用了cte自身
  5.   其实现类似于：
  7.   - non-recursive query block is evaluated, result goes into an internal tmp table
  8.   - if no rows, exit
  9.   - (A): recursive query block is evaluated over the tmp table's lastly inserted
  10.          rows, and it produces new rows which are appended to the tmp table (if UNION
  11.              ALL; only distinct not-already-there rows if UNION DISTINCT)
  12.   - if the last step didn't produce new rows, exit
  13. - goto (A)

递归的部分不可以包含:

  1. Aggregate functions such as SUM()
  2. Window functions
  3. GROUP BY
  4. ORDER BY
  5. LIMIT
  6. DISTINCT

再举个典型的斐波拉契数(Fibonacci Series Generation)

  1.   WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
  2.   (
  3.    SELECT 1, 0, 1
  4.    UNION ALL
  5.    SELECT n + 1, next_fib_n, fib_n + next_fib_n
  6.    FROM fibonacci WHERE n < 10
  7.   )
  8.   SELECT * FROM fibonacci;
  10.   +------+-------+------------+
  11.   | n    | fib_n | next_fib_n |
  12.   +------+-------+------------+
  13.   |    1 |     0 |          1 |
  14.   |    2 |     1 |          1 |
  15.   |    3 |     1 |          2 |
  16.   |    4 |     2 |          3 |
  17.   |    5 |     3 |          5 |
  18.   |    6 |     5 |          8 |
  19.   |    7 |     8 |         13 |
  20.   |    8 |    13 |         21 |
  21.   |    9 |    21 |         34 |
  22.   |   10 |    34 |         55 |
  23.   +------+-------+------------+
  24.   10 rows in set (0.00 sec)

关于递归的深度，除了自定义推出条件外，为了避免无限递归，也定义了一个系统参数cte_max_recursion_depth来限制深度，默认值为1000:

  1.   root@sb1 09:53:31>SELECT @@SESSION.cte_max_recursion_depth;
  2.   +-----------------------------------+
  3.   | @@SESSION.cte_max_recursion_depth |
  4.   +-----------------------------------+
  5.   |                              1000 |
  6.   +-----------------------------------+
  7.   1 row in set (0.01 sec)
  9.   root@sb1 09:53:42>WITH RECURSIVE cte (n) AS (   SELECT 1   UNION ALL   SELECT n + 1 FROM cte WHERE n  < 1001) SELECT * FROM cte;
  10.   ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

如何实现

前文已经说过，笔者对Server层代码了解不多，这里只做简单的记录

主要提交的代码

想看实现思路可以阅读如下两个worklog：

WL#883: Non-recursive WITH clause (Common Table Expression)

WL#3634: Recursive WITH (Common Table Expression)

参考文档

官方文档

A Definitive Guide To MySQL Recursive CTE

An Introduction to MySQL CTE

MySQL | Recursive CTE (Common Table Expressions)

之前的月报文章