mysql无限分类sku_实现无限级分类的数据库设计及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>