Managing Hierarchical Data in MySQL Using the Adjacency List Model

摘要：在本教程中，您将学习如何使用邻接表模型来管理 MySQL 中的分层数据。

邻接表模型简介

分层数据无处不在。它可以是博客类别、产品层次结构或组织结构。

MySQL 中有多种管理分层数据的方法，邻接表模型可能是最简单的解决方案。由于其简单性，邻接列表模型是开发人员和数据库管理员非常流行的选择。

在邻接表模型中，每个节点都有一个指向其父节点的指针。顶部节点没有父节点。请参阅以下类别的电子产品：

在使用邻接表模型之前，您应该熟悉一些术语：

• Electronics是顶部节点或根节点。
• Laptops, Cameras & photo, Phones & Accessories节点是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
);
Code language: SQL (Structured Query Language) (sql)

表中的每一行都是树中的一个节点，由id列标识。 parent_id列是category表本身的外键。它的作用就像一个指向id列的指针。

插入数据

树的根节点没有父节点，因此， parent_id设置为NULL 。其他节点必须有且只有一个父节点。

要插入根节点，请将parent_id设置为NULL ，如下所示：

INSERT INTO category(title,parent_id) 
VALUES('Electronics',NULL);
Code language: SQL (Structured Query Language) (sql)

要插入非根节点，只需将其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);
Code language: SQL (Structured Query Language) (sql)

寻找根节点

根节点是没有父节点的节点。换句话说，它的parent_id是NULL ：

SELECT
    id, title
FROM
    category
WHERE
    parent_id IS NULL;
Code language: SQL (Structured Query Language) (sql)

查找节点的直接子节点

以下查询获取根节点的直接子节点：

SELECT
    id, title
FROM
    category
WHERE
    parent_id = 1;
Code language: SQL (Structured Query Language) (sql)

寻找叶节点

叶节点是没有子节点的节点。

SELECT
    c1.id, c1.title
FROM
    category c1
        LEFT JOIN
    category c2 ON c2.parent_id = c1.id
WHERE
    c2.id IS NULL;
Code language: SQL (Structured Query Language) (sql)

查询整棵树

以下递归公用表表达式 (CTE)检索整个类别树。请注意， CTE功能从 MySQL 8.0 开始可用

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;
Code language: SQL (Structured Query Language) (sql)

查询子树

以下查询获取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;
Code language: SQL (Structured Query Language) (sql)

查询单个路径

要查询从下到上的单个路径，例如从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;
Code language: SQL (Structured Query Language) (sql)

计算每个节点的level

假设根节点的级别为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;
Code language: SQL (Structured Query Language) (sql)

删除节点及其后代

要删除一个节点及其后代，只需删除该节点本身，所有后代将通过外键约束的DELETE CASCADE自动删除。

例如，要删除Laptops & PC节点及其子节点（ Laptops 、 PC ），请使用以下语句：

DELETE FROM category 
WHERE
    id = 2;
Code language: SQL (Structured Query Language) (sql)

删除节点并提升其后代

删除非叶节点并提升其后代：

1. 首先，将节点的直接子节点的parent_id更新为新父节点的id 。
2. 然后，删除该节点。

例如，要删除Smartphones节点并提升其子节点，例如Android 、 iOS 、 Other Smartphones节点：

首先，更新Smartphones的所有直接子级的parent_id ：

UPDATE category 
SET 
    parent_id = 7 -- Phones & Accessories
WHERE
    parent_id = 5; -- Smartphones
Code language: SQL (Structured Query Language) (sql)

其次，删除Smartphones节点：

DELETE FROM category 
WHERE
    id = 8;
Code language: SQL (Structured Query Language) (sql)

两个语句应该包含在一个事务中：

BEGIN;

UPDATE category 
SET 
    parent_id = 7 
WHERE 
    parent_id = 5;

DELETE FROM category 
WHERE 
    id = 8;

COMMIT;
Code language: SQL (Structured Query Language) (sql)

移动子树

要移动子树，只需更新子树顶部节点的parent_id即可。例如，要将Cameras & photo移动为Phone and Accessories的子项，请使用以下语句：

UPDATE category 
SET 
    parent_id = 7
WHERE
    id = 5;
Code language: SQL (Structured Query Language) (sql)

在本教程中，您学习了如何使用邻接表模型来管理 MySQL 中的分层数据。