使用邻接表模型在MySQL中管理分层数据
简介：在本教程中，您将学习如何使用邻接表模型在MySQL中管理分层数据。
邻接表模型简介
分层数据无处不在。它可以是博客类别，产品层次结构或组织结构。 在MySQL中有许多方法可以管理分层数据，而邻接表模型可能是最简单的解决方案。由于其简单性，邻接列表模型是开发人员和数据库管理员非常喜欢的选择。 在邻接列表模型中，每个节点都有一个指向其父节点的指针。顶层节点没有父节点。请参阅以下电子产品类别：
Electronics是顶级节点或根节点。Laptops, Cameras & photo, Phones & Accessories节点是Electronics节点的子代。反之亦然，电子节点Laptops, Cameras & photo, Phones & Accessories是节点的父节点。- 叶子节点是没有孩子如节点
Laptops，PC，Android，iOS，等，而非叶节点是具有至少一个子代。 - 节点的子孙称为子孙。节点的父母，祖父母等也称为祖先。
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;运行结果：
+----+-------------+ | id | title | +----+-------------+ | 1 | Electronics | +----+-------------+ 1 row in set (0.00 sec)
查找节点的直接子代
以下查询获取根节点的直接子代：SELECT id, title FROM category WHERE parent_id = 1;运行结果：
+----+----------------------+ | id | title | +----+----------------------+ | 2 | Laptops & PC | | 5 | Cameras & photo | | 7 | Phones & Accessories | +----+----------------------+ 3 rows in set (0.00 sec)
查找叶节点
叶节点是没有子节点的节点。SELECT c1.id, c1.title FROM category c1 LEFT JOIN category c2 ON c2.parent_id = c1.id WHERE c2.id IS NULL;运行结果：
+----+-------------------+ | id | title | +----+-------------------+ | 3 | Laptops | | 4 | PC | | 6 | Camera | | 9 | Android | | 10 | iOS | | 11 | Other Smartphones | | 12 | Batteries | | 13 | Headsets | | 14 | Screen Protectors | +----+-------------------+ 9 rows in set (0.00 sec)
完整查询整棵树
以下递归公用表表达式（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 | title | path | +------+----------------------+----------------------------------------------------------------------+ | 1 | Electronics | Electronics | | 5 | Cameras & photo | Electronics > Cameras & photo | | 6 | Camera | Electronics > Cameras & photo > Camera | | 2 | Laptops & PC | Electronics > Laptops & PC | | 3 | Laptops | Electronics > Laptops & PC > Laptops | | 4 | PC | Electronics > Laptops & PC > PC | | 7 | Phones & Accessories | Electronics > Phones & Accessories | | 12 | Batteries | Electronics > Phones & Accessories > Batteries | | 13 | Headsets | Electronics > Phones & Accessories > Headsets | | 14 | Screen Protectors | Electronics > Phones & Accessories > Screen Protectors | | 8 | Smartphones | Electronics > Phones & Accessories > Smartphones | | 9 | Android | Electronics > Phones & Accessories > Smartphones > Android | | 10 | iOS | Electronics > Phones & Accessories > Smartphones > iOS | | 11 | Other Smartphones | Electronics > Phones & Accessories > Smartphones > Other Smartphones | +------+----------------------+----------------------------------------------------------------------+ 14 rows in set (0.00 sec)
查询子树
下面的查询得到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;
运行结果：
+------+-------------------+---------------------------------+ | id | title | path | +------+-------------------+---------------------------------+ | 12 | Batteries | Batteries | | 13 | Headsets | Headsets | | 14 | Screen Protectors | Screen Protectors | | 8 | Smartphones | Smartphones | | 9 | Android | Smartphones > Android | | 10 | iOS | Smartphones > iOS | | 11 | Other Smartphones | Smartphones > Other Smartphones | +------+-------------------+---------------------------------+ 7 rows in set (0.00 sec)
查询单个路径
要查询从下到上的单个路径，例如从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;
运行结果：
+------+----------------------+-----------+ | id | title | parent_id | +------+----------------------+-----------+ | 10 | iOS | 8 | | 8 | Smartphones | 7 | | 7 | Phones & Accessories | 1 | | 1 | Electronics | NULL | +------+----------------------+-----------+ 4 rows in set (0.00 sec)
计算每个节点的级别
假设根节点的级别为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;
运行结果：
+------+----------------------+------+ | id | title | lvl | +------+----------------------+------+ | 1 | Electronics | 0 | | 2 | Laptops & PC | 1 | | 5 | Cameras & photo | 1 | | 7 | Phones & Accessories | 1 | | 3 | Laptops | 2 | | 4 | PC | 2 | | 6 | Camera | 2 | | 8 | Smartphones | 2 | | 12 | Batteries | 2 | | 13 | Headsets | 2 | | 14 | Screen Protectors | 2 | | 9 | Android | 3 | | 10 | iOS | 3 | | 11 | Other Smartphones | 3 | +------+----------------------+------+ 14 rows in set (0.00 sec)
删除节点及其后代
要删除节点及其后代，只需删除节点本身，所有后代将通过DELETE CASCADE外键约束的来自动删除。
例如，要删除Laptops & PC节点及其子女（Laptops，PC），可以使用如下语句：
DELETE FROM category WHERE id = 2;
删除节点并提升其后代
删除非叶子节点并提升其后代：- 首先，parent_id将节点的直接子节点的更新为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中的分层数据。
