MySQL 入门教程
  • 教程目录
  • 第一章 基础教程
    • 第一节 入门
      • 1.1 安装
      • 1.2 示例数据库
    • 第二节 查询数据
      • 2.1 SELECT 语句
      • 2.2 SELECT DISTINCT 语句
    • 第三节 过滤数据
      • 3.1 WHERE 子句
      • 3.2 AND 运算符
      • 3.3 OR 运算符
      • 3.4 IN 运算符
      • 3.5 BETWEEN 运算符
      • 3.6 LIKE 运算符
      • 3.7 LIMIT 子句
      • 3.8 IS NULL 运算符
    • 第四节 数据排序
      • 4.1 ORDER BY 子句
      • 4.1 自然排序
    • 第五节 连接表
      • 5.1 别名
      • 5.2 INNER JOIN
      • 5.3 LEFT JOIN
      • 5.4 自连接
      • 5.5 CROSS JOIN
    • 第六节 分组数据
      • 6.1 GROUP BY子句
      • 6.2 HAVING 子句
    • 第七节 子查询,派生表和通用表达式
      • 7.1 子查询
      • 7.2 派生表
      • 7.3 公共表表达式
      • 7.4 递归 CTE
    • 第八节 集合操作符
      • 8.1 UNION 和 UNION ALL
      • 8.2 INTERSECT 模拟
    • 第九节 修改数据
      • 9.1 INSERT 语句
      • 9.2 INSERT IGNORE
      • 9.3 UPDATE 语句
      • 9.4 UPDATE JOIN 语句
      • 9.5 DELETE
      • 9.6 ON DELETE CASCADE.
      • 9.7 DELETE JOIN
      • 9.8 REPLACE 语句
      • 9.9 PREPARE 语句
    • 第十节 事务
      • 10.1 事务介绍
      • 10.2 表锁定
    • 第十一节 管理数据库和表
      • 11.1 数据库管理
      • 11.2 MySQL 表类型
      • 11.3 CREATE TABLE
      • 11.4 序列
      • 11.5 ALTER TABLE
      • 11.6 重命名表
      • 11.7 从表中删除列
      • 11.8 向表中添加新列
      • 11.9 删除表
      • 11.10 临时表
      • 11.11 TRUNCATE TABLE
    • 第十二节 索引
      • 12.1 管理索引
      • 12.1 UNIQUE 索引
    • 第十三节 数据类型
      • 13.1 INT
      • 13.2 DECIMAL
      • 13.3 BIT
      • 13.4 BOOLEAN
      • 13.5 CHAR
      • 13.6 VARCHAR
      • 13.7 TEXT
      • 13.8 DATE
      • 13.9 TIME
      • 13.10 DATETIME
      • 13.11 TIMESTAMP
      • 13.12 JSON
      • 13.13 ENUM
    • 第十四节 约束
      • 14.1 NOT NULL 约束
      • 14.2 主键约束
      • 14.2 外键约束
      • 14.4 UNIQUE 约束
      • 14.5 CHECK 约束
    • 第十五节 全球化
      • 15.1 字符集
      • 15.2 校对规则
    • 第十六节 导入和导出
      • 16.1 导入 CSV 文件
      • 16.2 导出为 CSV
  • 第二章 技巧
    • CTE 简介
    • 递归 CTE
    • 邻接列表模型和层次结构
    • 获取行数
    • 比较表
    • 找重复值
    • 删除重复行
    • UUID 和主键
    • 复制表数据
    • 变量
    • 生成列
    • 连续行比较
    • 更改存储引擎
    • 基于正则表达式的搜索
    • row_number 模拟
    • 随机选择记录
    • 选择第 n 个最高纪录
    • 重置自动增量值
    • MariaDB 与 MySQL 比较
    • 间隔
    • NULL 详细和应用
    • 获取今天的日期
    • 将NULL值映射到有意义的值
    • 注释
  • 第三章 存储过程
    • 简介
    • 入门
    • 变量
    • 参数
    • 返回多个值
    • IF语句
    • CASE语句
    • IF和CASE语句的技巧
    • 循环
    • 游标
    • 列出存储过程
    • 错误处理
    • SIGNAL 和 ESIGNAL 语句
    • 存储函数
  • 第四章 触发器
    • 实现
    • 创建
    • 创建多个触发器
    • 管理
    • 计划事件
    • 修改事件
  • 第五章 视图
    • 简介
    • 实现和限制
    • 创建
    • 可更新视图
    • 确保视图一致性
    • 检查选项子句
    • 管理
  • 第六章 全文搜索
    • 简介
    • 定义索引
    • 自然语言全文搜索
    • 布尔全文搜索
    • 查询扩展
    • ngram全文解析器
  • 第七章 函数
  • 第八章 管理
    • 访问控制系统入门
    • 创建用户
    • 授予权限
    • 撤销权限
    • 角色管理
    • 删除用户
    • 维护数据库表
    • 备份数据库
    • 列出数据库
    • 列出表
    • 列出表的列
    • 列出用户
Powered by GitBook
On this page

Was this helpful?

  1. 第二章 技巧

邻接列表模型和层次结构

Previous递归 CTENext获取行数

Last updated 5 years ago

Was this helpful?

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

邻接列表模型介绍

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

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

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

img

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

  • 电子设备(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列的指针。

插入数据

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;

查询整个树

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;

如下所示 -

删除节点及其后代

例如,要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;

移动子树

UPDATE category 
SET 
    parent_id = 7
WHERE
    id = 5;

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

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

要根节点数据,请将parent_id设置为NULL,如下所示:

以下(CTE)检索整个类别树。 请注意,自从MySQL 8.0起,功能已经可用了。

img
img

要节点及其后代,只需删除节点本身,则所有后代将被删除的DELETE CASCADE自动删除

要移动子树,只需子树的顶级节点的parent_id。 例如,要移动Cameras & photo作为Phone and Accessories的子节点,可使用以下语句:

NULL
插入
递归公用表表达式
CTE
删除
更新