业务设计-商品表sku通用设计
在设计商品详情页的sku时,可以参考如下设计
ER图:
具体的DDL:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_attribute
-- ----------------------------
DROP TABLE IF EXISTS `t_attribute`;
CREATE TABLE `t_attribute` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`attribute_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '属性名称',
`type` tinyint(1) NULL DEFAULT NULL COMMENT '分类: 0 基础,1销售',
`catetory_id` bigint NOT NULL COMMENT '分类id',
`created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品所有属性定义' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_category
-- ----------------------------
DROP TABLE IF EXISTS `t_category`;
CREATE TABLE `t_category` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`parent_id` bigint NULL DEFAULT NULL COMMENT '父id',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '分类名称',
`created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品分类' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_sku
-- ----------------------------
DROP TABLE IF EXISTS `t_sku`;
CREATE TABLE `t_sku` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`spu_id` int NULL DEFAULT NULL COMMENT 'spu_id',
`price` bigint NULL DEFAULT NULL COMMENT '价格,分',
`created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `spu_id`(`spu_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '库存价格' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_sku_attribute
-- ----------------------------
DROP TABLE IF EXISTS `t_sku_attribute`;
CREATE TABLE `t_sku_attribute` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`sku_id` bigint NULL DEFAULT NULL COMMENT 'sku_id',
`attribute_id` bigint NULL DEFAULT NULL COMMENT '属性id',
`attribute_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '属性值',
`created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '库存属性值' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_spu
-- ----------------------------
DROP TABLE IF EXISTS `t_spu`;
CREATE TABLE `t_spu` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商品名称',
`catetory_id` bigint NULL DEFAULT NULL COMMENT '分类id',
`status` int NULL DEFAULT NULL COMMENT '状态: 0、下架,1、上架',
`created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品信息表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_spu_attribute
-- ----------------------------
DROP TABLE IF EXISTS `t_spu_attribute`;
CREATE TABLE `t_spu_attribute` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`spu_id` int NULL DEFAULT NULL COMMENT 'spu_id',
`attribute_id` bigint NULL DEFAULT NULL COMMENT '属性id',
`created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品基本属性表' ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
模拟数据:
INSERT INTO `t_attribute` (`id`, `attribute_name`, `type`, `catetory_id`, `created_time`) VALUES (1, '品牌', 0, 1, '2024-01-28 15:02:28');
INSERT INTO `t_attribute` (`id`, `attribute_name`, `type`, `catetory_id`, `created_time`) VALUES (2, '机身内存', 0, 1, '2024-01-28 15:02:31');
INSERT INTO `t_attribute` (`id`, `attribute_name`, `type`, `catetory_id`, `created_time`) VALUES (3, '运行内存', 0, 1, '2024-01-28 15:03:01');
INSERT INTO `t_attribute` (`id`, `attribute_name`, `type`, `catetory_id`, `created_time`) VALUES (4, '型号', 1, 1, '2024-01-28 15:03:44');
INSERT INTO `t_attribute` (`id`, `attribute_name`, `type`, `catetory_id`, `created_time`) VALUES (5, '颜色', 1, 1, '2024-01-28 15:03:46');
INSERT INTO `t_category` (`id`, `parent_id`, `name`, `created_time`) VALUES (1, 0, '手机', '2024-01-28 14:58:03');
INSERT INTO `t_category` (`id`, `parent_id`, `name`, `created_time`) VALUES (2, 0, '电脑', '2024-01-28 14:58:07');
INSERT INTO `t_sku` (`id`, `spu_id`, `price`, `created_time`) VALUES (1, 1, 699900, '2024-01-28 15:07:40');
INSERT INTO `t_sku` (`id`, `spu_id`, `price`, `created_time`) VALUES (2, 1, 799900, '2024-01-28 15:07:40');
INSERT INTO `t_sku` (`id`, `spu_id`, `price`, `created_time`) VALUES (3, 2, 299900, '2024-01-28 15:07:40');
INSERT INTO `t_sku_attribute` (`id`, `sku_id`, `attribute_id`, `attribute_value`, `created_time`) VALUES (1, 1, 4, '12G+512G', '2024-01-28 15:09:31');
INSERT INTO `t_sku_attribute` (`id`, `sku_id`, `attribute_id`, `attribute_value`, `created_time`) VALUES (2, 1, 5, '雅丹黑', '2024-01-28 15:09:31');
INSERT INTO `t_sku_attribute` (`id`, `sku_id`, `attribute_id`, `attribute_value`, `created_time`) VALUES (3, 2, 4, '8G+512G', '2024-01-28 15:09:31');
INSERT INTO `t_sku_attribute` (`id`, `sku_id`, `attribute_id`, `attribute_value`, `created_time`) VALUES (4, 2, 5, '雅丹黑', '2024-01-28 15:09:31');
INSERT INTO `t_sku_attribute` (`id`, `sku_id`, `attribute_id`, `attribute_value`, `created_time`) VALUES (5, 3, 4, '8G+512G', '2024-01-28 15:09:31');
INSERT INTO `t_sku_attribute` (`id`, `sku_id`, `attribute_id`, `attribute_value`, `created_time`) VALUES (6, 3, 5, '雅丹黑', '2024-01-28 15:09:31');
INSERT INTO `t_spu` (`id`, `spu_name`, `catetory_id`, `status`, `created_time`) VALUES (1, '华为/HUAWEI Mate 60', 1, 1, '2024-01-28 15:04:46');
INSERT INTO `t_spu` (`id`, `spu_name`, `catetory_id`, `status`, `created_time`) VALUES (2, '华为/HUAWEI Mate 60 Pro', 1, 1, '2024-01-28 15:05:04');
INSERT INTO `t_spu_attribute` (`id`, `spu_id`, `attribute_id`, `created_time`) VALUES (1, 1, 1, '2024-01-28 15:05:23');
INSERT INTO `t_spu_attribute` (`id`, `spu_id`, `attribute_id`, `created_time`) VALUES (2, 1, 2, '2024-01-28 15:05:23');
INSERT INTO `t_spu_attribute` (`id`, `spu_id`, `attribute_id`, `created_time`) VALUES (3, 1, 3, '2024-01-28 15:05:23');
INSERT INTO `t_spu_attribute` (`id`, `spu_id`, `attribute_id`, `created_time`) VALUES (4, 2, 1, '2024-01-28 15:05:23');
INSERT INTO `t_spu_attribute` (`id`, `spu_id`, `attribute_id`, `created_time`) VALUES (5, 2, 2, '2024-01-28 15:05:23');
INSERT INTO `t_spu_attribute` (`id`, `spu_id`, `attribute_id`, `created_time`) VALUES (6, 2, 3, '2024-01-28 15:05:23');
查询 详情信息
SELECT
ts.id,
tc.`name`,
tss.spu_name,
ts.price,
GROUP_CONCAT(tsa.attribute_value)
FROM
t_sku ts
LEFT JOIN t_sku_attribute tsa ON ts.id = tsa.sku_id
LEFT JOIN t_attribute ta ON ta.id = tsa.attribute_id
LEFT JOIN t_category tc ON tc.id = ta.catetory_id
LEFT JOIN t_spu tss ON tss.id = ts.spu_id
AND tss.`status` =1
WHERE 1=1
GROUP BY ts.id,tc.`name`,tss.spu_name
结果