如何在关系型数据库中存储树形结构

文中使用公司部门结构树作为栗子，要在mysql中存储这个公司部门结构树

邻接表想必大家都不陌生吧，用邻接表的关键是，在每个节点存储他的父节点的id。

在每一个部门信息中都存储了他的父节点id，parent_id字段

导入数据的过程就不说了，直接来看下数据吧：

这里使用常用的几种查询方式来看下这种方案的查询

可以通过parent_id做查询条件，可以快速查询到一个部门的直属下级部门

通过部门信息中的parent_id去查相应的父节点信息就可以快速实现

这种数据存储结构下，更新数据是比较方便快捷的，添加数据时直接找准父节点的id，组织部门变更时，也直接变更父id就好了，删除时候，看自己业务是否需要删除子节点这几种情况，

路径标的要点，就是每个节点存储根节点到该节点的路径，其实我觉得和别的几种方案可以共用

在每一个部门信息中都存储了他完整的路径，path字段

导入数据的过程就不说了，直接来看下数据吧：

使用路径表，通过path这个字段查询起来是比较困难的，一般都需要使用like，CONCAT函数、REPLACE函数等做字符串的处理逻辑，查询起来比较复杂，这里不做展示了，线上服务不建议使用这种方式，查询效率低会影响到服务性能，一般建议和邻接表方式统一使用，同时添加parent_id和path字段，parent_id用来查询，path用来查看节点完整的路径

这种数据存储结构下，更新数据是比较方便快捷的，添加数据时直接找准路径就好，组织部门变更时，也直接找准路径就好，删除时候，看自己业务是否需要删除子节点这几种情况，

Closure Table，百度直译过来叫闭合表，大多数人叫做闭包表，这种方案的要点是存储公司部门信息主表中，不存储节点关系的数据，使用另一张关系表来存储节点之间的关系，其中包含了任何两个有关系（上下级）节点的关联信息

公司部门信息主表，只需要存储部门的本身信息

主要包括三个字段

要点就是关系表的一条记录是一个上级节点、下级节点、与他们之间的路径距离。拿部门结构图来举例子

总公司-企划部的关系数据是：

总公司-大区A的关系数据是：

关系表中存储所有的节点路径信息，还用distance表示路径的距离，需要把树形结构中每两个节点之间的路径信息都维护进来。

数据存储的过程就拿导入总公司-门店A的过程做个示例。主表的数据存储就不说，说下关系中，存储部门结构的路径信息，总公司-门店A总共包含以下几条路径：

看到了么，是存储了所有总公司-门店A之间的路径信息

这里使用常用的几种查询方式来看下这种方案的查询

这种数据存储结构下，更新数据比较麻烦，因为他存储了两节点直接所有路径信息（包括中间节点的）

现在，让我们一行行分析结构生成器生成的authors表，下面是up()方法中的代码：

1 // auto increment id (primary key)

2 $table->increments('id')

3

4 $table->string('name')

5 $table->integer('age')->nullable()

6 $table->boolean('active')->default(1)

7 $table->integer('role_id')->unsigned()

8 $table->text('bio')

9

10 // created_at, updated_at DATETIME

11 $table->timestamps()

实际上生成的SQL代码为：

1 `id` INT(11) NOT NULL AUTO_INCREMENT,

2 `name` VARCHAR(255) NOT NULL,

3 `age` INT(11) NULL DEFAULT NULL,

4 `active` TINYINT(4) NOT NULL DEFAULT '1',

5 `role_id` INT(10) UNSIGNED NOT NULL,

6 `bio` TEXT NOT NULL,

7

8 `created_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',

9 `updated_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',

我们会意识到，迁移是多么的强大，我们自需要记住一些结构生成器方法而不是写晦涩难懂的SQL DDL代码。

我们创建了表，那我们想要回滚的时候怎么办呢？只需要在于up()方法对应的down()方法中使用drop()方法即可。

1 public function down()

2 {

3 Schema::drop('authors')

4 }

这个方法非常简单，只有一行。它的全部作用就是删除”authors”表，如果你熟悉sql，它等同于DROP TABLE authors。

现在，我们已经写好了架构，我们就可以对数据库执行迁移文件了。转到命令行工具，跳转到应用目录下，运行artisan migrate命令：

1 php artisan migrate

执行结果如下：

检查数据库，你会发现已经有了”authors”表，

表结构如下：

如果你要使用sql语句实现这张表，那么sql查询语句如下：

1 CREATE TABLE `authors` (

2 id int AUTO_INCREMENT NOT NULL,

3 namevarchar(255) NOT NULL,

4 age int,

5 active tinyint NOT NULL DEFAULT '1',

6 role_id int(10) UNSIGNED NOT NULL,

7 bio text NOT NULL,

8 created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

9 updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

10 email varchar(64) NOT NULL,

11 /* Keys */

12 PRIMARY KEY (id)

13 ) ENGINE = InnoDB

现在假设我们上次的执行存在错误想要回滚，我们自需要使用artisan命令行工具执行下面的命令即可:下一次，如果你想修改数据模型，你可以创建一个新的迁移，再次执行命令artisan migrate。每一次执行artisan migrate命令，它都会根据时间戳去检查哪些没有执行，如果执行了，就跳到下一个文件，如果没有执行，就执行这次迁移，直到执行完所有迁移文件。

1 php artisan migrate:rollback

运行如下：

你会发现，表”authors”已经从数据库中删除了。

现在重新创建”authors”表，执行artisan migrate命令：

1 php artisan migrate

这时，表”authors”又重新创建了。

但是我想在表中添加”email”列。先使用artisan创建新的迁移文件：

php artisan migrate:make add_email_to_authors_table

运行结果如下：

然后编辑2014_03_12_051119_add_email_to_authors_table.php文件，添加电子邮件列。我们使用Schema::table()方法，有两个参数：表名、闭包函数（在此函数内添加字段）。

1 public function up()

2 {

3 Schema::table('authors', function($table) {

4 $table ->string('email', 64)

5 })

6 }

有了添加方法，当然也需要添加回滚方法了，这里再次使用Schema::table()方法。

1 public function down()

2 {

3 Schema::table('authors', function($table) {

4 $table ->dropColumn('email')

5 })

6 }

上面的方法使用了dropColumn()方法上出列。

现在运行artisan命令运行该迁移文件如下：

刷新数据库表，你会发现”email”字段已经在”authors”中出现了，如下图：

如果我们回滚了这次迁移，那么email字段会从表中删除。

Artisan命令还有一个更强大的命令行，它可以回滚所有的迁移：

---