SQL分析——使用邻接表模型在MySQL中管理分层数据

邻接表模型简介

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

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

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

为了模拟这一类的树，我们可以创建一个命名的表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列的指针 。

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

要插入非根节点，只需将其设置 parent_id为其父节点的ID。例如，所述parent_id的Laptop & PC，Cameras & Photos和Phone & Accessories节点被设置为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; 

运行结果：

查询子树

下面的查询得到Phone & Accessories的子树，其id为7。

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; 

删除节点并提升其后代

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

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

例如，要删除Smartphones节点并提升其子节点（例如Android，）iOS，请执行以下操作Other Smartphones：

首先，更新的parent_id所有直属子项Smartphones：

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;