mysql 设计表原则 详解 mysql表的设计

【Mysql】浅谈表结构设计

• （1）主键选择
• （2）创建时间&修改时间
• （3）四个范式
• （4）添加必要的冗余字段
• （5）合理的表关联

• 数据表设计规则总结

• （6）字段数据类型设计规范
• （7）数据库设计实际案例

• （7.1）需求分析
• （7.2）表结构设计

• （1）明确表功能
• （2）简单设计表字段
• （3）分析表之间关联关系
• （4）表之间创建关联关系
• （5）实际的表数据
• （6）实际的sql语句

• （8）数据设计小Demo

（1）主键选择

主键要满足以下几个条件：

1. 唯一性（是表的唯一标识，用来联立其他的表）
2. 非空性
3. 有序性（提高查询效率）
4. 可读性
5. 可扩展性

注意点：

• 建议使8字节无符号的bigint(20)作为主键的数据类型
• 主外键的数据类型一定要一致
• 每个表中的主键命名保持一致
• 不使用varchar类型作主键

选择合适的类型：

• 尽量选择小，简单的数据类型（服务端处理效率高，传输快）
• 保持可读性
• 尽量避免Null

常见的数据类型设计：

• 状态类型用 tinyint，例如 性别等
• 时间日期使用datetime,timesamp类型，我比较喜欢使用datetime类型，(可读性高些)
• 尽量不要使用text和blob数据类型，特别是blob

（2）创建时间&修改时间

这些主要是用作业务上的字段，每个表中都应该有，在查询，以及问题查找定位时有诸多好处，例如： 查询最近三个月的登录用户，这时就可用创建时间来查询等等

• 非空
• now

（3）四个范式

（1）第一范式
当表中字段存在可再分的情况时，对其进行拆分

（2）第二范式：属性完全依赖于某个候选键

• 每个表必须有唯一键
• 主键是唯一标识

（3）第三范式：属性不依赖于其他非主属性

非主属性指的是不再任何码（主码和候选码）中出现的属性。当属性依赖于其他非主属性时，应该进行拆分

未拆分前，属性level依赖于非主属性Score，无法适应多变的业务，这种设计较为死板，拆分以后可以灵活更改各个科目对应的level属性，较为灵活，适用于多变的业务（4）第四范式L禁止非主键列和其他非主键列一对多关系

在同一张表中，非主键的列和非主键列存在一对多的关系会造成存储大量的冗余字段，当出现这种情况时，应该进行合理的拆表

在考试分数等级表中，Grade和Subject存在一对多的关系，浪费了存储空间，并且会降低查询效率，拆分后业务的可扩展性更大，也节省了不必要的冗余信息存储所浪费的空间

（4）添加必要的冗余字段

像“创建时间”、“修改时间”、“备注”、“操作用户IP”和一些用于其他需求(如统计)的字段等，在每张表中必须都要有。

不是说只有系统中用到的数据才会存到数据库中，一些冗余字段是为了便于日后维护、分析、拓展而添加的，这点是非常重要的，比如黑客攻击，篡改了数据，我们便就可以根据修改时间和操作用户IP来查找定位

（5）合理的表关联

若多张表之间的关系复杂，建议采用第三张映射表来关联维护两张表之间的关系，以降低表之间的直接耦合度。若多张表涉及到大数据量的问题，表结构尽量简单，关联也要尽可能避免。

（1）一对多关系
一个人只有一套房子，一个房子对应夫妻两个人。所以我们在多的一方，也就是在人表中加入房子的唯一标识主键（房子地址）

（2）多对多关系
一个人拥有好几辆车，每辆车又会对应两个人（夫妻二人）。面对多对多的这种关系，我们提取出一张中间表，而不再对人的表和车的表进行修改。所以我们把人表中的主键身份证号、车表中的主键车牌号，单独取出来放进一个中间表

数据表设计规则总结

（1）设计范式

• 每张表只存储一类数据，比如用户表就存储用户信息；（做关联用的列不算，比如上面例子中人的表中还存储了房子的地址，但是地址是做关联用的）
• 设计表之前首先要划分清楚各模块是什么，比如用户模块、车、房子，然后理清楚各模块间的关系，两张表之间如果存在关联关系，则该关系可以分为三种：一对一，一对多，多对多。
• 分别先独立的设计各个表的结构，这样设计完后是一张张相互独立没有关联的表
• 然后按照两表之间的关系来进行完善即可

（2）两表之间关系的完善

• A和B之间是1对1关系，则将一张表的唯一字段（唯一能够确定一行数据的）放入另外一张表中：比如用户表和手机表是1对1的关系，那么我们可以将用户身份证号放入手机表中，作为手机表的列名（字段）之一。
• A和B之间是1对多 关系，则将“一”这一端的唯一字段放入“多“的一端中：比如用户表和衣服表，一个用户有多件衣服，一件衣服只属于一个用户，那么就将身份证号放入衣服表中即可
• A和B之间是 多对多 关系，则建立一张中间表，把AB两表中的唯一键放入中间表即可：比如手机表和APP表，一个手机可以安装多个APP，一个APP可以被多个手机安装。那么我们将APP的ID号和手机的手机号一同放入一张中间表（也叫关联表）中即可

（6）字段数据类型设计规范

本着够用的原则，不能把数据字段范围设为最大或者默认值，会导致存储空间大量的浪费，对数据库的执行效率造成很大的影响。

用尽量少的存储空间来存数一个字段的数据：

• 能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);
• IP地址最好使用int类型;
• 固定长度的类型最好使用char,例如：邮编;
• 能使用tinyint就不要使用smallint,int;
• 最好给每个字段一个默认值,最好不能为null;

（1）字段类型尽可能占用少的存储空间
更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理CPU的时间也很短。在需要存储年龄、性别这些类似的应用场景中，应该选择tinyint来存储，而不是int。处理日期的时候，存储用户日期，应该选择date类型而不是datetime，datetime可以精确到时分秒

TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。当然，对于跨时区的业务，TIMESTAMP更为合适。

（2）数据类型的整合最好固定长度
存储字符时，应优先考虑char数据类型，因为char是定长的，而varchar 是变长的，mysql处理char比varchar要快一点。char类型的最大宽度为255 字节，varchar 最大宽度为 65535 个字节。

（3）尽量避免NULL
索引null列需要额外的空间来保存，占更多空间，进行比较和计算时，对null值作特别的处理，可能造成索引失效

（4）数据库设计过程中尽量使用int字段类型
所有的数据类型中int不管是存储空间还是执行速度方面都是比较好的。但是不要因为int高效，而把所有的字段都设计成为int来处理。

（5）财务相关数据，使用decimal类型
用decimal来存储金额字段，不要用float和double，会出现数据精度丢失

（6）Mysql 的表尽量设置成 KV(Key-Value)结构，这样便于扩展和维护。

（7）当表的字段数非常多时，可以将表分成两张表，一张作为条件查询表，一张作为详细内容表(主要是为了性能考虑)。

（8）字段的类型为枚举型或布尔型时，建议使用 char(1)类型

（7）数据库设计实际案例

（7.1）需求分析

1. 分类管理（分页查询、增删改）
2. 属性管理（分页查询、增删改）
3. 产品管理（分页查询、增删改）
4. 产品图片管理（分页查询、增删改）（一件产品对应多个图片，所以单独管理）
5. 产品属性设置（改）（一件产品对应多条属性值）
6. 用户管理（分页查询）
7. 订单管理（分页查询、查看详情、发货）

（7.2）表结构设计

（1）明确表功能

• 分类表：存放分类信息，如电视、手机、电扇等
• 属性表：存放属性信息，如颜色、重量、品牌、尺寸等
• 产品表：存放产品信息
• 属性值表：存放属性值信息，如红色、12kg、华为、13寸
• 产品图片表：存放产品图片信息，如产品页面显示的5个图片
• 评论表：存放评论信息
• 用户表：存放用户信息
• 订单表：存放订单信息，如邮寄地址、电话号码、收件人等
• 订单项表：存放订单项信息，包括购买产品种类、数量等

表名	介绍
分类表	存放分类信息，如电视、手机、电扇等
属性表	存放属性信息，如颜色、重量、品牌、尺寸等
产品表	存放产品信息
属性值表	存放属性值信息，如红色、12kg、华为、13寸
产品图片表	存放产品图片信息，如产品页面显示的5个图片
评论表	存放评论信息
用户表	存放用户信息
订单表	存放订单信息，如邮寄地址、电话号码、收件人等
订单项表	存放订单项信息，包括购买产品种类、数量等

（2）简单设计表字段

（1）用户表

（2）分类表

（3）属性表

（4）产品表

（5）属性值表

（6）产品图片表

（7）评价表

（8）订单表

（9）订单项表

（3）分析表之间关联关系

1. 分类表和产品表：一对多关系，一个分类对用多个产品，一个产品只对应一个分类。所以在产品表中加入分类表的唯一标识外键
2. 分类表和属性表：一对多关系，所以在属性表中后加入分类表的唯一标识外键
3. 属性表和属性值表：一对多关系，所以在属性值表中加入属性表的唯一标识外键
4. 产品表和属性值表：一对多关系
5. 产品表和产品图片表：一对多
6. 产品表和评价表：一对多
7. 用户表和订单表：一对多

因为用户表和产品表是多对多关系，产品和订单也是多对多关系，所以创建一个订单项表作为关联表

9. 产品表和订单项表：一对多
10. 用户表和订单项表：一对多
11. 订单表和订单项表：一对多
12. 用户表和评价表：一对多

（4）表之间创建关联关系

（1）用户表不变
（2）分类表不变
（3）属性表加入分类表的id
（4）产品表加入属性表的id
（5）属性值表加入产品表id、属性表id
（6）产品图片表加入产品表id
（7）评价表加入用户id、产品id
（8）订单表加入用户id
（9）订单项表加入产品id、订单id、用户id

（5）实际的表数据

（1）用户表

（2）分类表

（3）属性表

根据分类表id，可以找到这类产品包含的属性。例如id为83的平板电视鸡，包含的属性有品牌、证书状态、型号等等属性

（4）产品表

产品表包含的信息主要是对产品的描述，这些属性是产品自带的。

可以根据分类表id找到这类产品包含的各个具体产品

（5）属性值表

属性值表包含的是属性具体的值。上面根据分类id可以找到产品id和属性id，现在根据属性id和产品id可以找到具体的 某个产品的某个属性值是什么

（6）产品图片表

根据产品id找到对应的图片

（7）评价表

根据用户id和产品id，因为评价是跟用户和产品相关的，所以要加入两个外键字段。可以找到某个用户对某个产品的评价

（8）订单表

（9）订单项表

（6）实际的sql语句

（8）数据设计小Demo

（1）构思场景
有几对夫妻，每对都是夫妻二人共通购买了一间房子，也共通购买了两辆以上的车。现在要设计表来存储每个人的信息、房子信息、车信息。
（2）先独立设计表
人people的信息表：

身份证号

姓名

性别

手机号

年龄

房子house的信息表：

地址

使用面积

建筑面积

车car的信息表：

车牌

车龄

车型

（3）分析表之间的关系
人只有一套房子，房子属于两个人。所以房子和人是一对多的关系，房子是一，人是多。
每个人可以有多辆车，每辆车属于两个人。所以车和人是多对多的关系。
（4）改进表之间的关系
首先看人和房子，因为房子是一，人是多。所以在多的一方，也就是人表中加入外键：房子地址。

身份证号

姓名

性别

手机号

年龄

房子地址

再看人和车，因为都是多，所以提取一张中间表carPeople，字段包括：身份证号、车牌号

身份证号

车牌号

（5）分析查询效果
1-根据一个人的身份证号来查询这个人下面的房子信息
select h.房子信息 from house h left join people p on h.地址=p.房子地址 where p.身份证号=身份证号;
2-根据一个人的身份证号来查询这个人下面的车子信息
select c.车子信息 from car c left join carPeople cp on c.车牌=cp.车牌号 where cp.身份证号=身份证号;

（6）总结一下设计的规则
1-每张表只存储一类数据，比如用户表就存储用户信息；（做关联用的列不算，比如上面例子中人的表中还存储了房子的地址，但是地址是做关联用的）
2-设计表之前首先要划分清楚各模块是什么，比如用户模块、车、房子，然后理清楚各模块间的关系，两张表之间如果存在关联关系，则该关系可以分为三种：一对一，一对多，多对多。
3-分别先独立的设计各个表的结构，这样设计完后是一张张相互独立没有关联的表
4-然后按照两表之间的关系来进行完善即可。

（7）总结两表之间的关系完善规则
1-A和B之间是1对1关系，则将一张表的唯一字段（唯一能够确定一行数据的）放入另外一张表中：比如用户表和手机表是1对1的关系，那么我们可以将用户身份证号放入手机表中，作为手机表的列名（字段）之一。
2-A和B之间是1对多 关系，则将“一”这一端的唯一字段放入“多“的一端中：比如用户表和衣服表，一个用户有多件衣服，一件衣服只属于一个用户，那么就将身份证号放入衣服表中即可
3-A和B之间是 多对多 关系，则建立一张中间表，把AB两表中的唯一键放入中间表即可：比如手机表和APP表，一个手机可以安装多个APP，一个APP可以被多个手机安装。那么我们将APP的ID号和手机的手机号一同放入一张中间表（也叫关联表）中即可。