mysql 层次数据_使用 MySQL 管理层次结构的数据

概述

我们知道，关系数据库的表更适合扁平的列表，而不是像 XML 那样可以直管的保存具有父子关系的层次结构数据。

首先定义一下我们讨论的层次结构，是这样的一组数据，每个条目只能有一个父条目，可以有零个或多个子条目(唯一的例外是根条目，它没有父条目)。许多依赖数据库的应用都会遇到层次结构的数据，例如论坛或邮件列表的线索、企业的组织结构图、内容管理系统或商城的分类目录等等。我们如下数据作为示例：

数据来源于维基百科的这个页面，为什么挑了这几个条目，以及是否准确合理在这里就不深究了。

Mike Hillyer 考虑了两种不同的模型——邻接表(Adjacency List)和嵌套集(Nested Set)来实现这个层次结构。

邻接表(Adjacency List)模型

我们可以很直观的使用下面的方式来保存如图所示的结构。

创建名为 distributions 的表：

CREATE TABLE distributions (

id INT NOT NULL AUTO_INCREMENT,

name VARCHAR(32) NOT NULL,

parent INT NULL DEFAULT NULL,

PRIMARY KEY (id)

)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8;

插入数据：

INSERT INTO distributions VALUES

(1, 'Linux', NULL),

(2, 'Debian', 1),

(3, 'Knoppix', 2),

(4, 'Ubuntu', 2),

(5, 'Gentoo', 1),

(6, 'Red Hat', 1),

(7, 'Fedora Core', 6),

(8, 'RHEL', 6),

(9, 'CentOS', 8),

(10, 'Oracle Linux', 8);

执行：

SELECT * FROM distributions;

可以看到表中的数据形如：

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

|id |name |parent |

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

|1 |Linux |NULL |

|2 |Debian |1 |

|3 |Knoppix |2 |

|4 |Ubuntu |2 |

|5 |Gentoo |1 |

|6 |Red Hat |1 |

|7 |Fedora Core |6 |

|8 |RHEL |6 |

|9 |CentOS |8 |

|10 |Oracle Linux |8 |

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

使用链接表模型，表中的每一条记录都包含一个指向其上层记录的指针。顶层记录(这个例子中是 Linux)的这个字段的值为 NULL。邻接表的优势是相当直观和简单，我们一眼就能看出 CentOS 衍生自 RHEL，后者又是从 Red Hat 发展而来的。虽然客户端程序可能处理起来相当简单，但是使用纯 SQL 处理邻接表则会遇到一些麻烦。

获取整棵树以及单个节点的完整路径

第一个处理层次结构常见的任务是显示整个层次结构，通常包含一定的缩进。使用纯 SQL 处理时通常需要借助所谓的 self-join 技巧：

SELECT

t1.name AS level1,

t2.name as level2,

t3.name as level3,

t4.name as level4

FROM

distributions AS t1

LEFT JOIN distributions AS t2

ON t2.parent = t1.id

LEFT JOIN distributions AS t3

ON t3.parent = t2.id

LEFT JOIN distributions AS t4

ON t4.parent = t3.id

WHERE t1.name = 'Linux';

结果如下：

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

|level1 |level2 |level3 |level4 |

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

|Linux |Red Hat |RHEL |CentOS |

|Linux |Red Hat |RHEL |Oracle Linux |

|Linux |Debian |Knoppix |NULL |

|Linux |Debian |Ubuntu |NULL |

|Linux |Red Hat |Fedora Core |NULL |

|Linux |Gentoo |NULL |NULL |

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

可以看到，实际上客户端代码拿到这个结果也不容易处理。对比原文，我们发现返回结果的顺序也是不确定的。在实践中没有什么参考意义。不过可以通过增加一个 WHERE 条件，获取一个节点的完整路径：

SELECT

t1.name AS level1,

t2.name as level2,

t3.name as level3,

t4.name as level4

FROM

distributions AS t1

LEFT JOIN distributions AS t2

ON t2.parent = t1.id

LEFT JOIN distributions AS t3

ON t3.parent = t2.id

LEFT JOIN distributions AS t4

ON t4.parent = t3.id

WHERE

t1.name = 'Linux'

AND t4.name = 'CentOS';

结果如下：

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

| level1 | level2 | level3 | level4 |

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

| Linux | Red Hat | RHEL | CentOS |

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

找出