常见电商项⽬的数据库表设计（MySQL版）转⾃：t/developer/article/1164332
简介：
⽬的：
电商常⽤功能模块的数据库设计
常见问题的数据库解决⽅案
环境：
MySQL5.7
图形客户端，SQLyog
Linux
模块：
⽤户：注册、登陆
商品：浏览、管理
订单：⽣成、管理
仓配：库存、管理
电商实例数据库结构设计
电商项⽬⽤户模块
⽤户表涉及的实体
改进1：第三范式：将依赖传递的列分离出来。⽐如：登录名<-⽤户级别<-级别积分上限，级别积分下限
改进2：尽量做到冷热数据的分离，减⼩表的宽度
⽤户登录表(customer_login)
CREATE TABLE customer_login(
customer_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '⽤户ID',
login_name VARCHAR(20) NOT NULL COMMENT '⽤户登录名',
password CHAR(32) NOT NULL COMMENT 'md5加密的密码',
user_stats TINYINT NOT NULL DEFAULT 1 COMMENT '⽤户状态',
modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间', PRIMARY KEY pk_customerid(customer_id)
) ENGINE = innodb COMMENT '⽤户登录表'
⽤户信息表(customer_inf)
CREATE TABLE customer_inf(
customer_inf_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '⾃增主键ID',
customer_id INT UNSIGNED NOT NULL COMMENT 'customer_login表的⾃增ID',mysql数据库迁移命令
customer_name VARCHAR(20) NOT NULL COMMENT '⽤户真实姓名',
identity_card_type TINYINT NOT NULL DEFAULT 1 COMMENT '证件类型：1 ⾝份证，2 军官证，3 护照',
identity_card_no VARCHAR(20) COMMENT '证件号码',
mobile_phone INT UNSIGNED COMMENT '⼿机号',
customer_email VARCHAR(50) COMMENT '邮箱',
gender CHAR(1) COMMENT '性别',
user_point INT NOT NULL DEFAULT 0 COMMENT '⽤户积分',
register_time TIMESTAMP NOT NULL COMMENT '注册时间',
birthday DATETIME COMMENT '会员⽣⽇',
customer_level TINYINT NOT NULL DEFAULT 1 COMMENT '会员级别：1 普通会员，2 青铜，3⽩银，4黄⾦，5钻⽯',
user_money DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '⽤户余额',
modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间', PRIMARY KEY pk_customerinfid(customer_inf_id)
) ENGINE = innodb COMMENT '⽤户信息表';
⽤户级别表(customerlevelinf)
CREATE TABLE customer_level_inf(
customer_level TINYINT NOT NULL AUTO_INCREMENT COMMENT '会员级别ID',
level_name VARCHAR(10) NOT NULL COMMENT '会员级别名称',
min_point INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '该级别最低积分',
max_point INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '该级别最⾼积分',
modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间', PRIMARY KEY pk_levelid(customer_level)
)
ENGINE = innodb COMMENT '⽤户级别信息表';
⽤户地址表(customer_addr)
CREATE TABLE customer_addr(
customer_addr_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '⾃增主键ID',
customer_id INT UNSIGNED NOT NULL COMMENT 'customer_login表的⾃增ID',
zip SMALLINT NOT NULL COMMENT '邮编',
province SMALLINT NOT NULL COMMENT '地区表中省份的ID',
city SMALLINT NOT NULL COMMENT '地区表中城市的ID',
district SMALLINT NOT NULL COMMENT '地区表中的区ID',
address VARCHAR(200) NOT NULL COMMENT '具体的地址门牌号',
is_default TINYINT NOT NULL COMMENT '是否默认',
modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间', PRIMARY KEY pk_customeraddid(customer_addr_id)
) ENGINE = innodb COMMENT '⽤户地址表';
⽤户积分⽇志表(customerpointlog)
CREATE TABLE customer_point_log(
point_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '积分⽇志ID',
customer_id INT UNSIGNED NOT NULL COMMENT '⽤户ID',
source TINYINT UNSIGNED NOT NULL COMMENT '积分来源：0订单，1登陆，2活动',
refer_number INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '积分来源相关编号',
change_point SMALLINT NOT NULL DEFAULT 0 COMMENT '变更积分数',
create_time TIMESTAMP NOT NULL COMMENT '积分⽇志⽣成时间',
PRIMARY KEY pk_pointid(point_id)
) ENGINE = innodb COMMENT '⽤户积分⽇志表';
⽤户余额变动表(customerbalancelog)
CREATE TABLE customer_balance_log(
balance_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '余额⽇志ID',
customer_id INT UNSIGNED NOT NULL COMMENT '⽤户ID',
source TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '记录来源：1订单，2退货单',
source_sn INT UNSIGNED NOT NULL COMMENT '相关单据ID',
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录⽣成时间',
amount DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '变动⾦额',
PRIMARY KEY pk_balanceid(balance_id)
)
ENGINE = innodb COMMENT '⽤户余额变动表';
⽤户登陆⽇志表(customerloginlog)
CREATE TABLE customer_login_log(
login_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '登陆⽇志ID',
customer_id INT UNSIGNED NOT NULL COMMENT '登陆⽤户ID',
login_time TIMESTAMP NOT NULL COMMENT '⽤户登陆时间',
login_ip INT UNSIGNED NOT NULL COMMENT '登陆IP',
login_type TINYINT NOT NULL COMMENT '登陆类型：0未成功，1成功',
PRIMARY KEY pk_loginid(login_id)
) ENGINE = innodb COMMENT '⽤户登陆⽇志表';
Hash分区表
分区表特点：逻辑上为⼀个表，在物理上存储在多个⽂件中
CREATE TABLE customer_login_log(
login_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '登陆⽇志ID',
customer_id INT UNSIGNED NOT NULL COMMENT '登陆⽤户ID',
login_time TIMESTAMP NOT NULL COMMENT '⽤户登陆时间',
login_ip INT UNSIGNED NOT NULL COMMENT '登陆IP',
login_type TINYINT NOT NULL COMMENT '登陆类型：0未成功，1成功',
PRIMARY KEY pk_loginid(login_id)
) ENGINE = innodb COMMENT '⽤户登陆⽇志表'
PARTITION BY HASH(customer_id) PARTITIONS 4;
区别就在于加了PARTITION这个命令。⽂件结构上的区别
普通表结构:
customer_login_log.frm
customer_login_log.ibd
分区表结构:
customer_login_log.frm
customer_login_log#P#p0.ibd
customer_login_log#P#p1.ibd
customer_login_log#P#p2.ibd
customer_login_log#P#p3.ibd
按HASH分区的特点
根据MOD（分区建，分区数）的值把数据⾏存储到表的不同分区
数据可以平均的分布在各个分区中
HASH分区的键值必须是⼀个INT类型的值，或是通过函数可以转为INT类型⽐如UNIX_TIMESTAMP(login_time)
Range分区表
特点：
根据分区键值的范围把数据⾏存储到表的不同分区中
多个分区的范围要连续，但是不能重复
默认情况下使⽤VALUES LESS THAN属性，即每个分区不包括指定的那个值
CREATE TABLE customer_login_log(
login_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '登陆⽇志ID',
customer_id INT UNSIGNED NOT NULL COMMENT '登陆⽤户ID',
login_time TIMESTAMP NOT NULL COMMENT '⽤户登陆时间',
login_ip INT UNSIGNED NOT NULL COMMENT '登陆IP',
login_type TINYINT NOT NULL COMMENT '登陆类型：0未成功，1成功',
PRIMARY KEY pk_loginid(login_id)
) ENGINE = innodb COMMENT '⽤户登陆⽇志表'
PARTITION BY RANGE (customer_id) (
PARTITION p0 VALUES LESS THAN (10000),
PARTITION p1 VALUES LESS THAN (10000),
PARTITION p2 VALUES LESS THAN (10000),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
Range分区的适⽤范围
分区键为⽇期或是时间类型
所有SELECT查询中都包括分区键
LIST分区
特点：
按分区键取值的列表进⾏分区
同范围分区⼀样，各分区的列表值不能重复
每⼀⾏数据必须能到对应的分区列表，否则数据插⼊失败
CREATE TABLE customer_login_log(
login_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '登陆⽇志ID',
customer_id INT UNSIGNED NOT NULL COMMENT '登陆⽤户ID',
login_time TIMESTAMP NOT NULL COMMENT '⽤户登陆时间',
login_ip INT UNSIGNED NOT NULL COMMENT '登陆IP',
login_type TINYINT NOT NULL COMMENT '登陆类型：0未成功，1成功',
PRIMARY KEY pk_loginid(login_id)
) ENGINE = innodb COMMENT '⽤户登陆⽇志表'
PARTITION BY LIST (login_type) (
PARTITION p0 VALUES (1,3,5,7,9),
PARTITION p1 VALUES (2,4,6,8)
);
如何选择正确的分区类型
如何为customerloginlog表分区
业务场景：
⽤户每次登录都会记录
⽇志保存⼀年，⼀年后可删除
解决：
使⽤RANGE范围分区
以login_type作为分区键
如何查看分区是否正确：
使⽤SELECT查询information_schema.PARTITIONS
这⾥不使⽤MAXVALUE，防⽌后续的⽇期全部归到⼀个分区中，⽽是使⽤定时计划修改增加分区ALTER TABLE customer_login_log ADD PARTITION(PARTITION p4 VALUES LESS THAN(2018))
删除以前⼀年的分区ALTER TABLE customer_login_log DROP PARTITION p0;
过期数据归档
建⽴⽤户登陆⽇志归档CREATE TABLE arch_customer_login_log(login_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT'登陆⽇志ID',customer_id INT UNSIGNED NOT NULL COMMENT'登陆⽤户ID',login_time TIMESTAMP NOT NULL COMMENT'⽤户登陆时间',login_ip INT
UNSIGNED NOT NULL COMMENT'登陆IP',login_type TINYINT NOT NULL COMMENT'登陆类型：0未成功，1成功',PRIMARY KEY
pk_loginid(login_id))ENGINE=innodb COMMENT'⽤户登陆⽇志归档表'
归档操作：ALTER TABLE customer_login_log EXCHANGE PARTITION p1 WITH TABLE arch_customer_login_log
迁移后删除：ALTER TABLE customer_login_log DROP PARTITION p2
根据需要可以把归档的表引擎改为ARCHIVE
分区数据归档迁移条件
操作步骤
1. mysql >= 5.7
2. 结构相同
3. 归档到的数据表⼀定是⾮分区表
4. ⾮临时表；不能有外键约束
5. 归档引擎要是：archive
使⽤分区表的注意事项
结合业务场景选择分区键，避免跨分区查询
对分区表进⾏查询最好在WHERE从句中包含分区键
具有主键或唯⼀索引的表，主键或唯⼀索引必须是分区键的⼀部分
商品实体
品牌信息表（brand_info）
CREATE TABLE brand_info(
brand_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '品牌ID',
brand_name VARCHAR(50) NOT NULL COMMENT '品牌名称',
telephone VARCHAR(50) NOT NULL COMMENT '',
brand_web VARCHAR(100) COMMENT '品牌⽹络',
brand_logo VARCHAR(100) COMMENT '品牌logo URL',
brand_desc VARCHAR(150) COMMENT '品牌描述',
brand_status TINYINT NOT NULL DEFAULT 0 COMMENT '品牌状态,0禁⽤,1启⽤',
brand_order TINYINT NOT NULL DEFAULT 0 COMMENT '排序',
modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY pk_brandid (brand_id)
)ENGINE=innodb COMMENT '品牌信息表';
分类信息表(product_category)
CREATE TABLE product_category(
category_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '分类ID',
category_name VARCHAR(10) NOT NULL COMMENT '分类名称',
category_code VARCHAR(10) NOT NULL COMMENT '分类编码',
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '⽗分类ID',
category_level TINYINT NOT NULL DEFAULT 1 COMMENT '分类层级',
category_status TINYINT NOT NULL DEFAULT 1 COMMENT '分类状态',
modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY pk_categoryid(category_id)
)ENGINE=innodb COMMENT '商品分类表'
供应商信息表（supplier_info）
CREATE TABLE supplier_info(
supplier_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '供应商ID',
supplier_code CHAR(8) NOT NULL COMMENT '供应商编码',
supplier_name CHAR(50) NOT NULL COMMENT '供应商名称',
supplier_type TINYINT NOT NULL COMMENT '供应商类型：1.⾃营，2.平台',
link_man VARCHAR(10) NOT NULL COMMENT '供应商联系⼈',
phone_number VARCHAR(50) NOT NULL COMMENT '',
bank_name VARCHAR(50) NOT NULL COMMENT '供应商开户银⾏名称',
bank_account VARCHAR(50) NOT NULL COMMENT '银⾏账号',
address VARCHAR(200) NOT NULL COMMENT '供应商地址',
supplier_status TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0禁⽌，1启⽤',
modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY pk_supplierid(supplier_id)
) ENGINE = innodb COMMENT '供应商信息表';
商品信息表(product_info)
宽度较宽，字段差不多⼀起使⽤
可以被缓存
CREATE TABLE product_info(
product_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '商品ID',
product_core CHAR(16) NOT NULL COMMENT '商品编码',
product_name VARCHAR(20) NOT NULL COMMENT '商品名称',
bar_code VARCHAR(50) NOT NULL COMMENT '国条码',
brand_id INT UNSIGNED NOT NULL COMMENT '品牌表的ID',
one_category_id SMALLINT UNSIGNED NOT NULL COMMENT '⼀级分类ID',
two_category_id SMALLINT UNSIGNED NOT NULL COMMENT '⼆级分类ID',
three_category_id SMALLINT UNSIGNED NOT NULL COMMENT '三级分类ID',
supplier_id INT UNSIGNED NOT NULL COMMENT '商品的供应商ID',
price DECIMAL(8,2) NOT NULL COMMENT '商品销售价格',
average_cost DECIMAL(18,2) NOT NULL COMMENT '商品加权平均成本',
publish_status TINYINT NOT NULL DEFAULT 0 COMMENT '上下架状态：0下架1上架',
audit_status TINYINT NOT NULL DEFAULT 0 COMMENT '审核状态：0未审核，1已审核',
weight FLOAT COMMENT '商品重量',
length FLOAT COMMENT '商品长度',
height FLOAT COMMENT '商品⾼度',
width FLOAT COMMENT '商品宽度',
color_type ENUM('红','黄','蓝','⿊'),
production_date DATETIME NOT NULL COMMENT '⽣产⽇期',
shelf_life INT NOT NULL COMMENT '商品有效期',
descript TEXT NOT NULL COMMENT '商品描述',
indate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '商品录⼊时间',
modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间', PRIMARY KEY pk_productid(product_id)
) ENGINE = innodb COMMENT '商品信息表';
商品图⽚表(productpicinfo)
CREATE TABLE product_pic_info(
product_pic_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '商品图⽚ID',
product_id INT UNSIGNED NOT NULL COMMENT '商品ID',
pic_desc VARCHAR(50) COMMENT '图⽚描述',
pic_url VARCHAR(200) NOT NULL COMMENT '图⽚URL',
is_master TINYINT NOT NULL DEFAULT 0 COMMENT '是否主图：0.⾮主图1.主图',
pic_order TINYINT NOT NULL DEFAULT 0 COMMENT '图⽚排序',
pic_status TINYINT NOT NULL DEFAULT 1 COMMENT '图⽚是否有效：0⽆效 1有效',
modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间', PRIMARY KEY pk_picid(product_pic_id)
)ENGINE=innodb COMMENT '商品图⽚信息表';
商品评论表（product_comment）
CREATE TABLE product_comment(
comment_id INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '评论ID',
product_id INT UNSIGNED NOT NULL COMMENT '商品ID',
order_id BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
customer_id INT UNSIGNED NOT NULL COMMENT '⽤户ID',
title VARCHAR(50) NOT NULL COMMENT '评论标题',
content VARCHAR(300) NOT NULL COMMENT '评论内容',
audit_status TINYINT NOT NULL COMMENT '审核状态：0未审核，1已审核',
audit_time TIMESTAMP NOT NULL COMMENT '评论时间',
modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间', PRIMARY KEY pk_commentid(comment_id)
) ENGINE = innodb COMMENT '商品评论表';
订单模块
订单主表（order_master）
CREATE TABLE order_master(
order_id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
order_sn BIGINT UNSIGNED NOT NULL COMMENT '订单编号 yyyymmddnnnnnnnn',
customer_id INT UNSIGNED NOT NULL COMMENT '下单⼈ID',
shipping_user VARCHAR(10) NOT NULL COMMENT '收货⼈姓名',
province SMALLINT NOT NULL COMMENT '省',
city SMALLINT NOT NULL COMMENT '市',
district SMALLINT NOT NULL COMMENT '区',
address VARCHAR(100) NOT NULL COMMENT '地址',
payment_method TINYINT NOT NULL COMMENT '⽀付⽅式：1现⾦，2余额，3⽹银，4⽀付宝，5',
order_money DECIMAL(8,2) NOT NULL COMMENT '订单⾦额',
district_money DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '优惠⾦额',
shipping_money DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '运费⾦额',
payment_money DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '⽀付⾦额',
shipping_comp_name VARCHAR(10) COMMENT '快递公司名称',
shipping_sn VARCHAR(50) COMMENT '快递单号',
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
shipping_time DATETIME COMMENT '发货时间',
pay_time DATETIME COMMENT '⽀付时间',
receive_time DATETIME COMMENT '收货时间',
order_status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态',
order_point INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单积分',
invoice_time VARCHAR(100) COMMENT '发票抬头',
modified_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
发表评论