MySQL 树形结构数据库设计

程序设计过程中，我们常常用树形结构来表示某些数据的关联关系，如企业的部门上下级、电商平台的商品分类等等，通常而言，我们需要通过数据库来完成数据的持久化。由于关系型数据库没有一个很好的树形结构解决方案，因此设计合适的 Schema 以及其对应的 CURD 算法是关键。

接下来，我们以电商商品分类结构来介绍几种解决方案。

邻接表

邻接表就是把所有节点都放在一张表中，然后用一个属性来记录每个节点的父节点。如下：

CREATE TABLE `cate` (
`cate_id`  smallint NOT NULL AUTO_INCREMENT ,
`cate_name`  varchar(30) NOT NULL COMMENT '分类名称' ,
`cate_sn`  char(3) NULL DEFAULT '' COMMENT '分类编号' ,
`cate_sort`  smallint NULL DEFAULT 0 COMMENT '分类排序' ,
`cate_parent_id`  smallint NULL DEFAULT 0 COMMENT '父级id' ,
PRIMARY KEY (`cate_id`)
);

优点：

增加 和 编辑节点信息十分简便，如：

mysql > insert into first_cate (cate_name) VALUES ('服装');

缺点：

查询会十分麻烦：

一次只能查询有限的节点，而且每多查询一次都要多嵌套一层连接语句

mysql >  SELECT * from first_cate as cate1 LEFT JOIN first_cate as cate2 on cate2.cate_parent_id = cate1.cate_id where cate1.cate_id = 1;

删除也变得十分麻烦，需要额外的查询，获取其后节点的数据，然后再进行删除。

路径枚举

即在表中加入一个字段，用来存储根节点到当前节点的路径。如下：

CREATE TABLE `second_cate` (
`cate_id`  smallint NOT NULL AUTO_INCREMENT ,
`cate_name`  varchar(30) NOT NULL ,
`cate_sn`  char(3) NULL ,
`cate_sort`  smallint NULL DEFAULT 0 COMMENT '分类排序' ,
`cate_path`  varchar(100) NOT NULL COMMENT '路径' ,
PRIMARY KEY (`cate_id`)
);

存储如下数据：

优点：

可以比较简单的查询到一个节点的祖先和后代。

可以通过以下类似的 sql 来获取

mysql > SELECT * from second_cate where cate_path like '1/2/%';

插入一个节点页相对比较简单，只需要复制父节点的路径，加入自己即可。

缺点：

1.数据库没有约束来确保路径的格式是否完全正确

2.无法保证路径中的某个节点是否真实存在

3.依赖程序维护路径的字符串且验证字符串的正确性开销较大

4.VARCHAR 长度有限，因此树不能无限扩展

总结

邻接表是最简单的设计，查询单个节点、删除、更新、新增都比较简单。但是查询一颗完整的树比较复杂，可以通过缓存来解决。

枚举路径在查询单个节点，查询树，删除、更新、新增都比较见长，但无法保证引用完整性，使得设计变得十分脆弱，数据存储也比较冗余。可以在删除完之后对相关的节点重新生成路径。