数据库

MySQL技巧（二）——无限级分类表设计

云博小周宇3 Mins Read

无限级分类表的设计(掌握‘自身连接‘)

类似图书这种，会有很多种分类，而且在现实生活中这种分类会无限的往下分，所以不可能每有一个分类就创建一个分类表。应该使用下面这种语句

　　

DROP TABLE IF EXISTS tdb_goods_types; CREATE TABLE tdb_goods_types( type_id SMALLINT  PRIMARY KEY AUTO_INCREMENT COMMENT ‘分类ID‘, type_name 　　　VARCHAR(50) 　　　　　　　　　　　　　　 COMMENT ‘分类名称‘, parent_id SMALLINT 　　NOT NULL DEFAULT 0  　　 COMMENT ‘父类ID‘ );

然后再模拟图书类的分类来个小demo

INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘电子书‘,DEFAULT); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘文学‘,1); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘影视原著‘,2); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘中外名著‘,2); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘漫画杂志‘,2); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘文学‘,2); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘小说‘,2); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘传记‘,2); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘经管‘,1); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘金融投资‘,9); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘市场营销‘,9); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘管理学‘,9); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘职场进阶‘,9); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘科学新知‘,1); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘人工智能‘,14); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘电子商务‘,14); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘大数据‘,14); INSERT INTO tdb_goods_types(type_name,parent_id) VALUES(‘科普‘,14);

 

　从下图中很容易可以看到，‘电子书‘为顶级分类,所以parent_id为0，以此类推….

这时候我们需要查询具体分类以及对应父类，我们就需要假想有两张相同的表，一张是父表(parent)，一张是子表(son)，自己连接自己查询，子表的parent_id = 父表的type_id，子表中的type_name就是子类分类,父表中的type_name就是父类分类名称

SELECT s.type_id AS 分类编号, s.type_name AS 分类名称, p.type_name AS 父类名称 FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;

 

也可以用另一种思路，查看父类，以及对应的子类

SELECT p.type_id AS 分类编号, p.type_name AS 父类名称, s.type_name AS 子类名称 FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id;

 

改进:获取的是子类的数量

 

SELECT p.type_id AS 分类编号, p.type_name AS 父类名称, COUNT(s.type_name) AS 子类数目 FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id ASC;