MySQL – 取得無限上層結構資料 Parent tree

從去年開始,就碰到要抓取上層分類的問題,以前就用很土砲的方式,先取得現在層次編號(ID),再去一層一層拿父層編號(parent_id),但通常這對效能上來說非常不優,所以在這幾天就把上層分類樹的語法拼出來了,以下是MySQL資料結構

categories

 

假設今天要取得 衣裝服飾 > 男裝 > 西服 > 週邊配件

(當然,你也可以從 西服 開始算起,也就是 ID 帶入 26 即可取得 26 > 21 > 2)

這裡只需要取得現在最底層的編號,例如ID=28

就可以取得上層所有節點 28 > 26 > 21 > 2

也就是可以使用很節省資源的方式取得上層結構樹

以下是基本語法

 

資料表 `categories`
主比對資料欄位 `ID`, `parent_id`
額外取出欄位 `name`

首先,第一行 SELECT 就是最後取得的資料欄位 `ID`, `parent_id` 與 `name`
通常寫SQL的人都知道要習慣從FROM開始
如果從最裡層的FROM來看
他是先宣告兩個變數 @r 與 @i,順便取得主資料表做基本資料關鍵
取主資料表資料由 @r <> 0 開始取得

同時,取得資料時的資料欄位由 @r = `parent_id` 開始
也是從主資料表取得
當取得資料時,先將變數 @r 另外命名為 `_id`,而這時 @r 為 `parent_id`
條件為 `ID`(主資料表ID) = `_id`(變數@r現在值)
則取出來的資料為 `_id`(變數@r現在值) 與 `parent_id`(透過 ID=_id 取出的parent_id)

由此可知,從上面的條件取出來的資料,分別是
28 | 26
26 | 21
21 | 2
2 | 0
因為每次取出資料後的@r都會變成parent_id
所以最後當 @r = 0(也就是parent_id=0) 時會停止取得資料

然而,最後使用 JOIN 的方式再取出一次資料表作比對
這時就比較簡單
直接使用你要取出的資料ID,做為你變數@r傳進去的ID比對
就可以取出由 @r(傳入的ID) 所關聯的其他欄位資料

SELECT  CAST(`_id` AS UNSIGNED) AS `ID`, `q`.`parent_id`, `r`.`name`
FROM (SELECT @r AS `_id`, (SELECT @r := `parent_id`
						   FROM `categories`
						   WHERE `ID` = `_id`) AS `parent_id`
	  FROM (SELECT  @r := '28', @l := 0) AS `vars`, `categories`
	  WHERE @r <> 0 ) AS `q`
LEFT JOIN `categories` AS `r`
ON `r`.`ID` = `q`.`_id`

另外,很感謝 Hierarchical queries in MySQL 所提供的資料

如果有其他詳細問題,可以參考這個網頁

相連文章

臉書留言

一般留言

  1. 雖然看不太懂,但是實際測試後真的是威力強大!!
    竟然一次查詢就可以把所有節點查出,厲害啊!
    唉~要好好練功了….

    感謝!!

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。

這個網站採用 Google reCAPTCHA 保護機制,這項服務遵循 Google 隱私權政策服務條款