MySQL 用得上的新特性(二) — WITH(Common Table Expressions)

0x01 白话解释

像变量一样，预先定义子查询，后续SQL可像中间表一样直接引用，如下图所示：

作者注：这种预定义的写法，能减少子查询的嵌套，提升可读性。

0x02 举例说明

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;
复制代码

0x02 官方定义

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
复制代码

0x03 CTE(Common Table Expression)

不那么精确的解释，CTE 就是指预定义的表，如最上方 SQL 所示的 cte1 和 cte2。
官方啰嗦的文档解释如下：

A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times. The following discussion describes how to write statements that use CTEs.

0x04 奇技淫巧

由于这个语法特性，实在是没有提供太多的能力，因此需要在“讨论”的时候，需要出奇制胜。

1. 定义时引用

如果定义的 CTE 之间没有关联，那显然太无趣了，依旧无法消除深层嵌套。在定义 CTE 时，就能直接使用已经定义的 CTE，稍微修改下最上方的代码，举例如下：

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT a as c, b as d FROM `cte1`)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
复制代码

2. 递归创建 CTE

细心的朋友已经发现，官方文档定义中，有个 RECURSIVE 关键字，即递归。实践中，甚至可以用 CTE 来递归生成斐波那契数列！ 举例如下：

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;
复制代码

0x05 UPDATE && DELETE

另外，上面的举例中，紧随 With clause 之后的，都是 Select 语句。With clause 之后是支持 UPDATE 和 DELTE 语句的，举例如下：

with tmp as (
	select id from cte_delete where seq >= 50
) 
delete from cte_delete where id in (select id from tmp);
复制代码

0x06 题外话

1. 照例贴官方文档
2. 照例横向对比数据库 PostgreSQL
   PostgreSQL 功能更完备：
   • With Clause 里面可以使用 UPDATE 和 DELETE

   WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
    )
    INSERT INTO products_log
    SELECT * FROM moved_rows;
   复制代码

   • With Clause 还可以使用 INSERT

   WITH moved_rows AS (
   DELETE FROM products
   WHERE
       "date" >= '2010-10-01' AND
       "date" < '2010-11-01'
   RETURNING *
   )
   INSERT INTO products_log
   SELECT * FROM moved_rows;
   复制代码

0x07 感悟

超过 20 行的 SQL 可读性极差，而相同情况下代码的可读性却很高。
细究之下，读代码过程：定义变量 -> 赋值变量 -> 计算变量 -> 返回结果。
SQL 借助 CTE，过程也能更易读，其流程类似：定义简单 talbe -> 定义简单 table 运算后的复杂 table -> 执行复杂 table 的简易操作 -> 返回结果。

此功能乍看之下，乏善可陈，但实际可用性极高，大大提高可读性、可维护性（就 OLAP 而言）。（尤其是对于 3 个月后重新看复杂 SQL 的自己）

Fin.