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 SQL
Source Server : local
Source Server Type : MySQL
Source Server Version : 80012 (8.0.12)
Source Host : localhost:3306
Source Schema : java-test
Target Server Type : MySQL
Target Server Version : 80012 (8.0.12)
File Encoding : 65001
Date: 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;
查询语句
要求查询出 商品信息 商品大类小类 商品套餐 套餐里的选项
SELECT
p.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 p
LEFT 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_id
LEFT JOIN product_categories pc ON po.category_id = pc.category_id
LEFT JOIN category_association so ON so.category_id = po.category_id
WHERE p.product_id = 541834;
查询性能
