一个电商项目的数据库设计实践（第一部分）（附源码）

1.准备工作

数据库：MySQL5.7（5.5、5.6皆可）；
数据库客户端软件：SQLyog；
源码在Github上：https://github.com/Fateasstring/EB-SQL ，文章整理不易，请赏个star吧。

2.用户实体

用户模块用于管理和维护用户信息。把所有的用户信息放在一个表中，会容易导致：

• 数据插入异常；
• 数据更新异常，比如要修改某一行的值是，不得不修改多行数据；
• 数据删除异常，删除某一数据时不得不同时删除另一数据；
• 数据存在冗余； 数据表过宽，会影响修改表结构的效率。

第三范式（3NF）定义：一个表中的列和其它列之间，即不包含部分函数依赖关系，也不包含传递函数依赖关系，那么这个表的设计就符合第三范式。

设计方式：
用户登陆表：{登录名，密码，用户状态}
用户地址表：{省，市，区编码，地址}
用户信息表：{用户姓名，证件类型，证件号码，手机号，邮箱，性别，积分，注册时间，生日，会员级别，用户余额}

2.1 用户登陆表（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_TIMESTAMPON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',

PRIMARY key pk_customerid(customer_id)

)engine = INNODB COMMENT = '用户登录表';

2.2 用户信息表（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',

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_TIMESTAMPON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',

primary key pk_customerinfid (customer_inf_id)

)ENGINE = INNODB COMMENT '用户信息表';

2.3 用户级别表（customer_level_inf）

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 '用户级别信息表';

2.4 用户地址表（customer_addr）

CREATE TABLE customer_addr(
customer_addr_id int UNSIGNED AUTO_INCREMENTnot 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_TIMESTAMPON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',

primary key pk_customeraddid (customer_addr_id)
		
)ENGINE = INNODB COMMENT '用户地址表';

2.5 用户积分日志表（customer_point_log）

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_point(point_id)

)ENGINE = INNODB COMMENT '用户积分日志表';

2.6 用户余额变动表（customer_balance_log）

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 '用户余额变动表';

2.7 用户登录日志表（customer_login_log）

```sql
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 '用户登陆日志表'

3.customer_login_log分区

3.1 customer_login_log表分区

业务使用场景：用户登录日志表主要用于用户每次登录的记录，每次登录时会在表中增加一条日志，所以数据量增长很快。为了尽可能保持生产环境数据库不会因为日志表增长而过快增长，用户登录日志只保存一年。

1）登录日志表的分区类型及分区键

这样的场景使用范围（RANGE）分区比较适合，可以很方便删除分区范围的数据，特别时数据量大的时候会方便很多。
这里以login_time作为分区键。

2）分区后的用户登录日志表

```sql
CREATE table customer_login_log(
customer_id int UNSIGNED NOT null ,
login_time DATETIME not null ,
login_ip int UNSIGNED not null ,
login_type TINYINT not null 
)ENGINE = INNODB 
PARTITION by RANGE(YEAR(login_time))
(PARTITION p0 VALUES less than(2015),
PARTITION p1 VALUES less than(2016),
PARTITION p2 VALUES less than(2017)
);

插入数据：

INSERT into customer_login_log(customer_id, login_time, login_ip, login_type)VALUES
(1001,'2015-01-25',0,1),
(1001,'2015-07-1',0,1),
(1001,'2015-10-1',0,1),
(1001,'2016-3-1',0,1),
(1001,'2016-9-1',0,1)

表中的数据为：

​
执行下列SQL语句：

SELECT 
table_name, 
partition_name, 
partition_description, 
table_rowsFROM information_schema.`PARTITIONS`
WHERE TABLE_NAME = 'customer_login_log'

得到结果：

为了避免数据插入到RANGE分区失败，要做修改并添加分区的计划任务。
增加分区：

ALTER TABLE customer_login_log ADD partition (partition p4 VALUES less than(2018));

再次查询分区信息表得到结果：
​
接下来删除p0分区：

ALTER TABLE customer_login_log DROP partition p0;

再次查询分区信息表得到结果：
​
所以，可以用这种方式删除过期日志，十分便捷。

3）分区数据归档

分区数据迁移条件：

• MySQL版本>=5.7；
• 结构相同；
• 归档到的数据表一定要是非分区表（非临时表）；
• 非临时表，不能有外键约束；
• 归档引擎要是archive。

首先建立一个与分区表结构相同的非分区表，作为归档表：

CREATE table arch_customer_login_log(
customer_id int UNSIGNED NOT null ,
login_time DATETIME not null ,
login_ip int UNSIGNED not null ,
login_type TINYINT not null 
)ENGINE = INNODB 

执行命令：

SELECT *FROM customer_login_log;

可以看到数据在customer_login_log中的情况：
​
现在将这些数据交换到归档表中：

ALTER TABLE customer_login_log exchange PARTITION p1 WITH TABLE arch_customer_login_log;

现在查看customer_login_log中2016年之前的数据：
​
查看arch_customer_login_log：
​
可以看到2016前的数据已经迁移到arch_customer_login_log归档表中了。
此时customer_login_log中的p1分区依旧是是存在的，所以需要对分区p1进行删除：

ALTER TABLE customer_login_log DROP partition p1;

再一次查看customer_login_log中的分区：

SELECT 
table_name, partition_name, partition_description, table_rowsFROM 
information_schema.`PARTITIONS`WHERE 
TABLE_NAME = 'customer_login_log'

可以看到p1分区已经没有了。
最后arch_customer_login_log改为使用归档引擎archive：

ALTER TABLE arch_customer_login_log ENGINE=archive;

这样能就完成了整个归档操作。而使用archive引擎的好处是，它比innodb引擎占用的空间更小。但是归档引擎中的表只能进行查询操作，不能进行写操作。

3.2 使用分区表的注意事项

• 结合业务场景选择分区，避免跨分区查询；
• 对分区表进行查询最好在where从句中包含分区键；
• 具有主键或唯一索引的表，主键或唯一索引必须是分区键的一部分；

4.商品实体

商品实体包含的信息如下：
​
将这些信息分类存储在以下几个表中。

4.1 品牌信息表（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'品牌信息表'

4.2 分类信息表（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 '商品分类';

4.3 商品供应商信息表（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 '供应商信息表';

4.4 商品信息表（product_info）

DROP TABLE IF EXISTS `product_info`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `product_info` (
  `product_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品ID',

  `product_code` char(16) NOT NULL COMMENT '商品编码',

  `product_name` varchar(50) NOT NULL COMMENT '商品名称',

  `bar_code` varchar(50) NOT NULL COMMENT '国条码',

  `brand_id` int(10) unsigned NOT NULL COMMENT '品牌表的ID',

  `one_category_id` smallint(5) unsigned NOT NULL COMMENT '一级分类ID',

  `two_category_id` smallint(5) unsigned NOT NULL COMMENT '二级分类ID',

  `three_category_id` smallint(5) unsigned NOT NULL COMMENT '三级分类ID',

  `supplier_id` int(10) unsigned NOT NULL COMMENT '商品的供应商id',

  `price` decimal(8,2) NOT NULL COMMENT '商品销售价格',

  `average_cost` decimal(18,2) NOT NULL COMMENT '商品加权平均成本',

  `publish_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '上下架状态:0下架1上架',

  `audit_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '审核状态:0未审核,1已审核',

  `weight` float DEFAULT NULL COMMENT '商品重量',

  `length` float DEFAULT NULL COMMENT '商品长度',

  `heigh` float DEFAULT NULL COMMENT '商品高度',

  `width` float DEFAULT NULL COMMENT '商品宽度',

  `color_type` enum('红','黄','蓝','黒') DEFAULT NULL,

  `production_date` datetime NOT NULL COMMENT '生产日期',

  `shelf_life` int(11) 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 (`product_id`)

) ENGINE=InnoDB AUTO_INCREMENT=262141 DEFAULT CHARSET=utf8 COMMENT='商品信息表';

4.5 商品图片表（product_pic_info）

DROP TABLE IF EXISTS `product_pic_info`;
/*!40101 SET @saved_cs_client     
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `product_pic_info` (

  `product_pic_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品图片ID',

  `product_id` int(10) unsigned NOT NULL COMMENT '商品ID',

  `pic_desc` varchar(50) DEFAULT NULL COMMENT '图片描述',

  `pic_url` varchar(200) NOT NULL COMMENT '图片URL',

  `is_master` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否主图:0.非主图1.主图',

  `pic_order` tinyint(4) NOT NULL DEFAULT '0' COMMENT '图片排序',

  `pic_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '图片是否有效:0无效 1有效',

  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 

    CURRENT_TIMESTAMP COMMENT '最后修改时间',

  PRIMARY KEY (`product_pic_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品图片信息表';

图片，小视频等数据，是不能直接存在MySQL数据库里的，而是存在专门的图片服务器，文件服务器或者CDN上，并把相应的地址信息存储在MySQL数据库中。

4.5 商品评论表（product_comment）

DROP TABLE IF EXISTS `product_comment`;
/*!40101 SET @saved_cs_client     
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `product_comment` (

  `comment_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '评论ID',

  `product_id` int(10) unsigned NOT NULL COMMENT '商品ID',

  `order_id` bigint(20) unsigned NOT NULL COMMENT '订单ID',

  `customer_id` int(10) unsigned NOT NULL COMMENT '用户ID',

  `title` varchar(50) NOT NULL COMMENT '评论标题',

  `content` varchar(300) NOT NULL COMMENT '评论内容',

  `audit_status` tinyint(4) NOT NULL COMMENT '审核状态:0未审核1已审核',

  `audit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP COMMENT '评论时间',

  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',

  PRIMARY KEY (`comment_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品评论表';

5.订单实体

订单实体包含如下信息：
​

5.1 订单主表（order_master）

DROP TABLE IF EXISTS `order_master`;
/*!40101 SET @saved_cs_client     
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `order_master` (
  `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `order_sn` bigint(20) unsigned NOT NULL COMMENT '订单编号 yyyymmddnnnnnnnn',
  `customer_id` int(10) unsigned NOT NULL COMMENT '下单人ID',
  `shipping_user` varchar(10) NOT NULL COMMENT '收货人姓名',
  `province` smallint(6) NOT NULL COMMENT '收货人所在省',
  `city` smallint(6) NOT NULL COMMENT '收货人所在市',
  `district` smallint(6) NOT NULL COMMENT '收货人所在区',
  `address` varchar(100) NOT NULL COMMENT '收货人详细地址',
  `payment_method` tinyint(4) 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) DEFAULT NULL COMMENT '快递公司名称',
  `shipping_sn` varchar(50) DEFAULT NULL COMMENT '快递单号',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
  `shipping_time` datetime DEFAULT NULL COMMENT '发货时间',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  `receive_time` datetime DEFAULT NULL COMMENT '收货时间',
  `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
  `order_point` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单积分',
  `invoice_title` varchar(100) DEFAULT NULL COMMENT '发票抬头',
  `modified_time` timestamp NOT NULL 
  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`order_id`),
  UNIQUE KEY `ux_ordersn` (`order_sn`)
) ENGINE=InnoDB AUTO_INCREMENT=10011 
DEFAULT CHARSET=utf8 COMMENT='订单主表';
/*!40101 SET character_set_client = @saved_cs_client */;

订单主表是非常重要的表，关系到所有经营的行为活动。

5.2 订单详情表（order_detail）

DROP TABLE IF EXISTS `order_detail`;
/*!40101 SET @saved_cs_client     
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `order_detail` (
  `order_detail_id` int(10) unsigned NOT NULL 
  AUTO_INCREMENT COMMENT '自增主键ID,订单详情表ID',
  
  `order_id` int(10) unsigned NOT NULL COMMENT '订单表ID',
  `product_id` int(10) unsigned NOT NULL COMMENT '订单商品ID',
  `product_name` varchar(50) NOT NULL COMMENT '商品名称',
  `product_cnt` int(11) NOT NULL DEFAULT '1' COMMENT '购买商品数量',
  `product_price` decimal(8,2) NOT NULL COMMENT '购买商品单价',
  `average_cost` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '平均成本价格',
  `weight` float DEFAULT NULL COMMENT '商品重量',
  `fee_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '优惠分摊金额',
  `w_id` int(10) unsigned NOT NULL COMMENT '仓库ID',
  `modified_time` timestamp NOT NULL 
  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  
  PRIMARY KEY (`order_detail_id`)
) ENGINE=InnoDB AUTO_INCREMENT=29698 
DEFAULT CHARSET=utf8 COMMENT='订单详情表';
/*!40101 SET character_set_client = @saved_cs_client */;

5.3 购物车表（order_cart）

DROP TABLE IF EXISTS `order_cart`;
/*!40101 SET @saved_cs_client    
 = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `order_cart` (
  `cart_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '购物车ID',
  `customer_id` int(10) unsigned NOT NULL COMMENT '用户ID',
  `product_id` int(10) unsigned NOT NULL COMMENT '商品ID',
  `product_amount` int(11) NOT NULL COMMENT '加入购物车商品数量',
  `price` decimal(8,2) NOT NULL COMMENT '商品价格',
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '加入购物车时间',
  `modified_time` timestamp NOT NULL
   DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`cart_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='购物车表';
/*!40101 SET character_set_client = @saved_cs_client */;

5.4 仓库信息表（warehouse_info）

DROP TABLE IF EXISTS `warehouse_info`;
/*!40101 SET @saved_cs_client     
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `warehouse_info` (
  `w_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT '仓库ID',
  `warehouse_sn` char(5) NOT NULL COMMENT '仓库编码',
  `warehouse_name` varchar(10) NOT NULL COMMENT '仓库名称',
  `warehouse_phone` varchar(20) NOT NULL COMMENT '仓库电话',
  `contact` varchar(10) NOT NULL COMMENT '仓库联系人',
  `province` smallint(6) NOT NULL COMMENT '省',
  `city` smallint(6) NOT NULL COMMENT '市',
  `district` smallint(6) NOT NULL COMMENT '区',
  `address` varchar(100) NOT NULL COMMENT '仓库地址',
  `warehouse_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '仓库状态:0禁用,1启用',
  `modified_time` timestamp NOT NULL 
  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`w_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 
DEFAULT CHARSET=utf8 COMMENT='仓库信息表';
/*!40101 SET character_set_client = @saved_cs_client */;

5.5 商品库存表（warehouse_product）

DROP TABLE IF EXISTS `warehouse_product`;
/*!40101 SET @saved_cs_client     
= @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `warehouse_product` (
  `wp_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品库存ID',
  `product_id` int(10) unsigned NOT NULL COMMENT '商品id',
  `w_id` smallint(5) unsigned NOT NULL COMMENT '仓库ID',
  `currnet_cnt` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '当前商品数量',
  `lock_cnt` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '当前占用数据',
  `in_transit_cnt` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '在途数据',
  `average_cost` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '移动加权成本',
  `modified_time` timestamp NOT NULL 
  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`wp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品库存表';
/*!40101 SET character_set_client = @saved_cs_client */;

5.6 物流公司信息表（shipping_info）

DROP TABLE IF EXISTS `shipping_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `shipping_info` (
  `ship_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `ship_name` varchar(20) NOT NULL COMMENT '物流公司名称',
  `ship_contact` varchar(20) NOT NULL COMMENT '物流公司联系人',
  `telphone` varchar(20) NOT NULL COMMENT '物流公司联系电话',
  `price` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '配送价格',
  `modified_time` timestamp NOT NULL 
  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY (`ship_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='物流公司信息表';

6. DB规划

从以下角度考虑：

• 为以后数据库迁移和拆分提供方便；
• 避跨库操作，把经常一起关联查询的表放在一个DB中；
• 为了方便识别表所在的DB，在表名前面增加库名前缀。

6.1 用户数据库（mc_customerdb）

包含：

• customer_inf
• customer_login
• customer_level_inf
• customer_login_log
• customer_point_log
• customer_balance_log

6.2 商品数据库（mc_productdb）

包含：

• product_info
• product_pic_info
• product_category
• product_supplier_info
• product_comment
• product_brand_info

6.3 订单数据库（mc_orderdb）

• order_master
• order_detail
• order_customer_addr
• warehouse_info
• shipping_info
• order_cart
• warehouse_product

在接下来文章中会在MySQL数据库创建这些数据库。

7.各类规范小结

7.1 数据库命名规范

• 所有数据库对像名称必须使用小写字母并用下划线分割，MySQL大小写敏感；
• 所有数据库对像名称禁止使用MySQL保留关键字；
• 数据库对像的命名要能做到见名识义，尽量不要超过32个字符；
• 临时库表必须以tmp为前缀，以日期为后缀；
• 备份库，备份表必须以bak为前缀并，日期为后缀；
• 所有存储相同数据的列名和列类型必须一致；
• 所有表尽量使用Innodb存储引擎；
• 数据库和表的字符集统一使用UTF8（兼容性更好），避免由于字符集转换产生乱码；
• 所有的表和字段都需要添加注释，使用comment从句添加表和列的备注，从一开始就进行数据字典的维护；
• 尽量控制单表数据量的大小，建议控制在500万以内，否则修改表结构、备份、恢复都会有很大问题；
• 谨慎使用MySQL分区表，分区表在物理上表现为多个文件，在逻辑上表现为一个表；
• 谨慎选择分区键，跨分区查询效率可能更低，建议使用物理分表的方式管理大量数据；
• 尽量做到冷热数据分离，减小表的宽度，MySQL对行数没有限制，但限制最多存储4096列；
• 经常一起使用的列放在一个表中；
• 禁止在表中建立预留字段，预留字段的命名很难做到见名识义，对于预留字段的类型修改会对表进行锁定；
• 禁止在数据库中存储图片、文件等二进制数据；
• 禁止在线上做数据库压力测试；
• 禁止从开发环境、测试环境直连生产环境数据库。

5.6以后的默认引擎支持事务，行级锁，更好的恢复性，高并发下性能更好。可以利用历史数据归档，分库分表来控制数据量大小。

7.2 索引设计规范

• 限制每张表上的索引列数量，单张表索引尽量不要超过5个；
• 避免建立冗余索引和重复索引；
• 禁止给表中的每一列都建立单独的索引；
• 每个Innodb表必须有一个主键；
• 不使用更新频繁的列作为主键，不适用多列主键；
• 不使用UUID、MD5、HASH字符串列作为主键；
• 主键建议选择使用自增ID值；
• 对于频繁的查询优先考虑使用覆盖索引，避免Innodb表进行索引的二次查找；
• 尽量避免使用外键约束（通常是表与表之间的关联键），但一定在表与表之间的关联键上建立索引；
• 索引可以增加查询效率，但同样也会降低插入和更新的效率。

Innodb是按照主键来组织表的。

面试问题：要在哪些列上建立索引？
答：通常使用SELECT、UPDATE、DELETE语句中的where从句中的列，包含在ORDER BY、GROUP BY、DISTINCT中的字段，多表JOIN的关联列。

面试问题：如何选择索引列的顺序？
答：把区分度最高的列（主键）放在能够在联合索引的最左侧，其实尽量把字段长度小的列放在联合索引的最左侧，使用最频繁的列放到联合索引的左侧。

7.3 数据库字段设计规范

• 优先选择符合存储需要的最小的数据类型；
• 避免使用TEXT、BLOB数据类型，若要使用，则把BLOB或TEXT分离到单独的扩展表中；
• 避免使用ENUM数据类型；
• 禁止使用数值作为ENUM的枚举值；
• 尽可能把所有的列定义为NOT NULL；
• 使用TIMESTAMP或DATETIME类型存储时间；
• 同财务相关的金额类数据，必须使用decimal类型。

7.4 SQL开发规范

• 建议使用预编译语句进行数据库操作；
• 避免数据类型的隐式转换；
• 程序连接不同的数据库使用不同的账号，禁止跨库查询；
• 禁止使用不含字段列表的INSERT语句；
• 避免使用子查询，可以把子查询优化为join操作，子查询的结果集无法使用索引；
• 避免使用JOIN关联太多的表；
• 尽量减少与数据库的交互次数；
• 禁止使用order by rand()进行随机排序；
• where从句中禁止对列进行函数转换和计算；
• 在明显不会有重复值是使用union all 而不是union；
• 拆分复杂的大SQL为多个小SQL。

7.5 操作行为规范

• 超100万行的批量写操作，要分批多次进行操作；
• 对于大表使用pt-online-schema-change修改表结构；
• 禁止为程序使用的账号赋予super权限；
• 对于程序连接数据库账号，遵循权限最小原则。