MySql 树形结构表存储方案

type

Post

status

Published

date

Jul 18, 2022

slug

article-6

summary

MySql 树形结构表设计，xxx分类表等

方案一


CREATE TABLE IF NOT EXISTS `xxx_classify`(
	`id` BIGINT(20) UNSIGNED COMMENT '类别编号',
	`name` VARCHAR(20) NOT NULL COMMENT '类别名称',
	`parent_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '父类别编号',  -- 0表示没有父类
	PRIMARY KEY (`id`) -- 主键索引
)ENGINE=INNODB DEFAULT CHARSET=UTF8 COMMENT 'xxx分类表';

id	name	parent_id
1	食物	0
2	中餐	1
3	西餐	1
4	川菜	2
5	粤菜	2
6	江浙菜	2
7	牛排	3
8	汉堡	3

弊端: 在Dao层代码中, 如果想要查询所有的子节点, 就需要不断的循环递归查询数据库, 这会大大降低查询效率

方案二

为了解决方案一中的弊端, 基于索引联存储树形结构, 在方案1的基础上增加两个字段

index_link 索引链

level 与父类的距离


CREATE TABLE IF NOT EXISTS `xxx_classify`(
	`id` BIGINT(20) UNSIGNED COMMENT '类别编号',
	`name` VARCHAR(20) NOT NULL COMMENT '类别名称',
	`parent_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '父类别编号',  -- 0表示没有父类
	`index_link` VARCHAR(255) NOT NULL COMMENT '索引链',
	`level` INTEGER NOT NULL COMMENT '与父级的距离',
	PRIMARY KEY (`id`) -- 主键索引
)ENGINE=INNODB DEFAULT CHARSET=UTF8 COMMENT 'xxx分类表';

id	name	parent_id	index_link	level
1	食物	0	1-	0
2	中餐	1	1-2-	1
3	西餐	1	1-3-	1
4	川菜	2	1-2-4-	2
5	粤菜	2	1-2-5-	2
6	江浙菜	2	1-2-6-	2
7	牛排	3	1-3-7-	2
8	汉堡	3	1-3-8-	2

当需要查询某个节点的子孙节点时, 只需要一行sql指令即可实现


SELECT * FROM `xxx_classify` WHERE index_link LIKE '1-%';

当想要根据深度查询子孙节点时, 可以添加 level 条件


SELECT * FROM `xxx_classify` WHERE index_link LIKE '1-%' AND level = 1;

优点: 改变了方案一查询效率低的弊端。

缺点: 如果表中数据发生改变, 需同时更新 index_link 和 leve 。

总结

两种方式各有利弊, 根据实际业务逻辑情况, 选择合适的方案设计表结构

如果表更新较多, 则采用方案一更优。

如果表查询较多, 则采用方案二更优。