MYSQL 商城系统设计 商品数据表的设计 商品 商品类别 商品选项卡 多表查询

介绍

在开发商品模块时，通常使用分表的方式进行查询以及关联。在通过表连接的方式进行查询。每个商品都有不同的分类，每个不同分类下面都有商品规格可以选择，每个商品分类对应商品规格都有自己的价格和库存。在实际的开发中应该给这些表进行外键的约束避免垃圾无用的数据。

最终效果

{"id": 541834,"productName": "Kiei","product": { //商品的全部信息"productId": 541834,"productName": "Kiei","price": 416.83,"description": "Navicat Cloud could not connect and access your databases. By which it means, it could only store your connection settings, queries, model files, and virtual group; your database passwords            ","createdAt": "2010-08-07T23:54:41"},"classification": { //商品的类别"id": 1,"majorCategories": {"id": 1,"majorCategories": "家电","smallCategory": {"id": 2,"smallCategory": "空调"}}},"categories": [ //商品购买时选项名称{"categoryId": 228788,"categoryName": "运动与户外用品","sortOrder": 1,//前端根据这个字段排序"options": [ //商品具体购买项{"optionId": 375246,"productId": 541834,"categoryId": 228788,"optionValue": "White Smoke","sortOrder": null //前端根据这个字段排序}]},{"categoryId": 237990,"categoryName": "Apps & Games","sortOrder": 0,  //前端根据这个字段排序"options": [{"optionId": 118132,"productId": 541834,"categoryId": 237990,"optionValue": "雾玫瑰色","sortOrder": null //前端根据这个字段排序}]}]
}

创建商品表

category_id关联的是 商品大类 和 商品小类 的中间表字段

/*Navicat Premium Dump SQLSource Server         : localSource Server Type    : MySQLSource Server Version : 80012 (8.0.12)Source Host           : localhost:3306Source Schema         : java-testTarget Server Type    : MySQLTarget Server Version : 80012 (8.0.12)File Encoding         : 65001Date: 29/01/2025 18:19:06
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`  (`product_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品ID',`product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '商品名称',`price` decimal(10, 2) NULL DEFAULT NULL COMMENT '商品价格',`description` text CHARACTER SET utf8 COLLATE utf8_bin NULL COMMENT '商品描述',`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',`category_id` int(11) NULL DEFAULT NULL COMMENT '类目ID',PRIMARY KEY (`product_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1541834 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;

创建商品大类表

该表只存储大类的商品信息不存其他信息，而其他商品信息应该保存在中间表当中。这样是为了防止结构的耦合。

CREATE TABLE `java-test`.`Untitled`  (`id` int(11) NOT NULL AUTO_INCREMENT,`major_categories` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '大类',PRIMARY KEY (`id`) USING BTREE,INDEX `idx_id_major_categories`(`id`, `major_categories`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

创建商品小类表

该表只存储商品的小类

CREATE TABLE `java-test`.`Untitled`  (`id` int(11) NOT NULL AUTO_INCREMENT,`small_category` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '小类名称',PRIMARY KEY (`id`) USING BTREE,INDEX `idx_id_small_category`(`id`, `small_category`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

创建大类小类中间表

ID字段被商品表的category_id关联。

CREATE TABLE `java-test`.`Untitled`  (`id` int(11) NOT NULL,`major_categories_id` int(11) NULL DEFAULT NULL COMMENT '大类ID',`small_category_id` int(11) NULL DEFAULT NULL COMMENT '小类ID',PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Fixed;

创建套餐表

CREATE TABLE `java-test`.`Untitled`  (`category_id` int(11) NOT NULL AUTO_INCREMENT,`category_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '套餐类别',PRIMARY KEY (`category_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 516019 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

套餐选项表

套餐选项包含商品的ID和套餐的ID

CREATE TABLE `java-test`.`Untitled`  (`option_id` int(11) NOT NULL AUTO_INCREMENT,`product_id` int(11) NULL DEFAULT NULL COMMENT '商品ID',`category_id` int(11) NULL DEFAULT NULL COMMENT '套餐ID',`option_value` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '套餐名称',`sort_order` int(11) NULL DEFAULT NULL COMMENT '排序字段',PRIMARY KEY (`option_id`) USING BTREE,INDEX `idx_po_product_category`(`product_id` ASC, `category_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3000005 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

套餐中间表

CREATE TABLE `java-test`.`Untitled`  (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',`category_id` int(11) NOT NULL COMMENT '商品套餐',`sort_order` int(11) NOT NULL COMMENT '套餐排序',PRIMARY KEY (`id`, `category_id`) USING BTREE,INDEX `idx_category_sort_order`(`category_id` ASC, `sort_order` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3000001 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

查询语句

要求查询出 商品信息 商品大类小类 商品套餐 套餐里的选项

 SELECTp.product_id,p.product_name,p.price,p.description,p.created_at,ma.id major_categories_id ,ma.major_categories,sm.small_category,sm.id small_category_id,c.id classification_id,pc.category_name,pc.category_id,po.option_value,po.option_id,po.sort_order option_index,so.sort_order category_index
FROM products pLEFT JOIN classification c ON p.category_id = c.id LEFT JOIN major_categories ma ON c.major_categories_id = ma.id  LEFT JOIN small_category sm ON c.small_category_id = sm.id  LEFT JOIN product_options po ON p.product_id = po.product_idLEFT JOIN product_categories pc ON po.category_id = pc.category_idLEFT JOIN category_association so ON so.category_id = po.category_id
WHERE p.product_id = 541834;

查询性能