实现无限级分类的数据库设计及sql查询

创建表

DROP TABLE IF EXISTS `infinite_classification`;
CREATE TABLE `infinite_classification` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `pid` int(11) unsigned DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of infinite_classification
-- ----------------------------
BEGIN;
INSERT INTO `infinite_classification` VALUES (1, '前端', 0);
INSERT INTO `infinite_classification` VALUES (2, '后端', 0);
INSERT INTO `infinite_classification` VALUES (3, '移动', 0);
INSERT INTO `infinite_classification` VALUES (4, 'HTML', 1);
INSERT INTO `infinite_classification` VALUES (5, 'JS', 1);
INSERT INTO `infinite_classification` VALUES (6, '小程序', 1);
INSERT INTO `infinite_classification` VALUES (7, 'JAVA', 2);
INSERT INTO `infinite_classification` VALUES (8, 'PHP', 2);
INSERT INTO `infinite_classification` VALUES (9, 'Go', 2);
INSERT INTO `infinite_classification` VALUES (10, 'Android', 3);
INSERT INTO `infinite_classification` VALUES (11, 'ios', 3);
INSERT INTO `infinite_classification` VALUES (12, 'WEEX', 3);
INSERT INTO `infinite_classification` VALUES (13, 'css', 4);
INSERT INTO `infinite_classification` VALUES (14, 'Sass', 4);
INSERT INTO `infinite_classification` VALUES (15, 'jQuery', 5);
INSERT INTO `infinite_classification` VALUES (16, 'vue', 5);
INSERT INTO `infinite_classification` VALUES (17, 'React', 5);
COMMIT;

查询数据

mysql> select * from infinite_classification
    -> ;
+----+-----------+------+
| id | name      | pid  |
+----+-----------+------+
|  1 | 前端      |    0 |
|  2 | 后端      |    0 |
|  3 | 移动      |    0 |
|  4 | HTML      |    1 |
|  5 | JS        |    1 |
|  6 | 小程序    |    1 |
|  7 | JAVA      |    2 |
|  8 | PHP       |    2 |
|  9 | Go        |    2 |
| 10 | Android   |    3 |
| 11 | ios       |    3 |
| 12 | WEEX      |    3 |
| 13 | css       |    4 |
| 14 | Sass      |    4 |
| 15 | jQuery    |    5 |
| 16 | vue       |    5 |
| 17 | React     |    5 |
+----+-----------+------+
17 rows in set (0.00 sec)

联合查询join

mysql> SELECT t1.name AS lev1,t2.name as lev2,t3.name AS lev3 FROM infinite_classification as t1 LEFT JOIN infinite_classification AS t2 ON t2.pid = t1.id LEFT JOIN infinite_classification AS t3 ON t3.pid = t2.id;
+-----------+-----------+--------+
| lev1      | lev2      | lev3   |
+-----------+-----------+--------+
| 前端      | HTML      | css    |
| 前端      | HTML      | Sass   |
| 前端      | JS        | jQuery |
| 前端      | JS        | vue    |
| 前端      | JS        | React  |
| 前端      | 小程序    | NULL   |
| 后端      | JAVA      | NULL   |
| 后端      | PHP       | NULL   |
| 后端      | Go        | NULL   |
| 移动      | Android   | NULL   |
| 移动      | ios       | NULL   |
| 移动      | WEEX      | NULL   |
| HTML      | css       | NULL   |
| HTML      | Sass      | NULL   |
| JS        | jQuery    | NULL   |
| JS        | vue       | NULL   |
| JS        | React     | NULL   |
| 小程序    | NULL      | NULL   |
| JAVA      | NULL      | NULL   |
| PHP       | NULL      | NULL   |
| Go        | NULL      | NULL   |
| Android   | NULL      | NULL   |
| ios       | NULL      | NULL   |
| WEEX      | NULL      | NULL   |
| css       | NULL      | NULL   |
| Sass      | NULL      | NULL   |
| jQuery    | NULL      | NULL   |
| vue       | NULL      | NULL   |
| React     | NULL      | NULL   |
+-----------+-----------+--------+
29 rows in set (0.00 sec)

mysql> SELECT t1.name AS lev1,t2.name as lev2,t3.name AS lev3 FROM infinite_classification as t1 LEFT JOIN infinite_classification AS t2 ON t2.pid = t1.id LEFT JOIN infinite_classification AS t3 ON t3.pid = t2.id where t3.id = 15;
+--------+------+--------+
| lev1   | lev2 | lev3   |
+--------+------+--------+
| 前端   | JS   | jQuery |
+--------+------+--------+
1 row in set (0.00 sec)

mysql>