几篇关于无限分类算法的文章第1/5页

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

By Mike Hillyer

Introduction

Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table.

For our purposes, hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent). Hierarchical data can be found in a variety of database applications, including forum and mailing list threads, business organization charts, content management categories, and product categories. For our purposes we will use the following product category hierarchy from an fictional electronics store:

These categories form a hierarchy in much the same way as the other examples cited above. In this article we will examine two models for dealing with hierarchical data in MySQL, starting with the traditional adjacency list model.

The Adjacency List Model

Typically the example categories shown above will be stored in a table like the following (I'm including full CREATE and INSERT statements so you can follow along):

CREATE TABLE category(category_id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(20) NOT NULL,parent INT DEFAULT NULL);INSERT INTO categoryVALUES(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)

In the adjacency list model, each item in the table contains a pointer to its parent. The topmost element, in this case electronics, has a NULL value for its parent. The adjacency list model has the advantage of being quite simple, it is easy to see that FLASH is a child of mp3 players, which is a child of portable electronics, which is a child of electronics. While the adjacency list model can be dealt with fairly easily in client-side code, working with the model can be more problematic in pure SQL.

Retrieving a Full Tree