MariaDB 10.2.2 開始支援 WITH RECURSIVE Query

我現在是用 10.2.7

雖然 CTE 在其他資料庫上可以用很久了

但是從MySQL入門

甚至一直以來都只用MySQL的人應該對這很陌生

因為MySQL還要等到未來的 MySQL 8 才會支援 with

根據官方對 CTE 的描述

Common Table Expressions (CTEs) are a standard SQL feature, and are essentially temporary named result sets. There are two kinds of CTEs: Non-Recursive, which this article covers; and Recursive.

CTEs 是標準的SQL功能，本質上是一個有名字的結果集合，有兩種 CTEs ，非 recursive 跟 recursive 。

白話點說，也就是給了名字的子查詢啦

但比子查詢更好用的地方在於

有了名字就可以重複使用

在過去子查詢放在某個地方之後

就只能在那邊使用

如果想在別的地方用到同樣的子查詢資料

就只能再查詢一次

實在很浪費資源

使用 CTEs 就沒這個困擾了

這裡引用官方的例子：

WITH engineers AS(
    SELECT
        *
    FROM
        employees
    WHERE
        dept IN(
            'Development',
            'Support'
        )
),
SELECT
    *
FROM
    engineers E1
WHERE
    NOT EXISTS(
        SELECT
            1
        FROM
            engineers E2
        WHERE
            E2.country = E1.country
            AND E2.name <> E1.name
    );

這段是在找某幾個部門之內

不同人之間卻是住同一個城市的人員資料

這在以前要透過 self-join 來做

現在用 with query

就可以很方便做到了

先用一道子查詢查出某幾個部門的人

並命名為 employees

再對這個 employees 篩選出兩者名字不同卻住一樣城市的人

而且這樣可讀性比較好

很清楚可以知道先篩選出部門

再對剩下的做其他處理

而 recursive query

是建構其之上

官方的例子就非常好懂

有一張家族關係表

如果我要找 Alex 的所有長輩

在過去要用很多子查詢

或是下很多道sql command

並在程式裡處理

現在有了 recursive query

可以用一道指令就全部抓出來

WITH RECURSIVE ancestores AS(
    SELECT
        *
    FROM
        folks
    where
        name = 'Alex'
UNION SELECT
        f.*
    FROM
        folks AS f,
        ancestores AS a
    WHERE
        f.id = a.father
        OR f.id = a.mother
) 
SELECT * FROM ancestores;