5-13:无限级分类表设计
一. 前言
无限级分类简单来说可以理解为多级分类,比如图书可以分为文学、历史、财经,而历史又可以分为中国历史,外国历史等等,那么无限级分类的数据表应怎么设计呢?
二. 无限分类的数据表设计
mysql>CREATE TABLE tdb_goods_types(
->type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
->type_name VARCHAR(20) NOT NULL,
->parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
-> );
Query OK, 0 rows affected (0.01 sec)
其中第四行记录是父类的id,也就是说它其实是通过自身的连接来实现的。
接来下插入记录(为了方便大家插入,这里不写提示符,直接复制到CMD中即可):
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);
再查看一下表结构:
mysql> SHOW COLUMNS FROM tdb_goods_types;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| type_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| type_name | varchar(20) | NO | | NULL | |
| parent_id | smallint(5) unsigned | NO | | 0 | |
+-----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
再查看一下记录:
mysql> SELECT * FROM tdb_goods_types;
+---------+------------+-----------+
| type_id | type_name | parent_id |
+---------+------------+-----------+
| 1 | 家用电器 | 0 |
| 2 | 电脑、办公 | 0 |
| 3 | 大家电 | 1 |
| 4 | 生活电器 | 1 |
| 5 | 平板电视 | 3 |
| 6 | 空调 | 3 |
| 7 | 电风扇 | 4 |
| 8 | 饮水机 | 4 |
| 9 | 电脑整机 | 2 |
| 10 | 电脑配件 | 2 |
| 11 | 笔记本 | 9 |
| 12 | 超级本 | 9 |
| 13 | 游戏本 | 9 |
| 14 | CPU | 10 |
| 15 | 主机 | 10 |
+---------+------------+-----------+
15 rows in set (0.00 sec)
家用电器和电脑办公的parent_id为0的则代表顶级分类,没有父亲节点
大家电的parent_id为1,代表它的父节点是家用电器,同理
主机的parent_id为10,代表它的父节点是电脑配件,而电脑配件的parent_id是2,代表它的父节点是电脑、办公,以此类推
三. 查找所有分类及其父类
无限级分类的查找主要是通过自身连接来实现的,所谓自身连接就是指数据表对其自身进行连接(注意因为是和自身连接,一定要给表起别名):
mysql> SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;
+---------+------------+------------+
| type_id | type_name | type_name |
+---------+------------+------------+
| 1 | 家用电器 | NULL |
| 2 | 电脑、办公 | NULL |
| 3 | 大家电 | 家用电器 |
| 4 | 生活电器 | 家用电器 |
| 5 | 平板电视 | 大家电 |
| 6 | 空调 | 大家电 |
| 7 | 电风扇 | 生活电器 |
| 8 | 饮水机 | 生活电器 |
| 9 | 电脑整机 | 电脑、办公 |
| 10 | 电脑配件 | 电脑、办公 |
| 11 | 笔记本 | 电脑整机 |
| 12 | 超级本 | 电脑整机 |
| 13 | 游戏本 | 电脑整机 |
| 14 | CPU | 电脑配件 |
| 15 | 主机 | 电脑配件 |
+---------+------------+------------+
15 rows in set (0.00 sec)
这里是一个重难点,很多人到这里就迷糊了,其实可以这样理解:
我们在自身连接的时候,可以将当前数据表看成父表,然后将它自身再看成子表,进行连接,可以按照下图的方式在加深理解:
拿最后一条记录来说,将子表的parent_id 10和父表的type_id作比较,发现父表的电脑配件的type_id 10和子表的parent_id 10相等,于是显示父表中的type_id为10的type_name也就是电脑配件。
而如果只输出p.type_id和p.type_name的结果为:
mysql> SELECT p.type_id,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;
+---------+------------+
| type_id | type_name |
+---------+------------+
| NULL | NULL |
| NULL | NULL |
| 1 | 家用电器 |
| 1 | 家用电器 |
| 3 | 大家电 |
| 3 | 大家电 |
| 4 | 生活电器 |
| 4 | 生活电器 |
| 2 | 电脑、办公 |
| 2 | 电脑、办公 |
| 9 | 电脑整机 |
| 9 | 电脑整机 |
| 9 | 电脑整机 |
| 10 | 电脑配件 |
| 10 | 电脑配件 |
+---------+------------+
15 rows in set (0.00 sec)
这里一定要理解为什么出现NULL,因为前两条parent_id 为0,在父表中找不大type_id为0的字段,所以显示NULL,而如果这里是右外连接,则就不会显示NULL了。
四. 查找所有分类及其子类
接下来我们查找父类和父类以下的子,上面的操作是参照子表进行的,而现在需要参照父表进行:
mysql> SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id;
+---------+------------+-----------+
| type_id | type_name | type_name |
+---------+------------+-----------+
| 1 | 家用电器 | 大家电 |
| 1 | 家用电器 | 生活电器 |
| 2 | 电脑、办公 | 电脑整机 |
| 2 | 电脑、办公 | 电脑配件 |
| 3 | 大家电 | 平板电视 |
| 3 | 大家电 | 空调 |
| 4 | 生活电器 | 电风扇 |
| 4 | 生活电器 | 饮水机 |
| 5 | 平板电视 | NULL |
| 6 | 空调 | NULL |
| 7 | 电风扇 | NULL |
| 8 | 饮水机 | NULL |
| 9 | 电脑整机 | 笔记本 |
| 9 | 电脑整机 | 超级本 |
| 9 | 电脑整机 | 游戏本 |
| 10 | 电脑配件 | CPU |
| 10 | 电脑配件 | 主机 |
| 11 | 笔记本 | NULL |
| 12 | 超级本 | NULL |
| 13 | 游戏本 | NULL |
| 14 | CPU | NULL |
| 15 | 主机 | NULL |
+---------+------------+-----------+
22 rows in set (0.00 sec)
我们也可以将上面的记录修改成显示所有分类及其子类的数目,比如家用电器有两个子类,则显示2,可以用分组来实现这个方法
mysql> SELECT p.type_id,p.type_name,s.type_name 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;
+---------+------------+-----------+
| type_id | type_name | type_name |
+---------+------------+-----------+
| 14 | CPU | NULL |
| 15 | 主机 | NULL |
| 3 | 大家电 | 平板电视 |
| 1 | 家用电器 | 大家电 |
| 5 | 平板电视 | NULL |
| 13 | 游戏本 | NULL |
| 4 | 生活电器 | 电风扇 |
| 2 | 电脑、办公 | 电脑整机 |
| 9 | 电脑整机 | 笔记本 |
| 10 | 电脑配件 | CPU |
| 7 | 电风扇 | NULL |
| 6 | 空调 | NULL |
| 11 | 笔记本 | NULL |
| 12 | 超级本 | NULL |
| 8 | 饮水机 | NULL |
+---------+------------+-----------+
15 rows in set (0.00 sec)
按照id升序排列:
mysql> SELECT p.type_id,p.type_name,s.type_name 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;
+---------+------------+-----------+
| type_id | type_name | type_name |
+---------+------------+-----------+
| 1 | 家用电器 | 大家电 |
| 2 | 电脑、办公 | 电脑整机 |
| 3 | 大家电 | 平板电视 |
| 4 | 生活电器 | 电风扇 |
| 5 | 平板电视 | NULL |
| 6 | 空调 | NULL |
| 7 | 电风扇 | NULL |
| 8 | 饮水机 | NULL |
| 9 | 电脑整机 | 笔记本 |
| 10 | 电脑配件 | CPU |
| 11 | 笔记本 | NULL |
| 12 | 超级本 | NULL |
| 13 | 游戏本 | NULL |
| 14 | CPU | NULL |
| 15 | 主机 | NULL |
+---------+------------+-----------+
15 rows in set (0.00 sec)
显示子类个数:
mysql> SELECT p.type_id,p.type_name,count(s.type_name) AS child_count 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;
+---------+------------+-------------+
| type_id | type_name | child_count |
+---------+------------+-------------+
| 1 | 家用电器 | 2 |
| 2 | 电脑、办公 | 2 |
| 3 | 大家电 | 2 |
| 4 | 生活电器 | 2 |
| 5 | 平板电视 | 0 |
| 6 | 空调 | 0 |
| 7 | 电风扇 | 0 |
| 8 | 饮水机 | 0 |
| 9 | 电脑整机 | 3 |
| 10 | 电脑配件 | 2 |
| 11 | 笔记本 | 0 |
| 12 | 超级本 | 0 |
| 13 | 游戏本 | 0 |
| 14 | CPU | 0 |
| 15 | 主机 | 0 |
+---------+------------+-------------+
15 rows in set (0.00 sec)