如何查詢mysql所有子節點

一、什麼是子節點

1、在數據庫中，一般我們會採用樹形結構來存儲數據，每個節點都有若干個子節點；

2、子節點指的是在樹結構中，當前節點的下一級節點。

二、如何查詢一個節點的所有子節點

在mysql中，我們可以通過自關聯的方式來查詢一個節點的所有子節點。比如，我們有一張表叫做”category”，它的字段如下：

| id | name       | parent_id |
|----|------------|-----------|
| 1  | 電器       | 0         |
| 2  | 手機       | 1         |
| 3  | 平板電腦   | 1         |
| 4  | 遊戲機     | 1         |
| 5  | 華為       | 2         |
| 6  | 小米       | 2         |
| 7  | iPad      | 3         |
| 8  | Switch    | 4         |
| 9  | PS5       | 4         |
| 10 | Xbox Series X | 4     |

如果要查詢電器這個節點的所有子節點，可以使用如下的SQL語句：

WITH RECURSIVE cte AS (
  SELECT id, name, parent_id
  FROM category
  WHERE id = 1
  UNION ALL
  SELECT child.id, child.name, child.parent_id
  FROM category child
  JOIN cte parent ON parent.id = child.parent_id
)
SELECT * FROM cte;

上述SQL語句中，WITH RECURSIVE關鍵字表示我們要使用遞歸查詢，cte是一個遞歸查詢的臨時表，我們先定義了一個初始節點，然後不斷地與子節點進行連接操作，直到查詢出所有的子節點。

三、如何查詢多個節點的所有子節點

如果要查詢多個節點的所有子節點，可以使用IN關鍵字。比如，我們要查詢電器、手機和遊戲機這三個節點的所有子節點，可以使用如下的SQL語句：

WITH RECURSIVE cte AS (
  SELECT id, name, parent_id
  FROM category
  WHERE id IN (1,2,4)
  UNION ALL
  SELECT child.id, child.name, child.parent_id
  FROM category child
  JOIN cte parent ON parent.id = child.parent_id
)
SELECT * FROM cte;

四、如何查詢所有節點的子節點

如果要查詢所有節點的子節點，可以不指定初始節點。比如，我們要查詢所有電器類別的子節點，可以使用如下的SQL語句：

WITH RECURSIVE cte AS (
  SELECT id, name, parent_id
  FROM category
  WHERE parent_id = 1
  UNION ALL
  SELECT child.id, child.name, child.parent_id
  FROM category child
  JOIN cte parent ON parent.id = child.parent_id
)
SELECT * FROM cte;

上述SQL語句中，我們指定初始節點的parent_id為1，表示要查詢所有電器類別的子節點。

五、如何查詢所有節點及其子節點的深度

如果在查詢子節點時，希望能夠同時將每個子節點的深度一起查詢出來，可以使用如下的SQL語句：

WITH RECURSIVE cte(id, name, parent_id, depth) AS (
  SELECT id, name, parent_id, 1
  FROM category
  WHERE parent_id = 1
  UNION ALL
  SELECT child.id, child.name, child.parent_id, parent.depth + 1
  FROM category child
  JOIN cte parent ON parent.id = child.parent_id
)
SELECT * FROM cte;

上述SQL語句中，我們在cte表中增加了一個depth字段，表示每個節點的深度，初始節點的深度為1，每個子節點的深度都是其父節點的深度+1。

六、如何查詢所有節點及其子節點的葉節點

如果在查詢子節點時，希望將每個節點的葉節點一起查詢出來，可以使用如下的SQL語句：

WITH RECURSIVE cte(id, name, parent_id, path) AS (
  SELECT id, name, parent_id, CAST(name as char(200))
  FROM category
  WHERE parent_id = 1
  UNION ALL
  SELECT child.id, child.name, child.parent_id, CONCAT(parent.path, ' >> ', child.name)
  FROM category child
  JOIN cte parent ON parent.id = child.parent_id
)
SELECT id, name, path
FROM cte
WHERE NOT EXISTS (
  SELECT 1 FROM category child WHERE child.parent_id = cte.id
);

上述SQL語句中，我們在cte表中增加了一個path字段，表示每個節點到根節點的路徑，通過在path中查找”>>”字符串，可以得到每個節點的深度。同時在篩選葉節點時，使用NOT EXISTS子查詢來排除含有子節點的節點。

七、總結

本文介紹了如何在mysql中查詢所有子節點，包括如何查詢一個、多個或者所有節點的子節點，以及如何查詢每個節點的深度和葉節點。這些查詢方法可以為我們在實際項目中處理樹形結構的數據提供幫助。