MySQL多层级结构-树搜索介绍_Mysql

基本上在每个系统中都有那么几张表是自关联父子关系的结构。往往有很多人都是使用pid来做关联。在刚进入IT行业时使用CAKEPHP框架编写WEB的时候，使用它里面的一个ACL plugin实现权限管理的时候。发现一个表结构硬是不明白是怎么回事。具体表结构如下：

CREATE TABLE acos (
 id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 parent_id INTEGER(10) DEFAULT NULL,
 model VARCHAR(255) DEFAULT '',
 foreign_key INTEGER(10) UNSIGNED DEFAULT NULL,
 alias VARCHAR(255) DEFAULT '',
 lft INTEGER(10) DEFAULT NULL,
 rght INTEGER(10) DEFAULT NULL,
 PRIMARY KEY (id)
);

我们可以看到上面 acos 表用有lft、rght这两个字段。起初我根本就不明白这两个是做什么用的，几次直接修改数据导致数据错乱。

1.2. 原理解释

其实这就是树的后续遍历的每个节点的左值、右值。如下图表示:

1.3. 树的使用（引用上图树结构）

构造数据

DROP TABLE IF EXISTS comment;
CREATE TABLE `comment` (
 `comment_id` int(11) DEFAULT NULL,
 `left_num` int(11) DEFAULT NULL,
 `right_num` int(11) DEFAULT NULL
);

INSERT INTO `comment` VALUES
 (1,1,14),
 (2,2,5),
 (3,3,4),
 (4,6,13),
 (5,7,8),
 (6,9,12),
 (7,10,11);

CREATE INDEX idx$comment$left_num$right_num ON `comment` (`left_num`, `right_num`);

查找 '节点4' 的所有子节点

思路:我们只要查找出 节点左值在 '节点4' 左值和右值之间的节点
通俗说法:能被 '节点4' 包住的节点,通过左节点和右节点来判断是否被 '节点4' 包住。

-- 获得 '节点4' 孩子
SELECT c.*
FROM comment AS p, comment AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num
 AND p.comment_id = 4;
+------------+----------+-----------+
| comment_id | left_num | right_num |
+------------+----------+-----------+
|     4 |    6 |    13 |
|     5 |    7 |     8 |
|     6 |    9 |    12 |
|     7 |    10 |    11 |
+------------+----------+-----------+

查找 '节点6' 的所有父节点
思路: 找出 左值小于 '节点6' 并且 右值大于 '节点6' 的节点。
通俗说法: 找出那个节点能将 '节点6' 给包住。

-- 获得 '节点6' 父亲
SELECT p.*
FROM comment AS p, comment AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num
 AND c.comment_id = 6;
+------------+----------+-----------+
| comment_id | left_num | right_num |
+------------+----------+-----------+
|     1 |    1 |    14 |
|     4 |    6 |    13 |
|     6 |    9 |    12 |
+------------+----------+-----------+

计算 '节点4' 的深度
如果是MySQL5.7 需要修改sql_mode

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SELECT c.*,
 COUNT(c.comment_id) AS depth
FROM comment AS p, comment AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num
 AND c.comment_id = 4
GROUP BY c.comment_id;
+------------+----------+-----------+-------+
| comment_id | left_num | right_num | depth |
+------------+----------+-----------+-------+
|     4 |    6 |    13 |   2 |
+------------+----------+-----------+-------+

获取 '节点4' 的所有子节点, 和相关深度

SELECT sub_child.*,
 (COUNT(sub_parent.comment_id) - 1) AS depth
FROM (
 SELECT child.*
 FROM comment AS parent, comment AS child
 WHERE child.left_num BETWEEN parent.left_num AND parent.right_num
  AND parent.comment_id = 4
) AS sub_child, (
 SELECT child.*
 FROM comment AS parent, comment AS child
 WHERE child.left_num BETWEEN parent.left_num AND parent.right_num
  AND parent.comment_id = 4
) AS sub_parent
WHERE sub_child.left_num BETWEEN sub_parent.left_num AND sub_parent.right_num
GROUP BY sub_child.comment_id
ORDER BY sub_child.left_num;
+------------+----------+-----------+-------+
| comment_id | left_num | right_num | depth |
+------------+----------+-----------+-------+
|     4 |    6 |    13 |   0 |
|     5 |    7 |     8 |   1 |
|     6 |    9 |    12 |   1 |
|     7 |    10 |    11 |   2 |
+------------+----------+-----------+-------+

插入数据
数据的插入是一件相当麻烦的事，需要更新节点的所有父节点的右值和和所有孩子节点的 '左值、右值'
如上图，如果我们想为 '节点4' 添加一个孩子 '节点44'(为了不给自己挖坑，我们将添加的孩子放在父节点的最左边)，就是将 '节点44' 放在 '节点5' 的左边。如下图：

最终我们获得的结果，如下图：

上图 '紫色' 的是节点需要变更的左值和右值，'绿色' 的是新增节点的值。
更新思路：
1、将左值大于 '节点4' 的左值的节点的左值 加2。
2、将右值大于 '节点4' 的左值的节点的右值 加2。

-- 获得 '节点4' 和 '节点4'的第一个孩子的(节点5)的左右值
SELECT c.*
FROM comment AS p, comment AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num
 AND p.comment_id = 4;
+------------+----------+-----------+
| comment_id | left_num | right_num |
+------------+----------+-----------+
|     4 |    6 |    13 |
|     5 |    7 |     8 |
... omit ...
-- 通过上面获得的信息更新 '节点4' 的父子几点的左右值
UPDATE comment SET left_num = left_num + 2 WHERE left_num > 6;
UPDATE comment SET right_num = right_num + 2 WHERE right_num > 6;

插入思路
1、将 '节点44' 的左值设置为 '节点4' 的左值 加1
2、将 '节点44' 的右值设置为 '节点4' 的左值 加2

INSERT INTO comment
SELECT 44, left_num + 1, left_num + 2
FROM comment WHERE comment_id = 4;

验证

-- 获得 '节点4' 孩子
SELECT c.*
FROM comment AS p, comment AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num
 AND p.comment_id = 4;
+------------+----------+-----------+
| comment_id | left_num | right_num |
+------------+----------+-----------+
|     4 |    6 |    15 |
|     5 |    9 |    10 |
|     6 |    11 |    14 |
|     7 |    12 |    13 |
|     44 |    7 |     8 |
+------------+----------+-----------+
-- 获得 '节点44' 父亲
SELECT p.*
FROM comment AS p, comment AS c
WHERE c.left_num BETWEEN p.left_num AND p.right_num
 AND c.comment_id = 44;
+------------+----------+-----------+
| comment_id | left_num | right_num |
+------------+----------+-----------+
|     1 |    1 |    16 |
|     4 |    6 |    15 |
|     44 |    7 |     8 |
+------------+----------+-----------+

1.4. 总结

这种树结构一般会用在查询多增加修改少的场景中（比如地区表，类别表之类的）。
在现实中其实还有些表的数据字段很多，并且具有层级关系。但是他们层级关系并不需要实时的那么准确(最终能达到数据数据一直就行)，这是我们会将这种层级关系的字段和主表分开放在另外一个表。这样为了加快更新。如果实时更新影响到了性能，这是我们会考虑使用kafka(我们还没有发现性能很差)。

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索mysql
树搜索
mysql 多层嵌套查询、mysql 多层子查询、mysql 多层目录、mysql的多层判断、多层框架结构，以便于您获取更多的相关知识。

时间： 2024-08-07 06:52:56

MySQL多层级结构-树搜索介绍_Mysql的相关文章

mysql中复制表结构的方法小结_Mysql

mysql中用命令行复制表结构的方法主要有一下几种:  1.只复制表结构到新表 CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2 或者 CREATE TABLE 新表 LIKE 旧表 2.复制表结构及数据到新表 CREATE TABLE 新表 SELECT * FROM 旧表 3.复制旧表的数据到新表(假设两个表结构一样)  INSERT INTO 新表 SELECT * FROM 旧表 4.复制旧表的数据到新表(假设两个表结构不一样) INSERT INTO

MySQL多层级结构-区域表使用树详解_Mysql

1.1. 前言 前面我们大概介绍了一下树结构表的基本使用.在我们项目中有好几块有用到多层级的概念.下面我们哪大家都比较熟悉的区域表来做演示.1.2. 表结构和数据 区域表基本结构,可能在你的项目中还有包含其他字段.这边我只展示我们关心的字段: CREATE TABLE `area` ( `area_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '地区ID', `name` varchar(40) NOT NULL DEFAULT 'unkonw' COM

Mysql 索引结构直观图解介绍_Mysql

一.模拟创建原始数据 下图中,左边是自己方便说明,模拟的数据.引擎为mysiam~ 右边是用EXCEL把它们随机排列后的一个正常仿真数据表,把主键按照1-27再排列(不随机的话我在模拟数据时本来就是按顺序写的,再加索引看不大出这个索引排序的过程) 也就是说右边的数据,使我们要测试的原始数据,没建索引前是这样排序的,后边所有的数据都是以这个为依准进行的,这样更好看索引生成后的排序效果. 该表有4个字段(id,a,b,c),共21行数据 二.创建索引 a 如下图,当创建索引a以后,在该索引结构中,从

mysql之innodb的锁分类介绍_Mysql

一.innodb行锁分类 record lock:记录锁,也就是仅仅锁着单独的一行 gap lock:区间锁,仅仅锁住一个区间(注意这里的区间都是开区间,也就是不包括边界值. next-key lock:record lock+gap lock,所以next-key lock也就半开半闭区间,且是下界开,上界闭. www.jb51.net next-key 锁定范围:(负无穷大,最小第一记录],(记录之间],(最大记录,正无穷大) 二.语句锁定情况分析 SELECT ... FROM ... F

MySQL安全输入密码的一些操作介绍_Mysql

当我们运行mysql客户端连接mysql服务器的时候,以一种暴露的可被其他用户发现的方式指定我们的密码是不妥的.我们输入密码的方式有四种,其中每一种都有一定风险.下面就分别说下这四种方式: MySQL密码输入的安全操作(翻译) 第一种:直接在命令行后使用  -pyour_pass 或者r --password=your_pass 这两种选项. 举例: shell> mysql -u ksharpdabu -pksharppassword db_name 这种方式很方便,但是不安全,因为在某些系统

Mysql日志文件和日志类型介绍_Mysql

日志文件类型 MySQL有几个不同的日志文件,可以帮助你找出mysqld内部发生的事情: 日志文件 记入文件中的信息类型 错误日志 记录启动.运行或停止mysqld时出现的问题. 查询日志 记录建立的客户端连接和执行的语句. 更新日志 记录更改数据的语句.不赞成使用该日志. 二进制日志 记录所有更改数据的语句.还用于复制. 慢日志 记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询. 默认情况下,所有日志创建于mysqld数据目录中.通过刷新日志,你可以强制 mys

Mysql之EXPLAIN显示using filesort介绍_Mysql

语法格式如下 EXPLAIN tbl_name 或者: EXPLAIN SELECT select_options EXPLAIN 语句可以被当作 DESCRIBE 的同义词来用,也可以用来获取一个MySQL要执行的 SELECT 语句的相关信息. EXPLAIN tbl_name 语法和 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 一样. 当在一个 SELECT 语句前使用关键字 EXPLAIN 时,MYSQL会解释了即将如何运行该 SELEC

MySQL笔记之子查询使用介绍_Mysql

子查询是将一个查询语句嵌套在另一个查询语句中 内层查询语句的查询结果,可以为外层查询语句提供查询条件 因为在特定情况下,一个查询语句的条件需要另一个查询语句来获取 参考表:employee   参考表:department 带IN关键字的子查询 复制代码 代码如下: mysql> SELECT * FROM employee    -> WHERE d_id IN    -> (SELECT d_id FROM department);+------+------+--------+--

mysql 初始执行文件的使用介绍_Mysql

可以在配置文件里指定mysql启动以后初始执行的SQL文件, 其语法是: 在[mysqld]或者[server]下指定: init-file=D:\mysql-5.5.28-winx64\abc.sql, 后边为具体的sql文件值 注意下边两点就行了: 1. 确保你的mysqld 编译的时候没有加 --disable-grant-options 开关. 2. 确保init-file指定的脚本每行是一个具体的可以执行的语句. 为了示例: abc.sql为: 复制代码 代码如下: use test;