商品相关数据库设计

首先举个例子，苹果手机有内存、CPU、颜色等属性。不同的商品，他的规格属性并不相同，我们无法用固定的属性去设计表，那么如何让这些属性“动态化”呢？电商领域经过几十年的发展，以及总结出数据设计方案，那就是SKU与SPU。

SPU：标准产品单位，描述一个商品的各种特性。举例：苹果12就是一个SPU

SKU：库存进出计量的单位，SKU是物理上不可分割的最小单元。举例：紫色8+128的苹果12、蓝色6+64的苹果12

思考：SKU表该如何设计，将所有的属性都设计成一个字段吗？我们来看一下如果这样设计会出现什么样的现象。

主键	商品名称	CPU	内存	……	保质期	尺码
1	苹果12	a14	8GB	……	无	无
2	六个核桃	无	无	……	60天	无
3	小米T恤	无	无	……	无	XXL

结论：首先就是字段冗余现象，其次大量不需要的属性非常臃肿，这个数据表如果这样设计，那真是太糟糕了。

如何设计品类参数？参数与SKU的关系？

• 从品类表开始看，一条品类表信息对应多条参数表，例如手机是一个品类，对应CPU、内存、尺寸、电池等参数
• 一个产品表对应一个品类表，一个产品可以有多个商品。

品类表设计

为了更好的理解Sku与Spu我们需要进行一个过度，先来看看，在设计spu表前需要先设计出哪些表。

首先就是品类表，例如淘宝界面，品类表一般就是类似淘宝首页左侧的分类

数据库设计如下

名称	类型	长度	注释
id	int	11	主键
spg_id	int	11	品类编号
name	varchar	200	品类名称

SQL语句如下

CREATE TABLE `t_spec_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `spg_id` int(11) NOT NULL COMMENT '品类编号',
  `name` varchar(255) DEFAULT NULL COMMENT '品类名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_id` (`id`) USING BTREE COMMENT '主键唯一索引',
  UNIQUE KEY `unq_spg_id` (`spg_id`) USING BTREE COMMENT '品类编号唯一索引',
  KEY `idex_speg_id` (`spg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

常见问题与注意事项：

1. 主键类型选择，我这里是单体环境，因此设计成自增（主要是因为测试方便，偷个懒）。分布式环境下，MySQL如果做集群，就需要采用 varchar类型存放分布式ID，具体可用UUID或者雪花算法等生成ID。
2. 有了ID为什么还需要使用品类编号？ID是无意义的，品类编号可设计成有意义的，例如前四位是有意义的，如1-1000代表电子类产品，1001-2000代表服装类产品，具体含义自行设计。
3. 注意给主键和品类编号添加唯一索引。
4. 为什么使用utf8mb4而不是utf8，因为utf8不是真正的utf8。具体见博文：点击前往 (opens new window)
5. 表名为什么以“t”开头，这里的“t”代表表，“v”代表视图，有的地方设计成“tb”都是ok的。

插入几条数据：

INSERT INTO t_spec_group (spg_id, `name`) VALUES (10001, '手机');
INSERT INTO t_spec_group (spg_id, `name`) VALUES (10002, '数据线');
INSERT INTO t_spec_group (spg_id, `name`) VALUES (10003, '充电器');
INSERT INTO t_spec_group (spg_id, `name`) VALUES (20001, '毛衣');
INSERT INTO t_spec_group (spg_id, `name`) VALUES (20002, 'T恤');

参数表设计

什么是参数表，用来干什么的？当我们点击某个品类后，界面会显示该品类的参数，例如淘宝此界面左侧的品牌、手机类型、机身内存ROM……

直接上SQL语句

CREATE TABLE `t_spec_param` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `spp_id` int(11) NOT NULL COMMENT '参数编号',
  `spg_id` int(11) NOT NULL COMMENT '品类编号',
  `name` varchar(200) NOT NULL COMMENT '参数名称',
  `numeric` tinyint(1) NOT NULL COMMENT '是否为数字参数',
  `unit` varchar(200) DEFAULT NULL COMMENT '单位名称',
  `generic` tinyint(1) NOT NULL COMMENT '是否为通用参数',
  `searching` tinyint(1) DEFAULT NULL COMMENT '可搜索？',
  `segments` varchar(500) DEFAULT NULL COMMENT '参数值',
  PRIMARY KEY (`id`),
  KEY `ids_spg_id` (`spg_id`),
  KEY `ids_spp_id` (`spp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COMMENT='参数表';

数据：

INSERT INTO t_spec_param (spp_id, spg_id, `name`, `numeric`, unit, generic, searching, segments) VALUES (10001, 1, 'CPU', 0, NULL, 1, 0, NULL);
INSERT INTO t_spec_param (spp_id, spg_id, `name`, `numeric`, unit, generic, searching, segments) VALUES (10001, 2, '运存', 1, 'GB', 1, 1, NULL);
INSERT INTO t_spec_param (spp_id, spg_id, `name`, `numeric`, unit, generic, searching, segments) VALUES (10001, 3, '屏幕尺寸', 1, '英寸', 1, 1, NULL);
INSERT INTO t_spec_param (spp_id, spg_id, `name`, `numeric`, unit, generic, searching, segments) VALUES (10001, 4, '内存', 1, 'GB', 1, 1, NULL);
INSERT INTO t_spec_param (spp_id, spg_id, `name`, `numeric`, unit, generic, searching, segments) VALUES (10002, 5, '长度', 1, '米', 1, 1, NULL);
INSERT INTO t_spec_param (spp_id, spg_id, `name`, `numeric`, unit, generic, searching, segments) VALUES (10003, 6, '功率', 1, '瓦', 1, 1, NULL);
INSERT INTO t_spec_param (spp_id, spg_id, `name`, `numeric`, unit, generic, searching, segments) VALUES (10001, 7, '材质', 0, NULL, 1, 1, '纯毛/混纺/化纤');

设计品牌和分类关系

品牌表建表SQL语句

CREATE TABLE `t_brand` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(150) NOT NULL COMMENT '品牌名称',
  `image` varchar(500) DEFAULT NULL COMMENT '商标图片',
  `letter` char(1) NOT NULL COMMENT '品牌首字母',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_name` (`name`),
  KEY `idx_letter` (`letter`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

填充数据

INSERT INTO t_brand (`name`, image, letter) VALUES ('联想', 'https://p1.lefile.cn/product/adminweb/2018/12/13/2d4534f6-29f3-4a05-8e68-97d9904c7bd2.png', 'L');
INSERT INTO t_brand (`name`, image, letter) VALUES ('小米', 'https://s02.mifile.cn/assets/static/image/logo-mi2.png', 'X');
INSERT INTO t_brand (`name`, image, letter) VALUES ('苹果', 'https://dss2.baidu.com/6ONYsjip0QIZ8tyhnq/it/u=3652116195,1176156622&fm=84&app=100&f=JPEG?w=255&h=255', 'P');
INSERT INTO t_brand (`name`, image, letter) VALUES ('华为', 'https://www.huawei.com//cnwww-file.huawei.com/-/media/corporate/images/home/logo/huawei_logo.png', 'H');
INSERT INTO t_brand (`name`, image, letter) VALUES ('酷派', 'https://www.coolpad.com/static/img/logo-white.91219c3.png', 'K');
INSERT INTO t_brand (`name`, image, letter) VALUES ('魅族', 'https://pics3.baidu.com/feed/4d086e061d950a7be8f6e190f25cc7dff3d3c93c.png?token=7a361d9272d74ef86d7a84dbf413c205', 'M');

商品分类表

CREATE TABLE `t_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(200) NOT NULL COMMENT '分类名称',
  `parent_id` int(11) unsigned zerofill NOT NULL DEFAULT '00000000000' COMMENT '父节点ID',
  `sort` int(11) DEFAULT NULL COMMENT '排名指数',
  PRIMARY KEY (`id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_sort` (`sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表';

我们模仿这个页面填充数据

INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('手机/数码/配件', 0, 1);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('电脑/办公/外设', 0, 2);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('厨卫电器 生活电器', 0, 3);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('手机通讯', 1, 1);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('手机配件', 1, 2);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('运营商', 1, 3);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('摄影摄像', 1, 4);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('数码配件', 1, 5);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('智能设备', 1, 6);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('手机', 4, 1);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('移动电源', 5, 1);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('手机耳机', 5, 2);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('蓝牙耳机', 5, 3);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('保护壳/套', 5, 4);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('手机存储卡', 5, 5);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('手机贴膜', 5, 6);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('手机电池', 5, 7);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('合约购机', 6, 1);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('办手机卡', 6, 2);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('装宽带', 6, 3);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('单反相机', 7, 1);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('数码相机', 7, 2);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('摄像机', 7, 3);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('拍立得', 7, 4);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('镜头', 7, 5);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('航拍摄像', 7, 6);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('运动相机', 7, 7);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('数码相框', 7, 8);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('电脑整机', 2, 1);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('DIY硬件', 2, 2);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('存储设备', 2, 3);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('电脑外设', 2, 4);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('办公打印', 2, 5);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('笔记本', 29, 1);
INSERT INTO t_category (`name`, `parent_id`, `sort`) VALUES ('平板电脑', 29, 2);

思考商品分类表，为什么要这么设计？

1. 观察京东、天猫、苏宁等电商购物网站，发现分类大多为三级分类。那我们可不可以设计三个字段为布尔类型，分别代表三级目录？答案肯定是不行的，第一表结构混乱，第二后期产品可能会发生更改，改成二级、四级都有可能。产品快做完了要改动数据库，这可太糟糕了。
2. 我们设置父节点ID，父节点ID代表“我”的上一级是谁。那么一级目录怎样表示？答案：父节点ID为0代表一级目录，因为一般情况下主键都是正整数。
3. 排名指数相当于搜索的权重，指数越低，搜索的结果越靠上，用后端的话来说就是遍历时对排名指数进行排序。
4. 给字段加上索引，一般情况下是我们需要根据该字段查询数据，作用是优化整体查询速度。

分类品牌关系表

分类与品牌表关系，一个品牌有多个分类，一个分类也可以有多个品牌，因此是多对多关系。注意主键是复合关系。

CREATE TABLE `t_category_brand` (
  `category_id` int(11) NOT NULL COMMENT '分类id',
  `brand_id` int(11) NOT NULL COMMENT '品牌id',
  PRIMARY KEY (`category_id`,`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分类品牌关系表';

分类数据填充

INSERT INTO t_category_brand (`category_id`, `brand_id`) VALUES (10, 1);
INSERT INTO t_category_brand (`category_id`, `brand_id`) VALUES (10, 2);
INSERT INTO t_category_brand (`category_id`, `brand_id`) VALUES (10, 3);
INSERT INTO t_category_brand (`category_id`, `brand_id`) VALUES (10, 4);
INSERT INTO t_category_brand (`category_id`, `brand_id`) VALUES (10, 5);
INSERT INTO t_category_brand (`category_id`, `brand_id`) VALUES (10, 6);
INSERT INTO t_category_brand (`category_id`, `brand_id`) VALUES (10, 7);
INSERT INTO t_category_brand (`category_id`, `brand_id`) VALUES (10, 8);
INSERT INTO t_category_brand (`category_id`, `brand_id`) VALUES (10, 9);
INSERT INTO t_category_brand (`category_id`, `brand_id`) VALUES (10, 10);

产品表和商品表(sku与spu)

上面写了那么多其实都是为了产品表和商品表做铺垫的，先来看一下产品表的E-R图

建表语句

CREATE TABLE `t_spu` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `title` varchar(200) NOT NULL COMMENT '标题',
  `sub_title` varchar(200) DEFAULT NULL COMMENT '副标题',
  `category_id` int(10) unsigned NOT NULL COMMENT '分类ID',
  `brand_id` int(10) unsigned DEFAULT NULL COMMENT '品牌ID',
  `spg_id` int(10) unsigned NOT NULL COMMENT '品类ID',
  `saleable` tinyint(1) NOT NULL COMMENT '是否上架',
  `valid` tinyint(1) NOT NULL COMMENT '是否删除',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_brand_id` (`brand_id`) USING BTREE,
  KEY `idx_category_id` (`category_id`) USING BTREE,
  KEY `idx_spg_id` (`spg_id`) USING BTREE,
  KEY `idx_saleable` (`saleable`) USING BTREE,
  KEY `idx_valid` (`valid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品表';

1. valid 表示逻辑删除，之所以不使用物理删除原因很多。最主要的原因是牵连信息太多，如订单信息等，删除后影响大，数据量大时，删除是一个非常消耗性能的操作，修改要好很多。

2. 有读者问，应该会根据标题和副标题查询商品，为什么不给标题副标题设置索引。

   答案是，后面会使用分词技术对标题副标题进行分词，这里添加索引的意义不大，通过海量查询发现，对标题副标题设置索引速度变化不大，反而会影响增删改的速度。

我们向spu表添加一条数据

INSERT INTO t_spu 
(title, sub_title, category_id, brand_id, spg_id, saleable, valid, create_time, last_update_time) 
VALUES 
('iPhone 12', '【享3期免息 用建行分期再减200】A14仿生芯片 5G速度 瓷晶面板防跌落 航空级铝金属边框超漂亮', 10, 3, 1, 1, 1, '2021-07-19 19:38:52', '2021-07-19 19:38:52');

我们来分析一下这个表中的数据：

1. category_id：是分类表t_category中的id
2. brand_id：是品牌表t_brand中的id
3. spg_id：是品类表t_spec_group中的id

商品表ER图如下

商品表建表SQL语句如下

CREATE TABLE `t_sku` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `spu_id` int(10) unsigned NOT NULL COMMENT '产品ID',
  `title` varchar(200) NOT NULL COMMENT '商品标题',
  `images` json DEFAULT NULL COMMENT '商品图片',
  `price` decimal(10,2) unsigned NOT NULL COMMENT '价格',
  `param` json NOT NULL COMMENT '参数',
  `saleable` tinyint(1) NOT NULL COMMENT '是否上架',
  `valid` tinyint(1) NOT NULL COMMENT '是否有效',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_spu_id` (`spu_id`) USING BTREE,
  KEY `idx_saleable` (`saleable`) USING BTREE,
  KEY `idx_valid` (`valid`) USING BTREE,
  FULLTEXT KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

1. 价格可根据业务需求设置，可以设置平常价/打折价两个价格字段，根据业务灵活改变
2. 价格设置成decimal类型，不可设置成浮点型，double或者float会出现精度丢失的情况

来看下sku表中的数据：

主要是 images 和 param两个json格式是数据有疑问，我们来分析一下

// images
{
	"desc": ["http://127.0.0.1/3.jpg", "http://127.0.0.1/4.jpg"],
	"facade": ["http://127.0.0.1/1.jpg", "http://127.0.0.1/2.jpg"]
}
// param
{
	"CPU": "A14",
	"内存": 128,
	"电池": 3008,
	"运存": 8,
	"屏幕尺寸": 6.1
}

1. images中，facade表示展示的大图，desc是商品详情页的图片
2. param中的数据大家熟悉吗？它是参数表 t_spec_param 中的属性，我们通过品类表t_spec_group中手机对应的spg_id找到 t_spec_param 中所有手机的属性，通过前后端交互转换成json数据防止到数据库中。