mysql遞歸查詢(xún)語(yǔ)句

mysql遞歸查詢(xún)，mysql中從子類(lèi)ID查詢(xún)所有父類(lèi)（做無(wú)限分類(lèi)經(jīng)常用到）

天祝藏族自治網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)建站！從網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、APP開(kāi)發(fā)、響應(yīng)式網(wǎng)站設(shè)計(jì)等網(wǎng)站項(xiàng)目制作，到程序開(kāi)發(fā)，運(yùn)營(yíng)維護(hù)。創(chuàng)新互聯(lián)建站從2013年成立到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來(lái)保證我們的工作的順利進(jìn)行。專(zhuān)注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)建站。

由于mysql 不支持類(lèi)似 oracle with ...connect的 遞歸查詢(xún)語(yǔ)法

之前一直以為類(lèi)似的查詢(xún)要么用存儲(chǔ)過(guò)程要么只能用程序?qū)戇f歸查詢(xún).

現(xiàn)在發(fā)現(xiàn)原來(lái)一條sql語(yǔ)句也是可以搞定的

先來(lái)看數(shù)據(jù)表的結(jié)構(gòu)如下：

id name parent_id

---------------------------

1 Home 0

2 About 1

3 Contact 1

4 Legal 2

5 Privacy 4

6 Products 1

7 Support 1

我要的要求是根據(jù)一個(gè)分類(lèi)ID（這個(gè)分類(lèi)ID可能是一個(gè)子分類(lèi)），得到所有的父分類(lèi)，下面是相應(yīng)的SQL：

SELECT T2.id, T2.name

FROM (

SELECT

@r AS _id,

(SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,

@l := @l + 1 AS lvl

FROM

(SELECT @r := 5, @l := 0) vars,

table1 h

WHERE @r 0) T1

JOIN table1 T2

ON T1._id = T2.id

ORDER BY T1.lvl DESC

代碼@r := 5標(biāo)示查詢(xún)id為5的所有父類(lèi)。結(jié)果如下

1, ‘Home’

2, ‘About’

4, ‘Legal’

5, ‘Privacy’

自己仿照這看一下！

Mysql8.0遞歸函數(shù)

一般比較普遍的就是四種方法：（具體見(jiàn) SQL Anti-patterns這本書(shū)）

因?yàn)椴恢С诌f歸查詢(xún)，所以Mysql官方當(dāng)時(shí)推薦的是第三種方式：Nested Sets。我是用過(guò)的，非常難受?，F(xiàn)在支持遞歸查詢(xún)，我們可以使用第一種。

其實(shí)我還用了一種，一列維持順序，一列維持層級(jí)，有空可以把實(shí)現(xiàn)寫(xiě)出來(lái)看看。

英文好的朋友請(qǐng)移步到這里： Managing Hierarchical Data in MySQL Using the Adjacency List Model ，不好的同學(xué)跟我一起來(lái)。

直接語(yǔ)句說(shuō)明：

好，就這些吧。我是姜友華，下次見(jiàn)。

mysql中的遞歸調(diào)用

首先創(chuàng)建一個(gè)熟悉的機(jī)構(gòu)表

插入幾條測(cè)試數(shù)據(jù)：

union all上面的是初始化語(yǔ)句，只會(huì)執(zhí)行一次，查到了 開(kāi)發(fā)部 這一行記錄。

接下來(lái)下面的join會(huì)用初始化的語(yǔ)句去原來(lái)的organization表去join獲取所有 開(kāi)發(fā)部的子部門(mén) ，然后再用這些 子部門(mén) 去join更下面的部門(mén)。

執(zhí)行的結(jié)果如下：

如下想查詢(xún)開(kāi)發(fā)部的所有上級(jí)部門(mén)的話(huà)上面的遞歸查詢(xún)語(yǔ)句簡(jiǎn)單改一下就可以了：

執(zhí)行結(jié)果如下：

Recursive Common Table Expression 'temp' can contain neither

aggregation nor window functions in recursive query block

mysql

mysql對(duì)遞歸的深度是有限制的，默認(rèn)的遞歸深度是1000。

可以通過(guò) show variables like 'cte_max_recursion_depth'; 進(jìn)行查看

也可以通過(guò)select語(yǔ)句最大執(zhí)行時(shí)間對(duì)遞歸加以顯示， show variables lile 'max_execution_time';