【MySQL数据库开发设计规范】之字段设计规范

欢迎点开这篇文章，自我介绍一下哈，本人姑苏老陈 ，是一名JAVA开发老兵。

本文收录于 《MySQL数据库开发设计规范》专栏中，该专栏主要分享一些关于MySQL数据库开发设计相关的技术规范文章，定期更新，欢迎关注，谢谢 :-）

一、前言

在我们对数据库技术方案设计的时候，我们是否有自己的设计理念或者原则，还是更多的依据自己的直觉去设计，是否曾经懊悔线上发生过的一次低级故障，可能稍微注意点就可以避免，是否想过怎么才能很好的避免，规范的价值正是我们工作的检查清单，需要我们不断从错误中积累有效经验来指导未来的工作。

本文介绍一下MySQL数据库开发设计规范之字段设计规范。

PS：以下规范在大型互联网公司经过了充分的验证，尤其适用于并发量大、数据量大的业务场景。

二、字段设计规范

1.【强制】必须把字段定义为NOT NULL并且提供默认值；

说明：NULL字段很难查询优化，NULL字段的索引需要额外空间，NULL字段的复合索引无效；

2.【强制】禁止使用ENUM，可使用TINYINT代替；

说明：枚举ENUM类型，在数据库迁移、数据库索引使用等等方面，会带来一下麻烦问题，不建议使用。

3.【强制】禁止使用TEXT、BLOB类型(如果表的记录数在万级以下可以考虑)

说明：BLOB与TEXT是为了存储极大的字符串而设计的数据类型，通常以外部存储方式保存，而不是像固定长度或可变长度字段那样以行内存储的方式，从而，会出现性能方面的问题，所以，不建议使用；

4.【强制】必须使用varchar(20)存储手机号；

说明：存储手机号必须使用varchar(20)数据类型，以确保能够容纳各种格式的手机号码。

5.【推荐】禁止使用小数存储国币、使用“分”作为单位，这样数据库里就是整数了。

说明：阿里云推荐使用“分”作为单位，在查看金额相关的数据时，不够直观，需要换算成单位元。所以，这里仅推荐，不做强制使用。

6.【强制】用DECIMAL代替FLOAT和DOUBLE存储精确浮点数；

说明：decimal类型在存储小数时可以提供更高的精度，并避免了由于浮点数运算而引起的精度损失；

7.【推荐】使用UNSIGNED存储非负整数；

说明：同样的字节数，使用UNSIGNED存储的数值范围更大；

8.【推荐】建议使用INT UNSIGNED存储IPV4；

说明：用UNSINGED INT存储IP地址占用4字节，CHAR(15)则占用15字节。另外，计算机处理整数类型比字符串类型快一些。

另外，使用INT UNSIGNED存储IPV4地址时，可以通过MySQL函数inet_ntoa和inet_aton来进行转化。

例如:
SELECT INET_ATON('192.168.172.3'); 3232279555 SELECT INET_NTOA(3232279555); 192.168.172.3

9.【推荐】字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量；

说明：这么做不仅仅是为了节约空间，也是为了提高数据库性能；

10.【推荐】核心表字段数量尽可能地少，有大字段要考虑拆分；

说明：核心表访问频率较高，所以字段数量越少，性能越好；

11.【推荐】适当考虑一些反范式的表设计，增加冗余字段，减少JOIN；

说明：比如电商项目中的商品名称字段，查询频率较高，可以考虑冗余；

12.【推荐】资金字段考虑统一*100处理成整型，避免使用decimal浮点类型存储；

13.【推荐】使用VARBINARY存储大小写敏感的变长字符串或二进制内容

说明：VARBINARY默认区分大小写，没有字符集概念，速度快；

14.【参考】INT类型固定占用4字节存储；

说明：INT(4)仅代表显示字符宽度为4位，不代表存储长度。数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如INT(3)默认显示3位，空格补齐，超出时正常显示，Python、Java客户端等不具备这个功能；

15.【参考】区分使用DATETIME和TIMESTAMP

说明：TIMESTAMP占用的存储空间小一些，同时具有自动赋值以及自动更新的特性。但TIMESTAMP的取值范围截止到2038年，所以，这里不推荐使用TIMESTAMP；

16.【推荐】将大字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据

说明：有利于有效利用缓存，防⽌读入无用的冷数据，较少磁盘IO，同时保证热数据常驻内存提⾼高缓存命中率；比如订单表，当表字段数量超过50，考虑分离冷热数据；

17.【参考】VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N；

18.【参考】VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存；

19.【推荐】VARCHAR(N)，当N>5000时，考虑使用BLOB类型；

20.【推荐】使用短数据类型，比如取值范围为0~80时，使用TINYINT UNSIGNED；

说明：TINYINT数据类型是一个8位整数类型，用于存储从-128到127（有符号）或从0到255（无符号）的整数。

21.【强制】存储状态，性别等，用TINYINT类型；

说明：比如性别字段，stu_sex  tinyint(1)  DEFAULT '3'  COMMENT '1男，0女，3未知'；

22.【强制】所有存储相同数据的列名和列类型必须一致；

说明：比如在多个表中的字段如user_id，它们的字段名称和字段类型必须一致，否则进行join关联查询时，会进行类型转换，查询效率低；

23.【推荐】优先选择符合存储需要的最小数据类型；

24.【推荐】如果存储的字符串长度几乎相等，使用char定长字符串类型；

三、总结

以上介绍了在进行MySQL数据库表字段设计时，可以遵循的一些技术规范。希望对大家有帮助，谢谢 :-）

另外，经常有人会问MySQL数据库varchar(100)字段，能存储多少个中文汉字呢？

这个和MySQL数据库版本以及字符集有关系的。

其中，MySQL5.0版本以上，varchar(100)，代表100字符。无论存放的是数字、字母还是UTF8汉字(每个汉字3字节)，都可以存放100个。MySQL5.0版本以下，varchar(100)，代表100字节。UTF8汉字(每个汉字3字节)，能存储33个。

PS：不同字符集编码格式和占用字节空间，对应的关系如下：

附本文参考资料：

• 阿里云官方知乎：https://www.zhihu.com/org/a-li-yun-97-77
• 阿里云开发者官方社区：https://developer.aliyun.com/
• 阿里开发者官方CSDN社区：https://blog.csdn.net/alitech2017?type=blog
• 阿里云云栖号CSDN：https://blog.csdn.net/yunqiinsight/category_10231626.html
• 阿里巴巴技术团队发布的《JAVA开发手册》泰山版
• 阿里云开发者官方微信公众号

如果您对文章中内容有疑问，欢迎在评论区进行留言，我会尽量抽时间给您回复。如果文章对您有帮助，欢迎点赞、收藏。您的点赞，是对我最大的支持和鼓励，谢谢 :-）