type
status
date
slug
summary
password
tags
category
icon
MySQL数据库设计规则
1、字段命名
2、字段类型
1-10之间的10个数字，每个数字1个字节就能保存，选择tinyint更为合适。
1）、尽可能选择占用存储空间小的字段类型，在满足正常业务需求的情况下，从小到大，往上选。
2）、如果字符串长度固定，或者差别不大，可以选择char类型。如果字符串长度差别较大，可以选择varchar类型。
3）、是否字段，可以选择bit类型。
4）、枚举字段，可以选择tinyint类型。
5）、主键字段，可以选择bigint类型。
6）、金额字段，可以选择decimal类型。
7）、时间字段，可以选择timestamp或datetime类型。
3、字段长度
在mysql中除了varchar和char是代表字符长度之外，其余的类型都是代表字节长度。
假如我们定义的字段类型和长度是：bigint(4)，bigint实际长度是8个字节。
4、字段个数
建议每表的字段个数，不要超过20个。（字段太多查询效率低）
5、主键
在创建表时，一定要创建主键。因为主键自带了主键索引，相比于其他索引，主键索引的查询效率最高，因为它不需要回表。此外，主键还是天然的唯一索引，可以根据它来判重。在单个数据库中，主键可以通过AUTO_INCREMENT，设置成自动增长的。但在分布式数据库中，特别是做了分库分表的业务库中，主键最好由外部算法(比如：雪花算法）生成，它能够保证生成的id是全局唯一的。除此之外，主键建议保存跟业务无关的值，减少业务耦合性，方便今后的扩展。不过我也见过，有些一对一的表关系，比如：用户表和用户扩展表，在保存数据时是一对一的关系。这样，用户扩展表的主键，可以直接保存用户表的主键。
6、存储引擎
Innodb
7、NOT NULL
在创建字段时，需要选择该字段是否允许为NULL。我们在定义字段时，应该尽可能明确该字段NOT NULL。为什么呢？我们主要以innodb存储引擎为例，myslam存储引擎没啥好说的。
主要有以下原因：
- 1.
在innodb中，需要额外的空间存储null值，需要占用更多的空间。
- 2.
null值可能会导致索引失效。
- 3.
null值只能用is null或者is not null判断，用=号判断永远返回false。
因此，建议我们在定义字段时，能定义成NOT NULL，就定义成NOT NULL。但如果某个字段直接定义成NOT NULL，万一有些地方忘了给该字段写值，就会insert不了数据。这也算合理的情况。但有一种情况是，系统有新功能上线，新增了字段。上线时一般会先执行sql脚本，再部署代码。由于老码中，不会给新字段赋值，则insert数据时，也会报错。由此，非常有必要给NOT NULL的字段设置默认值，特别是后面新增的字段。
8、索引
在建表时，除了指定主键索引之外，还需要创建一些普通索引。
例如：
在创建商品表时，使用spu_id（商品组表）和brand_id（品牌表）的id。
像这类保存其他表id的情况，可以增加普通索引：
后面查表的时候，效率更高。但索引字段也不能建的太多，可能会影响保存数据的效率，因为索引需要额外的存储空间。建议单表的索引个数不要超过：5个。如果在建表时，发现索引个数超过5个了，可以删除部分普通索引，改成联合索引。顺便说一句：在创建联合索引的时候，需要使用注意最左匹配原则，不然，建的联合索引效率可能不高。对于数据重复率非常高的字段，比如：状态，不建议单独创建普通索引。因为即使加了索引，如果mysql发现全表扫描效率更高，可能会导致索引失效。
9、时间字段
时间字段的类型，我们可以选择的范围还是比较多的，目前mysql支持：date、datetime、timestamp、varchar等。
varchar类型可能是为了跟接口保持一致，接口中的时间类型是String。
但如果哪天我们要通过时间范围查询数据，效率会非常低，因为这种情况没法走索引。
date类型主要是为了保存日期，比如：2020-08-20，不适合保存日期和时间，比如：2020-08-20 12:12:20。
而datetime和timestamp类型更适合我们保存日期和时间。
但它们有略微区别。
timestamp：用4个字节来保存数据，它的取值范围为1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07。此外，它还跟时区有关。
datetime：用8个字节来保存数据，它的取值范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。它跟时区无关。
优先推荐使用datetime类型保存日期和时间，可以保存的时间范围更大一些。
10、金额字段
mysql中有多个字段可以表示浮点数：float、double、decimal等。
而float和double可能会丢失精度，因此推荐大家使用decimal类型保存金额。
一般我们是这样定义浮点数的：decimal(m,n)。
其中n是指小数的长度，而m是指整数加小数的总长度。
假如我们定义的金额类型是这样的：decimal(10,2)，则表示整数长度是8位，并且保留2位小数。
11、唯一索引
唯一索引在我们实际工作中，使用频率相当高。
你可以给单个字段，加唯一索引，比如：组织机构code。
也可以给多个字段，加一个联合的唯一索引，比如：分类编号、单位、规格等。
单个的唯一索引还好，但如果是联合的唯一索引，字段值出现null时，则唯一性约束可能会失效。
12、字符集
mysql中支持的字符集有很多，常用的有：latin1、utf-8、utf8mb4、GBK等。
这4种字符集情况如下：
字符集 | 长度 | 说明 |
GBK | 2 | 支持中文，但是不是国际通用字符集 |
UTF-8 | 3 | 支持中英文混合场景，是国际通用字符集 |
latin1 | 1 | MySQL默认字符集 |
utf8mb4 | 4 | 完全兼容UTF-8，用四个字节存储更多的字符 |
latin1容易出现乱码问题，在实际项目中使用比较少。
而GBK支持中文，但不支持国际通用字符，在实际项目中使用也不多。
从目前来看，mysql的字符集使用最多的还是：utf-8和utf8mb4。
其中utf-8占用3个字节，比utf8mb4的4个字节，占用更小的存储空间。
但utf-8有个问题：即无法存储emoji表情，因为emoji表情一般需要4个字节。
由此，使用utf-8字符集，保存emoji表情时，数据库会直接报错。
所以，建议在建表时字符集设置成：utf8mb4，会省去很多不必要的麻烦。
13、排序规则
不知道，你关注过没，在mysql中创建表时，有个COLLATE参数可以设置。
例如：
它是用来设置排序规则的。
字符排序规则跟字符集有关，比如：字符集如果是utf8mb4，则字符排序规则也是以：utf8mb4_开头的，常用的有：utf8mb4_general_ci、utf8mb4_bin等。
其中utf8mb4_general_ci排序规则，对字母的大小写不敏感。说得更直白一点，就是不区分大小写。
而utf8mb4_bin排序规则，对字符大小写敏感，也就是区分大小写。
说实话，这一点还是非常重要的。
假如order表中现在有一条记录，name的值是大写的YOYO，但我们用小写的yoyo去查，例如：
如果字符排序规则是utf8mb4_general_ci，则可以查出大写的YOYO的那条数据。
如果字符排序规则是utf8mb4_bin，则查不出来。
由此，字符排序规则一定要根据实际的业务场景选择，否则容易出现问题。
14、大字段
我们在创建表时，对一些特殊字段，要额外关注，比如：大字段，即占用较多存储空间的字段。
比如：用户的评论，这就属于一个大字段，但这个字段可长可短。
但一般会对评论的总长度做限制，比如：最多允许输入500个字符。
如果直接定义成text类型，可能会浪费存储空间，所以建议将这类字段定义成varchar类型的存储效率更高。
当然，我还见过更大的字段，即该字段直接保存合同数据。
一个合同可能会占几Mb。
在mysql中保存这种数据，从系统设计的角度来说，本身就不太合理。
- 作者:cherryhua
- 链接:https://blog.cherrylord.com/article/post-19
- 声明:本文采用 CC BY-NC-SA 4.0 许可协议，转载请注明出处。