• 注册
  • 赞助本站

    • 支付宝
    • 微信
    • QQ

    感谢一直支持本站的所有人!

    • 查看作者
    • 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)

      这里是一个重难点,很多人到这里就迷糊了,其实可以这样理解:

      我们在自身连接的时候,可以将当前数据表看成父表,然后将它自身再看成子表,进行连接,可以按照下图的方式在加深理解:

      5-13:无限级分类表设计

      拿最后一条记录来说,将子表的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)

       

    • 0
    • 0
    • 0
    • 3k
    • 请登录之后再进行评论

      登录
    • 单栏布局 侧栏位置: