為分層資料建立遞歸 MySQL 查詢

Question

我有一個 MySQL 表，如下：

id	名稱	parent_id
19	類別1	0
20	類別2	19
21	類別3	20
22	類別4	21
...	...	...

現在，我想要一個MySQL 查詢，我只需要提供id [例如id=19] 然後我應該要取得它的所有子id [即結果應該有id '20,21,22 ']....

子層級的層次結構未知；它可能會有所不同...

我知道如何使用 for 迴圈來做到這一點...但是如何使用單一 MySQL 查詢來實現相同的目的？

Answer 1

對於MySQL 8 ：使用遞迴使用#語法。
對於 MySQL 5.x： 使用內聯變數、路徑 ID 或自連接。

MySQL 8

with recursive cte (id, name, parent_id) as (
  select     id,
             name,
             parent_id
  from       products
  where      parent_id = 19
  union all
  select     p.id,
             p.name,
             p.parent_id
  from       products p
  inner join cte
          on p.parent_id = cte.id
)
select * from cte;

parent_id = 19 中指定的值應設定為您要選擇其所有後代的父級的 id。

MySQL 5.x

對於不支援公共表格運算式的 MySQL 版本（最高版本 5.7），您可以使用下列查詢來實現此目的：

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

這是一個小提琴。

此處，@pv := '19' 中指定的值應設為您要選擇其所有後代的父級的 id。

如果父母有多個孩子，這也會起作用。但要求每筆記錄都滿足parent_id < id< id條件，否則結果不完整。

查詢內的變數賦值

此查詢使用特定的 MySQL 語法：在執行期間指派和修改變數。對執行順序做了一些假設：

• 首先評估 from 子句。這就是 @pv 初始化的地方。
• 依照從 from 別名檢索的順序對每筆記錄評估 where 子句。因此，這裡設定的條件僅包括父級已被識別為位於後代樹中的記錄（主要父級的所有後代都將逐步添加到 @pv）。
• 此 where 子句中的條件依序求值，一旦總結果確定，求值就會中斷。因此，第二個條件必須位於第二位，因為它將 id 添加到父列表中，並且只有在 id 通過第一個條件時才會發生這種情況。呼叫 length 函數只是為了確保此條件始終為真，即使 pv 字串由於某種原因會產生虛假值。

總而言之，人們可能會發現這些假設風險太大，無法依賴。 文檔警告：

因此，即使它與上述查詢一致，評估順序仍然可能會發生變化，例如，當您新增條件或將此查詢用作較大查詢中的檢視或子查詢時。這是一個將在未來的 MySQL 版本中刪除的「功能」 ：

如上所述，從 MySQL 8.0 開始，您應該使用遞歸 with 語法。

效率

對於非常大的資料集，此解決方案可能會變慢，因為find_in_set 操作不是在列表中查找數字的最理想方式，當然也不是在達到與傳回的記錄數。

替代方案 1：使用遞迴，連線

越來越多的資料庫實作SQL:1999 ISO 標準WITH [RECURSIVE]遞迴查詢的 語法（例如Postgres 8.4 、SQL Server 2005 、DB2、Oracle 11gR2 ，SQLite 3.8.4 ，Firebird 2.1 、H2 、HyperSQL 2.1.0 ，Teradata，MariaDB 10.2.2 ）。從 版本 8.0 開始，MySQL 也支援它。請參閱此答案的頂部以了解要使用的語法。

某些資料庫具有用於分層查找的替代非標準語法，例如CONNECT BY 子句/B19306_01 /server.102/b14200/queries003.htm" rel="noreferrer">Oracle，DB2，Informix、CUBRID 和其他資料庫。

MySQL 5.7 版不提供這樣的功能。當您的資料庫引擎提供此語法或您可以遷移到提供此語法的資料庫引擎時，那麼這無疑是最佳選擇。如果沒有，請考慮以下替代方案。

替代方案 2：路徑樣式標識符

如果您指派包含分層資訊（路徑）的 id 值，事情就會變得容易得多。例如，在您的情況下，這可能如下所示：

ID	姓名
19	類別1
19/1	類別2
1/19	類別3
19/1/1/1	類別4

然後您的選擇將如下所示：

select  id,
        name 
from    products
where   id like '19/%'

替代方案 3：重複自連線

如果您知道層次結構樹的深度上限，則可以使用標準 sql 查詢，如下所示：

select      p6.parent_id as parent6_id,
            p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id) 
order       by 1, 2, 3, 4, 5, 6, 7;

請參閱此小提琴

#where 條件指定您要擷取哪個父代的後代。您可以根據需要擴展此查詢更多級別。