文章图片
无限级分类表设计
实际上我们在开发过程中,就拿商品类别来说,它还可以有更多的延伸,我们先来举一个简单的例子,比如图书,他的下面还可以分小说、文学等,在小说下面还可以分为言情小说、科幻小说等,还可以再往下一级一级继续分,那么这种分类就属于无限极分类,那么无限极分类表该如何去设计,理论上说我们可以设计很多张表,但是随着分类的逐步增多,表的数目就会越来越多,所以无限极分类表往往采用另外一种形式。
我们先来看一下它的语法结构。
CREATE TABLE tdb_goods_types( type_idSMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, type_name VARCHAR(20) NOT NULL, parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 );
这种数据表至少存在3个字段,第一个是分类的id,第二个字段是分类的名称,第三个字段是他父类的id,也就是说他实际上是通过自身的连接来实现的。
现在我们就先创建这张表,操作命令及结果如下:
CREATE TABLE tdb_goods_types( type_idSMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, type_name VARCHAR(20) NOT NULL, parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 );
现在我们插入几条我们事先准备好的数据,命令如下:
INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT); INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT); INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1); INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1); INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3); INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3); INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4); INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4); INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2); INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2); INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9); INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9); INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9); INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10); INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);
OK,插入成功之后。
下面我们来查看一下里边的记录,我们输入SELECT * FROMtdb_goods_types;
文章图片
那么后面的数字表示什么意思呢?
比如这个家用电器的0就表示他没有父亲节点,为顶级分类,再比如大家电后面的1就表示它的父亲节点是id号为1的家用电器
这就是无限极分类的数据表,但是这里边就涉及到另外一个问题,我们该怎么做查找。
要做查找的话,我们就需要通过自身连接来实现,所谓自身连接,就是指数据表自己来连接自己,下面我们来做一个简单的演示,我们还以刚才那个表为例,比如我要查找所有子类的父类,比如家用电器他的父类是什么?如果没有就是NULL,那么这就需要大家有一点想象力,就是在这张表的右侧还有一张跟他结构完全一样的数据表。假设我们把左边的当成父表,右边的当成子表,那么我们左边的父表中的parent_id这个字段就没有什么用了。
下面我们来看看该怎么写,这里边就一定要起别名了,因为所有的字段都是相同的,我们就把子表取为s,父表取为p,具体操作命令及结果如下:
文章图片
Ok,这样我们就查到了子类所对应的父类的名字。没有父类的就为NULL.当然这里我们只能查到他的一级父类,mysql暂时还没有递归查询的能力,后面我们可以通过程序来实现。
下面我们再来查找一下子类的父类,以及父类下面的子类,这里只是参照的表不同而已,比如这次我们把左边的当子表,右边的当父表,分组排序操作命令及结果如下:
文章图片
注:我在分组的时候出现过一个错误:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test2.p.type_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
可以去除only_full_group_by; 登录数据库后敲以下代码,回车,继续分组就好了:
set @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
那么现在如果我想要父类下面子类的数目。那么该怎么办呢?我们只需要对子类数目做一下统计。
现在我们想要子类的数目而不是子类的名字,那么我们只需要做一个计数,我们简单修改一下。修改后的命令及结果如下:
文章图片
【MySQL基础|MySQL无限极分类数据表的设计】OK,这就是我们想要的。这也就是通过自身连接来实现的。
推荐阅读
- mysql|InnoDB数据页结构
- javaweb|基于Servlet+jsp+mysql开发javaWeb学生成绩管理系统
- mysql|一文深入理解mysql
- Java毕业设计项目实战篇|Java项目:在线嘿嘿网盘系统设计和实现(java+Springboot+ssm+mysql+maven)
- SQL|SQL基本功(五)--函数、谓词、CASE表达式
- vue|电商后台管理系统(vue+python|node.js)
- Java及基础算法及数据结构|旧笔记整理(MySQL)
- mysql|双非本211硕,无实习无项目,自学大数据开发,秋招上岸
- 数据库|Mysql--InnoDB存储引擎详解
- MySQL学习笔记-9-order by