mysql 单表层级查询 mysql 层次查询_mysql 单表层级查询


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

邻接列表模型介绍

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

有很多方法来管理MySQL中的层次数据,邻接列表模型可能是最简单的解决方案。 由于其简单性,邻接列表模型是开发人员和数据库管理员非常受欢迎的选择。

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


mysql 单表层级查询 mysql 层次查询_删除节点_02


在使用邻接列表模型之前,应该熟悉一些术语:

  • 电子设备(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, titleFROM categoryWHERE parent_id IS NULL;

查找节点的直接子节点

以下查询获取根节点的直接子节点,参考以下查询语句 -

SELECT id, titleFROM categoryWHERE parent_id = 1;

查找叶节点

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

SELECT c1.id, c1.titleFROM category c1 LEFT JOIN category c2 ON c2.parent_id = c1.idWHERE 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_pathORDER 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_pathORDER BY path;

得到以下结果 -


mysql 单表层级查询 mysql 层次查询_父节点_03


查询单个路径

要查询从下到上的单一路径,例如从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_pathORDER BY lvl;

如下所示 -


mysql 单表层级查询 mysql 层次查询_mysql cte递归查询父级id_04


删除节点及其后代

要删除节点及其后代,只需删除节点本身,则所有后代将被删除的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 & AccessoriesWHERE 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 = 7WHERE id = 5;

在本教程中,您已经学会了如何使用邻接列表模型来管理MySQL中的分层数据。