15)mysql无限分类表设计和优化

• 参考：平然老师的《与MySQL的零距离接触》5-13小节

---

为什么要有无限分类表设计？
在实际的开发中，比如商品类别，其有很多延伸。比如图书，可以分类为小说，历史，哲学，音乐等，而小说里面又可以分为科幻小说，游戏小说等等，还可以再一级一级继续分，那么这种分类就属于无限分类。
理论上可以设计很多张表，但随着分类的逐步增多，表的数目会越来越多，所以要有无限分类表的设计

语法结构如下：
type_id: 类型id
type_name: 类型名字
parent_id: 父类id

CREATE TABLE tdb_goods_types(
type_id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id TINYINT UNSIGNED NOT NULL DEFAULT 0
);

这类表至少有3个字段，是通过对自身的连接来实现的。
插入一些数据

  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);

如下图：

家电，电脑后面的父id为0，表明该分类是顶级分类。大家电的父id为1，表明他是家电电器的下一级分类

要做查找的话，我们就需要通过自身连接来实现，所谓自身连接，就是指数据表自己来连接自己。
下面做一个简单的演示，比如要查找所有子类的父类，比如家用电器他的父类是什么？如果没有就是NULL
那么这就需要大家有一点想象力，就是在这张表的右侧还有一张跟他结构完全一样的数据表。假设我们把左边的当成父表，右边的当成子表，那么我们左边的父表中的parent_id这个字段就没有什么用了，如下图示：

child：儿子； parent：父亲
查询子表所有父类，命令如下

SELECT c.type_id,c.type_name,p.type_name AS 父类名字
FROM tdb_goods_types c 
LEFT JOIN tdb_goods_types p ON p.type_id=c.parent_id

如果要查询多级分类，可以用CTE（公共表达式），mysql8.0之后才有的功能
例如，查询第3级目录的父类名字和爷类名字

WITH cte_name AS (
SELECT c.type_id,c.type_name,p.type_name AS p_name,p.parent_id
FROM tdb_goods_types c 
LEFT JOIN tdb_goods_types p ON p.type_id=c.parent_id
)
SELECT cte_name.type_id,cte_name.type_name,cte_name.p_name,p.type_name AS gp_name
FROM cte_name 
LEFT JOIN tdb_goods_types p ON p.type_id=cte_name.parent_id

查询结果如下：

如果还要在深入，查询更多级，可以把cte变成 WITH RECURSIVE cte_name ，这样cte可以自引用，不过不建议这么做，最好是把数据查询出来，在mysql外面实现，减轻mysql的负担

---

---

反过来，要查找父类的子类也一样，命令如下

SELECT p.type_id,p.type_name,c.type_name
FROM tdb_goods_types p
LEFT JOIN tdb_goods_types c ON c.parent_id=p.type_id

结果如下：

再做下分组

SELECT p.type_id,p.type_name,c.type_name,COUNT(c.type_name)
FROM tdb_goods_types p
LEFT JOIN tdb_goods_types c ON c.parent_id=p.type_id
GROUP BY p.type_name
ORDER BY p.type_id

优化

上面中，如果目录层次太多，比如有10层，这样要通过 sql 查找出所有的目录层次会变得太繁琐，sql 查询语句也难写，因此在上面的基础上做进一步优化，如下：

1. 将 parent_id  改为 json 格式
2. 查询出来的结果按 id 排序，即为目录层次排序

表格如下：
CREATE TABLE `tdb_goods_types` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `type_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '',
  `parent_id` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入内容如下图：

这样，3-7目录层次分别是：
3. 家用电器 --> 大家电
4. 家用电器 --> 生活电器
5. 家用电器 --> 大家电 --> 平板电视
6. 家用电器 --> 大家电 --> 空调
7. 家用电器 --> 大家电 --> 生活电器 --> 平板电视 --> 电风扇

每次查询目录，需要先查询出改分类的 parent_id，
再去查询 parent_id 对应的类型，按 id 排序，即为目录层次