全部教學 » MySQL鄰接列表模型和層次結構

MySQL鄰接列表模型和層次結構

2019-10-16 22:56:41

在本教學中，您將學習如何使用鄰接列表模型來管理MySQL中的分層資料。

鄰接列表模型介紹

分層資料無處不在。它可以是部落格類別(欄目)，產品層次結構或組織結構。

有很多方法來管理MySQL中的層次資料，鄰接列表模型可能是最簡單的解決方案。 由於其簡單性，鄰接列表模型是開發人員和資料庫管理員非常受歡迎的選擇。

在鄰接列表模型中，每個節點都有一個指向其父節點的指標。頂級節點沒有父節點。 請參閱以下類別的電子產品：

在使用鄰接列表模型之前，應該熟悉一些術語：

  • 電子裝置(Electronics)是頂級節點或根節點。
  • 膝上型電腦，相機和照片，手機和配件(Laptops, Cameras & photo, Phones & Accessories)節點是Electronics節點的子節點。反之亦然Electronics節點是Laptops, Cameras & photo, Phones & Accessories節點的父節點。
  • 葉子節點是沒有子節點的節點，例如Laptops，PC，Android，iOS等，而非葉節點是至少有一個子節點的節點。
  • 一個節點的子孫節點被稱為後代節點。一個節點的父節點，祖父節點等也被稱為祖先節點。

要對此類樹進行建模，我們可以建立一個名為category的表，其中包含三個列：id，title和parent_id，如下所示：

CREATE TABLE category (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  parent_id int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES category (id) 
    ON DELETE CASCADE ON UPDATE CASCADE
);

表中的每一行都是由id列標識的樹中的一個節點。 parent_id列是category表本身的外來鍵。它像一個指向id列的指標。

插入資料

樹的根節點沒有父節點，因此parent_id設定為NULL。其他節點必須只有一個父節點。

要插入根節點資料，請將parent_id設定為NULL，如下所示：

INSERT INTO category(title,parent_id) 
VALUES('Electronics',NULL);

要插入非根節點，只需要將其parent_id設定為其父節點的ID值。 例如，Laptop & PC和Cameras & Photos，以及Phone & Accessories節點的parent_id設定為1，參考以下語句：

INSERT INTO category(title,parent_id) 
VALUES('Laptops & PC',1);

INSERT INTO category(title,parent_id) 
VALUES('Laptops',2);
INSERT INTO category(title,parent_id) 
VALUES('PC',2);

INSERT INTO category(title,parent_id) 
VALUES('Cameras & photo',1);
INSERT INTO category(title,parent_id) 
VALUES('Camera',5);

INSERT INTO category(title,parent_id) 
VALUES('Phones & Accessories',1);
INSERT INTO category(title,parent_id) 
VALUES('Smartphones',7);

INSERT INTO category(title,parent_id) 
VALUES('Android',8);
INSERT INTO category(title,parent_id) 
VALUES('iOS',8);
INSERT INTO category(title,parent_id) 
VALUES('Other Smartphones',8);

INSERT INTO category(title,parent_id) 
VALUES('Batteries',7);
INSERT INTO category(title,parent_id) 
VALUES('Headsets',7);
INSERT INTO category(title,parent_id) 
VALUES('Screen Protectors',7);

查詢根節點

根節點是沒有父節點的節點。換句話說，它的parent_id為NULL：

SELECT
    id, title
FROM
    category
WHERE
    parent_id IS NULL;

查詢節點的直接子節點

以下查詢獲取根節點的直接子節點，參考以下查詢語句 - 

SELECT
    id, title
FROM
    category
WHERE
    parent_id = 1;

查詢葉節點

葉節點是沒有子節點的節點。

SELECT
    c1.id, c1.title
FROM
    category c1
        LEFT JOIN
    category c2 ON c2.parent_id = c1.id
WHERE
    c2.id IS NULL;

查詢整個樹

以下遞迴公用表表示式(CTE)檢索整個類別樹。 請注意，自從MySQL 8.0起，CTE功能已經可用了。

WITH RECURSIVE category_path (id, title, path) AS
(
  SELECT id, title, title as path
    FROM category
    WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title)
    FROM category_path AS cp JOIN category AS c
      ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;

查詢子樹

以下查詢獲取ID為7的Phone＆Accessories的子樹。

WITH RECURSIVE category_path (id, title, path) AS
(
  SELECT id, title, title as path
    FROM category
    WHERE parent_id = 7
  UNION ALL
  SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title)
    FROM category_path AS cp JOIN category AS c
      ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;

得到以下結果 -

查詢單個路徑

要查詢從下到上的單一路徑，例如從iOS到Electronics，請使用以下語句：

WITH RECURSIVE category_path (id, title, parent_id) AS
(
  SELECT id, title, parent_id
    FROM category
    WHERE id = 10 -- child node
  UNION ALL
  SELECT c.id, c.title, c.parent_id
    FROM category_path AS cp JOIN category AS c
      ON cp.parent_id = c.id
)
SELECT * FROM category_path;

計算每個節點的級別

假設根節點的級別為0，下面的每個節點都有一個等於其父節點的級別加1的級別。

WITH RECURSIVE category_path (id, title, lvl) AS
(
  SELECT id, title, 0 lvl
    FROM category
    WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.title,cp.lvl + 1
    FROM category_path AS cp JOIN category AS c
      ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY lvl;

如下所示 -

刪除節點及其後代

要刪除節點及其後代，只需刪除節點本身，則所有後代將被刪除的DELETE CASCADE自動刪除

例如，要Laptops & PC節點及其子節點(Laptops , PC)，請使用以下語句：

DELETE FROM category 
WHERE
    id = 2;

刪除節點並提升其後子節點

刪除非葉節點並提升其後子節點：

  • 首先，將節點的直接子節點的parent_id更新為新父節點的ID。
  • 然後，刪除節點。

例如，要刪除Smartphones節點並其子項，例如Android，iOS，Other Smartphones節點：

首先，更新Smartphones的所有直接子節點項的parent_id：

UPDATE category 
SET 
    parent_id = 7 -- Phones & Accessories
WHERE
    parent_id = 5; -- Smartphones

其次，刪除Smartphones節點：

DELETE FROM category 
WHERE
    id = 8;

兩個語句都應該包含在一個事務中：

BEGIN;

UPDATE category 
SET 
    parent_id = 7 
WHERE 
    parent_id = 5;

DELETE FROM category 
WHERE 
    id = 8;

COMMIT;

移動子樹

要移動子樹，只需更新子樹的頂級節點的parent_id。 例如，要移動Cameras & photo作為Phone and Accessories的子節點，可使用以下語句：

UPDATE category 
SET 
    parent_id = 7
WHERE
    id = 5;

在本教學中，您已經學會了如何使用鄰接列表模型來管理MySQL中的分層資料。