一次电商项目后台的数据库设计小结（第一部分）（附源码）

• 1.准备工作
• 2.用户实体
• • 2.1 用户登陆表（customer_login）
  • 2.2 用户信息表（customer_inf）
  • 2.3 用户级别表（customer_level_inf）
  • 2.4 用户地址表（customer_addr）
  • 2.5 用户积分日志表（customer_point_log）
  • 2.6 用户余额变动表（customer_balance_log）
  • 2.7 用户登录日志表（customer_login_log）
• 3.customer_login_log分区
• • 3.1 customer_login_log表分区
  • 3.2 使用分区表的注意事项
• 4.商品实体
• • 4.1 品牌信息表（brand_info）
  • 4.2 分类信息表（product_category）
  • 4.3 商品供应商信息表（supplier_info）
  • 4.4 商品信息表（product_info）
  • 4.5 商品图片表（product_pic_info）
  • 4.5 商品评论表（product_comment）
• 5.订单实体
• • 5.1 订单主表（order_master）
  • 5.2 订单详情表（order_detail）
  • 5.3 购物车表（order_cart）
  • 5.4 仓库信息表（warehouse_info）
  • 5.5 商品库存表（warehouse_product）
  • 5.6 物流公司信息表（shipping_info）
• 6. DB规划
• • 6.1 用户数据库（mc_customerdb）
  • 6.2 商品数据库（mc_productdb）
  • 6.3 订单数据库（mc_orderdb）
• 7.各类规范小结
• • 7.1 数据库命名规范
  • 7.2 索引设计规范
  • 7.3 数据库字段设计规范
  • 7.4 SQL开发规范
  • 7.5 操作行为规范

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 NULLDEFAULT 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权限；
• 对于程序连接数据库账号，遵循权限最小原则。

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！