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

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

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

（當然，你也可以從 西服 開始算起，也就是 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 所提供的資料

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