mysql分层数据管理–Managing Hierarchical Data in MySQL(译文)
原文地址：
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
Introduction
大多数用户都曾在数据库中处理过分层数据(hierarchical data)，认为分层数据的管理不是关系数据库的目的。之所以这么认为，是因为关系数据库中的表没有层次关系，只是简单的平面化的列表；而分层数据具有父－子关系，显然关系数据库中的表不能自然地表现出其分层的特性。
我们认为，分层数据是每项只有一个父项和零个或多个子项（根项除外，根项没有父项）的数据集合。分层数据存在于许多基于数据库的应用程序中，包括论坛和邮件列表中的分类、商业组织图表、内容管理系统的分类、产品分类。我们打算使用下面一个虚构的电子商店的产品分类：
这些分类层次与上面提到的一些例子中的分类层次是相类似的。在本文中我们将从传统的邻接表(adjacency list)模型出发，阐述2种在MySQL中处理分层数据的模型。
邻接表模型
述例子的分类数据将被存储在下面的数据表中
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)
在邻接表模型中，数据表中的每项包含了指向其父项的指示器。在此例中，最上层项的父项为空值(NULL)。邻接表模型的优势在于它很简单，可以很容易地看出FLASH是MP3 PLAYERS的子项，哪个是portable electronics的子项，哪个是electronics的子项。虽然，在客户端编码中邻接表模型处理起来也相当的简单，但是如果是纯SQL编码的话，该模型会有很多问题。
检索整树
通常在处理分层数据时首要的任务是，以某种缩进形式来呈现一棵完整的树。为此，在纯SQL编码中通常的做法是使用自连接(self-join)：
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)
检索所有叶子节点
我们可以用左连接(LEFT JOIN)来检索出树中所有叶子节点(没有孩子节点的节点）：
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.
