MySQL嵌套集合模型实现方法及省份城市示例

介绍

什么是分层数据？

类似于树形结构，除了根节点和叶子节点外，所有节点都有用一个父节点和多个子节点。

那么，在MySQL中如何处理分层数据呢？

原文中介绍了两种分层结构模型：邻接表模型和嵌套集合模型。
邻接表模型(The Adjacency List Model)

首先，建立测试表，导入测试数据，

CREATE TABLE category(
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);
INSERT INTO category VALUES
        (1,'ELECTRONICS',NULL),
        (2,'TELEVISIONS',1),
        (3,'TUBE',2),
        (4,'LCD',2),
        (5,'PLASMA',2),
        (6,'PORTABLE ELECTRONICS',1),
        (7,'MP3 PLAYERS',6),
        (8,'FLASH',7),
        (9,'CD PLAYERS',6),
        (10,'2 WAY RADIOS',6);
SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)

在邻接表中，所有的数据均拥有一个Parent字段，用来存储它的父节点。当前节点为根节点的话，它的父节点则为NULL。
那么在遍历的时候，可以使用递归来实现查询整棵树，从根节点开始，不断寻找子节点（父节点->子节点->父节点->子节点）。

检索分层路径

一般需要获取一个分层结构的路径问题，那么

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';
+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

检索叶子节点

SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;
+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

检索指定路径

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';
+-------------+----------------------+-------------+-------+
| lev1        | lev2                 | lev3        | lev4  |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
+-------------+----------------------+-------------+-------+
1 row in set (0.01 sec)

邻接表的缺点

在检索路径的过程中，除了本层外，每一层都会对应一个LEFT JOIN，那么如果层数不定怎么办？或者层数过多？
在删除中间层的节点时，需要同时删除该节点下的所有节点，否则会出现孤立节点。

嵌套集合模型Nested Set Model

原文中主要的目的是介绍嵌套集合模型，如下

通过集合的包含关系，嵌套结合模型可以表示分层结构，每一个分层可以用一个Set来表示（一个圈），父节点所在的圈包含所有子节点所在的圈。

为了用MySQL来表示集合关系，需要定义连个字段left和right（表示一个集合的范围）。

CREATE TABLE nested_category (
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        lft INT NOT NULL,
        rgt INT NOT NULL
);
INSERT INTO nested_category VALUES
  (1,'ELECTRONICS',1,20),
  (2,'TELEVISIONS',2,9),
  (3,'TUBE',3,4),
  (4,'LCD',5,6),
  (5,'PLASMA',7,8),
  (6,'PORTABLE ELECTRONICS',10,19),
  (7,'MP3 PLAYERS',11,14),
  (8,'FLASH',12,13),
  (9,'CD PLAYERS',15,16),
  (10,'2 WAY RADIOS',17,18);
SELECT * FROM nested_category ORDER BY category_id;
+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |
+-------------+----------------------+-----+-----+

由于left和right是MySQL的保留字，因此，字段名称用lft和rgt代替。每一个集合都是从lft开始到rgt结束，也就是集合的两个边界。

在树中也同样适用，

当为树状结构编号时，我们从左到右，一次一层，赋值按照从左到右的顺序遍历其子节点，这种方法称为先序遍历算法。

检索分层路径

由于子节点的lft值总在父节点的lft和rgt值之间，所以可以通过父节点连接到子节点上来检索整棵树。

SELECT node.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;
+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| TELEVISIONS          |
| TUBE                 |
| LCD                  |
| PLASMA               |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
| CD PLAYERS           |
| 2 WAY RADIOS         |
+----------------------+

这个方法并不需要考虑层数，而且不需要考虑节点的rgt。

检索所有叶子节点

由于每一个叶子节点的rgt=lft+1，那么只需要这一个条件即可。

SELECT name
FROM nested_category
WHERE rgt = lft + 1;
+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

检索节点路径

不再需要多个join连接操作。

SELECT parent.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'FLASH'
ORDER BY node.lft;
+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
+----------------------+

检索节点深度

通过COUNT和GROUP BY函数来获取父节点的个数。

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| ELECTRONICS          |     0 |
| TELEVISIONS          |     1 |
| TUBE                 |     2 |
| LCD                  |     2 |
| PLASMA               |     2 |
| PORTABLE ELECTRONICS |     1 |
| MP3 PLAYERS          |     2 |
| FLASH                |     3 |
| CD PLAYERS           |     2 |
| 2 WAY RADIOS         |     2 |
+----------------------+-------+

甚至可以得到分层的缩进结果，

SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

检索子树的深度

考虑到检索中需要自连接的node或parent，因此需要增加一个额外的连接来作为子查询来限制子树。

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| FLASH                |     2 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

检索节点的直接子节点

假设一个场景，当用户点击网站上电子产品的一个分类时，将呈现该分类下的产品，同时需要列出所有子分类，并不是全部分类。
为了限制显示分类的层数，需要使用HAVING字句，

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                        nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;
+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

增加新节点

上面已经介绍了如何检索结果，那么如何才能增加新的节点呢？

如果希望在TELEVISIONS和PROTABLE ELECTRONICS节点之间增加一个新的节点，那么新节点的lft和rgt的值应该是10和11，那么所有大于10的节点（新节点右侧的节点）的lft和rgt都应该加2，如上图所示。

LOCK TABLE nested_category WRITE;
SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);
UNLOCK TABLES

如果希望在叶子节点下增加节点，需要修改下查询语句，

LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft FROM nested_category
WHERE name = '2 WAY RADIOS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO nested_category(name, lft, rgt) VALUES('FRS', @myLeft + 1, @myLeft + 2);
UNLOCK TABLES;```

###删除节点

删除叶子节点比较容易，只需要删除自己，而删除一个中间层节点就需要删除其所有子节点。在这个模型中，所有子节点的节点正好在lft和rgt之间。

LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';
DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
UNLOCK TABLES;

在某些情况下，只需要删除某个节点，但是并不希望删除该节点下的子节点数据。
通过把右侧所有节点的左右值-2，当前节点的子节点左右值-1

LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'PORTABLE ELECTRONICS';
DELETE FROM nested_category WHERE lft = @myLeft;
UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;
UNLOCK TABLES;
```

Mysql嵌套集合模型【省份城市示例】

父分类包围了其子分类。在数据表中，我们通过使用表示节点的嵌套关系的左值(left value)和右值(right value)来表现嵌套集合模型

中数据的分层特性。我们使用了lft和rgt来代替left和right，是因为在MySQL中left和right是保留字。

http://dev.mysql.com/doc/mysql/en/reserved-words.html，有一份详细的MySQL保留字清单。

那么，我们怎样决定左值和右值呢？我们从外层节点的最左侧开始，从左到右编号：

CREATE TABLE `region` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(30) default NULL,
`parent_id` int(11) default NULL,
`lft` int(10) unsigned default NULL,
`rgt` int(10) unsigned default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (1,'中国',0,1,20);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (2,'北京',1,2,5);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (3,'北京市',2,3,4);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (4,'上海',1,6,9);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (5,'上海市',4,7,8);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (6,'浙江',1,10,19);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (7,'金华市',6,15,16);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (8,'温州市',6,17,18);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (9,'杭州市',6,11,12);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (10,'宁波市',6,13,14);

查询全部节点分成展示：

SELECT CONCAT(REPEAT(' ', COUNT(parent.id)-1), node.name) AS name, node.id,node.lft,node.rgt, COUNT(parent.id)
FROM region AS node, region AS parent
where node.lft BETWEEN parent.lft AND parent.rgt
group by node.id
ORDER BY node.lft;

查询节点路径：
select parent.name, parent.id from region as node, region as parent
where node.lft BETWEEN parent.lft and parent.rgt and node.name='金华市';

参考

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索模型
json多层集合嵌套解析、嵌套集合模型、嵌套集合、java 集合嵌套、mybatis 对象嵌套集合，以便于您获取更多的相关知识。

时间： 2024-11-02 06:32:11

MySQL嵌套集合模型实现方法及省份城市示例的相关文章

MySQL实现嵌套集合模型

译文主要是介绍如何用MySQL来存储嵌套集合数据.在其中会增加一些自己的理解,也会删除掉一些自认为无用的废话. 这篇文章主要讲的是嵌套集合模型,所以邻接表不是本文的重点,简单略过就好. 也许这是原文地址,因为我也不知道这是不是原文. 介绍 什么是分层数据? 类似于树形结构,除了根节点和叶子节点外,所有节点都有用一个父节点和多个子节点. 那么,在MySQL中如何处理分层数据呢? 原文中介绍了两种分层结构模型:邻接表模型和嵌套集合模型. 邻接表模型(The Adjacency List Model)

c++连接mysql数据库的两种方法(ADO连接和mysql api连接)_C 语言

第一种方法可以实现我当前的需求,通过连接不同的字符串来连接不同的数据库.暂时只连接了mysql,sqlserver,oracle,access.对于access,因为它创建表的SQL语句不太兼容标准SQL语句,需要做一些处理,这里暂时不说.第二种方法只能针对于mysql数据库的连接,不过用这种方法不用安装MyODBC服务器程序. 不管用哪种方法,首先需要安装Mysql数据库,安装方法请看"mysql安装及一些注意点".最好安装一个Navicat for mysql,方便操作mysql数

Linux下安装Python3和django并配置mysql作为django默认服务器方法_Linux

我的操作系统为centos6.5 1  首先选择django要使用什么数据库.django1.10默认数据库为sqlite3,本人想使用mysql数据库,但为了测试方便顺便要安装一下sqlite开发包. yum install mysql mysql-devel #为了测试方便,我们需要安装sqlite-devel包 yum install sqlite-devel 2  接下来需要安装Python了,因为Python3已经成为主流,所以接下来我们要安装Python3,到官网去下载Python3

php利用scws实现mysql全文搜索功能的方法_php技巧

本文实例讲述了php利用scws实现mysql全文搜索功能的方法.分享给大家供大家参考.具体方法如下: scws这样的中文分词插件比较不错,简单的学习了一下,它包涵一些专有名称.人名.地名.数字年代等规则集合,可以直接将语句按这些规则分开成一个一个关键词,准确率在90%-95%之间,按照安装说明把scws的扩展放入php的扩展目录里,下载规则文件和词典文件,并在php配置文件中引用它们,就可以用scws进行分词了. 1) 修改 php 扩展代码以兼容支持 php 5.4.x 2) 修正 php

优化mysql嵌套查询和联表查询

优化mysql嵌套查询和联表查询 嵌套查询糟糕的优化 在上面我提到过,不考虑特殊的情况,联表查询要比嵌套查询更有效.尽管两条查询表达的是同样的意思,尽管你的计划是告诉服务器要做什么,然后让它决定怎么做,但有时候你非得告诉它改怎么做.否则优化器可能会做傻事.我最近就碰到这样的情况.这几个表是三层分级关系:category, subcategory和item.有几千条记录在category表,几百条记录在subcategory表,以及几百万条在item表.你可以忽略category表了,我只是交代一

php+mysql实现用户注册登陆的方法

 这篇文章主要介绍了php+mysql实现用户注册登陆的方法,可实现简单的用户注册登录的功能,具有一定参考借鉴价值,需要的朋友可以参考下     本文实例讲述了php+mysql实现用户注册登陆的方法.分享给大家供大家参考.具体分析如下: 这是一款利用php与mysql数据库实现的用户注册与登录代码,功能也是比较简单实用的用户注册程序,同时也加了用户登录时验证码程序,这样做就安全了很多,代码如下: 代码如下: <!doctype html public "-//w3c//dtd xhtml

如何查看mysql版本的四种方法

菜鸟大讲堂:如何查看mysql版本的四种方法 1:在终端下:mysql -V. 以下是代码片段: [shengting@login ~]$ mysql -V mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i686) 2:在mysql中:mysql> status; 以下是代码片段: mysql> status; -------------- mysql Ver 14.7 Distrib 4.1.10a, for redhat-lin

MySQL order by性能优化方法实例

  这篇文章主要介绍了MySQL order by性能优化方法实例,本文讲解了MySQL中order by的原理和优化order by的三种方法,需要的朋友可以参考下 前言 工作过程中,各种业务需求在访问数据库的时候要求有order by排序.有时候不必要的或者不合理的排序操作很可能导致数据库系统崩溃.如何处理好order by排序呢?本文从原理以及优化层面介绍 order by . 一 MySQL中order by的原理 1 利用索引的有序性获取有序数据 当查询语句的 order BY 条件和

MySQL延迟关联性能优化方法

  这篇文章主要介绍了MySQL延迟关联性能优化方法,本文讲解了延迟关联的背景.延迟关联的分析.延迟关联的解决等内容,需要的朋友可以参考下 [背景] 某业务数据库load 报警异常,cpu usr 达到30-40 ,居高不下.使用工具查看数据库正在执行的sql ,排在前面的大部分是: 代码如下: SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, b