手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜Qcloud , 注册 | 登陆

几个无限分类的设计

首页 > DataBase >

之前在博客里贴过无限分类的介绍,那是官方的例子,但上次在给同事们做介绍的时候,却发现官方的网站已经打不开了。所幸,又找到了一篇,好象这个才是原文?
原文来自:http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql
为了这次保证所有的数据都能够记录下来而不是再被茫茫的互联网淹没,所以我做了备份。
mysql.7z
但同样的,我也再次复制一部分的内容下来,好象是说这种无限分类的效率,在被其他项目查询的时候是最高的,但CRUD的时候就有点纠结。
OK,上主菜:

The Nested Set Model

What I would like to focus on in this article is a different approach, commonly referred to as the Nested Set Model. In the Nested Set Model, we can look at our hierarchy in a new way, not as nodes and lines, but as nested containers. Try picturing our electronics categories this way:

Nested Categories

Notice how our hierarchy is still maintained, as parent categories envelop their children.We represent this form of hierarchy in a table through the use of left and right values to represent the nesting of our nodes:

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 | +-------------+----------------------+-----+-----+

We use lft and rgt because left and right are reserved words in MySQL, see http://dev.mysql.com/doc/mysql/en/reserved-words.html for the full list of reserved words.

So how do we determine left and right values? We start numbering at the leftmost side of the outer node and continue to the right:

Numbering edges

This design can be applied to a typical tree as well:

Numbered Tree

When working with a tree, we work from left to right, one layer at a time, descending to each node’s children before assigning a right-hand number and moving on to the right. This approach is called the modified preorder tree traversal algorithm.

因为我已经在压缩包里做了备份,所以我图片就引用外站的了。




本站采用创作共享版权协议, 要求署名、非商业和保持一致. 本站欢迎任何非商业应用的转载, 但须注明出自"膘叔", 保留原始链接, 此外还必须标注原文标题和链接.

Tags: mysql, 无限分类

« 上一篇 | 下一篇 »

只显示10条记录相关文章

1条记录访客评论

呵呵,这个叫预排序遍历树算法,我一直在用的,是很好,就是增删改记录的时候算左右值有点麻烦,http://tech.ddvip.com/2009-03/1237534469111885.html

Post by fifsky on 2011, August 19, 9:36 AM 引用此文发表评论 #1


发表评论

评论内容 (必填):