分层数据管理和树状结构

MYSQL 中分层数据的管理

MYSQL 中分层数据的管理

By Mike Hillyer

引言

大多数用户都曾在数据库中处理过分层数据(hierarchical data)，认为分层数据的管理不

是关系数据库的目的。之所以这么认为，是因为关系数据库中的表没有层次关系，只是简单

的平面化的列表；而分层数据具有父－子关系，显然关系数据库中的表不能自然地表现出其

分层的特性。

邻接表模型

上述例子的分类数据将被存储在下面的数据表中（我给出了全部的数据表创建、数据插入的

第 1 页/总 16 页

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),

+-------------+----------------------+--------+

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

户端编码中邻接表模型处理起来也相当的简单，但是如果是纯 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

| ELECTRONICS | TELEVISIONS | PLASMA | NULL |

第 2 页/总 16 页

MYSQL 中分层数据的管理

| 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

| 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

+-------------+----------------------+-------------+-------+

| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |

+-------------+----------------------+-------------+-------+

1 row in set (0.01 sec)

这种方法的主要局限是你需要为每层数据添加一个自连接，随着层次的增加，自连接变得越

来越复杂，检索的性能自然而然的也就下降了。

邻接表模型的局限性

第 3 页/总 16 页

MYSQL 中分层数据的管理

该分类所在的层次。另外，我们在删除节点的时候要特别小心，因为潜在的可能会孤立一棵

子树（当删除 portable electronics 分类时，所有他的子分类都成了孤儿）。部分局限性

可以通过使用客户端代码或者存储过程来解决，我们可以从树的底部开始向上迭代来获得一

颗树或者单一路径，我们也可以在删除节点的时候使其子节点指向一个新的父节点，来防止

过使用表示节点的嵌套关系的左值(left value)和右值(right value)来表现嵌套集合模型

中数据的分层特性：

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),

+-------------+----------------------+-----+-----+

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

第 4 页/总 16 页