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;

首先他會執行上半部

SELECT * FROM folks where name = ‘Alex’

把Alex抓出來

這部分資料會被看成 ancestors 的結果

第二步開始,就會不斷的抓出要聯集的資料,直到沒資料為止

所以他會抓出 Alex 的爸爸跟媽媽

再塞進 ancestors 的結果裡

他會一直抓資料放到結果裡

直到沒資料並停住

最後就是抓出 Alex 的所有長輩了

這功能實在太好用啦

簡直相見恨晚

尤其把選單放進資料庫內

要抓出某子選單的所有上層選單

就靠這個來抓了!!!

 

ref:

https://mariadb.com/kb/en/library/non-recursive-common-table-expressions-overview/

https://mariadb.com/kb/en/library/recursive-common-table-expressions-overview/

看更多