最近这段时间同事们都休高温假，苦了我们这边今年的新员工了，经常读本号文章的小伙伴应该也看出来了吧，已经接近一个月没有原创了，没办法，一个人干n个人的活，工作赶进度，干货文章也就搁置了，只能慢慢的补上来。
正好最近公司新需求来了，又是假期没人的时候，所以我这块砖就被搬出来了。
新需求也就意味着新设计，原来的表设计也不能满足新需求，需要设计表了。
省略复杂的表设计沟通阶段，本文只记录表在设计过程中需要注意的18个规范。
在建表的过程中如果不注意本文所述的18条小细节，等待后面开发，甚至是系统上线，再进行表的修改那代价你就品把。
所以本文就拿我多年工作中踩过的坑，实际的使用经验来进行分享，非常的有借鉴意义，希望能对你有所帮助，少走弯路少踩坑，省下的时间自由摸鱼。
1.命名
(1) 字段命名
中国人的命名习惯就是中式英语，所以起名时每个人就有每个人的特色，在进行表设计时，尽可能的进行统一，要么全部中文拼音，要么全部英文，切记不要混用，那样真的太难受了。
给表、字段名给个好名字，真的太重要了，最好做到见名知意。
推荐一个起名网站，再不济就去 ChatGPT 起名。
https://unbug.github.io/codelf/
就拿用户名来说举个例子：
正例：
用户名：username反例：
用户名：yong_hu_name,name等需要注意的是，见名知意不错，切记名字也不可太长。
说完了起名，再说一下大小写。
(2) 大小写
在国产数据库中，有的会默认搞成全大写，小写的有的反而不兼容会有 Bug 产生，而站在视觉感官上来说，小写加下划线的形式更加易读，更加直观，所以在命名时，还是那句话，统一规则。
要么全部大写加下划线，要么全部小写加下划线，禁止大小写混用。
推荐全部小写加下划线的形式。
大小写混用的拉出去砍了。
正例：
产品名称：product_name,PRODUCT_NAME反例：
产品名称：product_NAME,PRODUCT_name(3) 分隔符
在给字段起名时，很多场景下单个单词无法满足我们的命名要求，那么多个单词怎么连接呢？
推荐使用_下划线进行连接。
还有驼峰形式或者不使用连接符，这些都是禁止的，有的框架在使用驼峰时会遇到转换问题。
使用连接符可读性太差，这谁家好人能一眼读出来一大长串啊是不是。
正例：
产品名称：product_name,PRODUCT_NAME反例：
产品名称：productname,productName,product@name(4) 关键字
上面说过起名要见名知意，但是也要避免与数据库中的关键字冲突，比如工作中经常用过的status。
比如涉及到关键字的，可以假如业务来进行区分。
创建时间：create_time
更新时间：update_time
删除状态：delete_status,deleted(5) 索引名
索引的命名由索引的类型来分类，因为索引有很多种，主键、唯一、普通、联合、空间等，
通过索引名称可以一眼看出来是普通索引还是唯一索引，或者联合索引那么这个索引的名称就是规范的。
比如联合索引按照字段顺序进行命名，唯一索引加入前缀uniq。
(6) 表名
字段相关名称的说完了，还有表名在提一下，表的命名中，除了体现当前表含义外最好加入业务前缀。
比如订单相关的表用order_前缀。
2.字段类型
对于字段的类型，可选择的太多了，时间类型我们可以使用date，datetime，timestamp，也可以用 bigint 等等。
字符类型的有varchar、char、text等，数字有int，bigint，tinyint，smallint等。
其实这么多都挑花眼了，不知道用哪个，还不如全用varchar，是不是你就是这样做的？
如何选择一个合适的字段类型，就变成了我们不得不考虑的问题了。
比如状态值，10以内的数字，每个数字1个字节就够，使用tinyint即可，如果选了bigint，反而会白白浪费空间。
所以我们可以参考以下原则：
- 满足业务需求的情况下，尽可能选择占用存储空间小的字段类型。
- 字段长度固定的可以选择char，不固定的可以选择varchar。
- 是否这种true或者false的字段，可以使用bit类型。
- 枚举字段可以tinyint类型。
- 主键使用bigint类型。
- 金额字段可以使用decimal或者换算单位存bigint。
- 时间字段使用datetime或者timestamp或者转换时间戳存bigint。
3.字段长度
上面字段类型的选择中提到了长度，接下来我们就重点说一下长度的选择。
varchar(255) 中 255 代表的是字符长度。而在 MySQL中，除了 varchar 和 char 代表的是字符长度之外，其他的类型都是字节长度。
bigint 的实际长度是 8 个字节，bigint(4) 代表当不满 4 个字节的时候，前面填充0（前提是开启了自动填充）。
当超过4个字节时按照实际情况展示。
比如现在的数据是 12345，展示的时候也是展示12345。
但是需要注意的是有的 MySQL 客户端只会展示 4 个字节，比如展示成 1234，所以 bigint(4)中的 4 表示的是显示长度，实际占用还是8个字节。
4.字段个数
大家在看数据库表优化时应该经常听到的就是减少表的字段个数，防止宽表的发生。
所以我们在建表时最好控制一下字段数量，我上家单位涉及的业务类型的表，那真的是字段巨多，对于这种场景，我们可以大表拆分小表，每个表拥有一个共同的唯一ID做主键进行关联。
建议每个表的字段数量控制在20个，如果字段太多，表中数据存储量大了之后严重影响查询效率。
5.主键
不知道你们有没有遇到过，我是遇到过表连个主键都没有，全是普通的列，索引更不用说当然也没有了。
之所以每个表都需要有个主键是因为，主键索引相比其他的索引在查询时可以避免回表，提升查询效率。而且主键索引也是唯一索引，可以作为业务的去重。
在单体数据库中使用默认的自增ID做主键即可，效率还是很高的。在分布式环境中，最好还是使用递增的分布式ID算法，保证全局唯一。
需要注意的是，主键建议保存与业务无关的值，方便后面扩展。
分布式ID生成算法可以看下之前的这篇文章：全网最全的分布式ID分析
6.外键
说完主键说一下外键，这个避免使用吧。
说实话，不好用，外键本来的作用是保证数据的一致，关联表少的时候还没啥，等关联表数量上来之后，在进行删除等操作时，性能是非常差的。
除了外键还有就是触发器以及存储过程，每次一见到开源框架中有这些就头疼。
7.索引
表的主键索引是必须的，对于其他的索引，根据自己的业务场景进行添加即可，但是一个表的索引数量尽量也不要太多，建议单表索引数量不要超过5个。
创建索引时尽可能的考虑索引覆盖、最左前缀、索引下推等优化方案。
需要注意的是对于重复性较高的字段也不建议创建索引，因为这样没意义。
8.唯一索引
这里为什么会单独把唯一索引揪出来呢，还是因为有坑啊，大家在使用唯一索引时，如果是单个字段倒还好，如果是多个字段的，那你一定要注意了，如果有 null 值的出现，唯一性约束可能会失效哦，对于唯一索引的坑下一篇文章单独拿出来讲讲。
9.NOT NULL
建议大家在设计表时，能确保不会出现 NULL值的列设置为 NOT NULL ，这是因为当存储引擎是 Innodb 时，对于NULL值会占用更多的空间，且查询时 NULL 值也会造成索引失效，查询条件只能用时IS NULL或者IS NOT NULL 进行判断。
因此建议能定义为NOT NULL，就定义为NOT NULL。
定义为NOT NULL也有好处，当 INSERT 时如果漏掉了某个字段的值，直接报错提醒出来，多么明显的报错。
还有一种情况就是在现有的表中增加字段，此时历史数据中对于新增加的字段是没有值的，因为设置NOT NULL 的字段尽量也都赋一个默认值。
10.存储引擎
这个应该没什么说的了，大部分都是使用的 Innodb，如果不是，去看看改一下吧。如果你的业务场景适合其他引擎或者你们有自己开发的引擎，当我没说。
如果你不知道为啥使用 Innodb，那么现在你知道了，因为 Innodb 支持事务，且性能越来越优秀。
11.时间字段
下面 就是对数据库中的个别容易产生 bug 的字段类型进行分析。
首先就是时间字段，毕竟时间类型太多了，我们存储时间可以使用 date、datetime、timestamp、varchar、bigint等。
varchar 保存有点就是易读，直接返回给前端，省去了转换的过程。
date 只能保存日期，没有时间，看需求。
datetime 与timestamp 更适合我们保存时间，但是他们也有区别。
(1) datetime
- datetime 存储的时间范围更广，在MySQL中，可以表示从 1000-01-01 到 9999-12-31 之间的日期和时间。
- datetime 不涉及时区转换。
- datetime 不支持自动更新。
(2) timestamp
- timestamp 存储范围较窄，在MySQL 中，可以表示从 1970-01-01 00:00:00 UTC 到 2038-01-19 03:14:07 UTC 的日期时间。
- timestamp 通常以 UTC 存储，所以需要进行时区转换， 比较适合存储跨时区的数据。
- timestamp 在 MySQL 中还可以设置更新时间字段，设置为自动更新。
需要注意的是，在给时间设置默认值时，不要设置0000-00-00 00:00:00 ，防止查询时时间转换报错。
出了上面几种，还可以使用 bigint 存储时间戳的形式，除了可读性以及需要转换外，好像也没啥大问题，你们有用这种方式存储时间的吗。
12.金额字段
金额字段想到的就是浮点类型 float，double，decimal等。
而 float、double 会丢失精度就算了还是别用了，因此还是推荐你用 decimal ，但是需要注意 decimal 使用时的几个坑，还不了解没关系，链接我放下面了。
如果你还是不想用 decimal ，那么再推荐你一种，转换为分或者更小的货币单位，使用 bigint 存储。
13.json字段
这个字段一直是我不想用的，因为兼容不好。如果后期需要切换数据库，假如正好你切换的数据库不支持json类型，那么恭喜你，改代码吧。
这段时间正好新需求，试了一下这个 json 字段，感觉用起来还是不错的，前提是兼容 json 格式。
不好的地方就是对数据的处理查询上还是没有那么方便。
一句话，能不用还是不用吧，建议 json 类型直接存储 varchar，然后代码中转换一下更好，毕竟不用考虑兼容问题啊。
14.大字段
如果你用了json，那么不可避免的会有大字段的可能，大字段的定义就是占用存储空间多的字段。
对于大文本如果直接定义为 text 类型，可能会浪费存储空间。如果业务可以对该字段进行一个最大长度的限制，那么我们可以使用 varchar 类型进行存储，效率更高。
还有一个类型就是 blob ，直接存储文件内容。如果你们也这样做，建议还是换了吧，这个设计有点不合理了，
上个存储保存个文件地址多好。
15.冗余字段
在设计表的时候，为了查询的性能考虑，可能会冗余一些信息字段，比如说某个表中需要记录用户的 userId，当我们需要用户名称的时候，还需要通过 userId 进行关联查询获取 username ，那么我们就可以冗余 username 到我们的表中，提升我们的查询效率。
相当于空间换时间的概念，牺牲这一点空间，减少的却是 join 查询的时间，对查询性能的提升很有帮助。
不能光说好的地方，也有坏的地方，有存储的地方就得有维护，容易造成数据的不一致。
所以在使用中也是根据自己的业务综合评估，选择一个更适合自己业务的方法。
16.注释
表注释以及字段注释，与代码开发中的代码注释没差别，都得写清楚啊，假如是个状态值1、2、3、4、5的，不写注释时间长了你知道是什么意思吗？
需要注意一点哈，写的注释注意与代码中的保持同步，别到了最后一个字段好多个含义，最后弄的自己都要分不清哪个是什么意思那不悲催了。
17.字符集
说了那么多表里面的东西，在说一下底层最基础的编码。MySQL 中支持的编码类型还是很多的，不过这边建议使用utf8mb4，因为 utf8 是没法存储 emoji 表情的，所以被替代也是个趋势吧，使用 ut8mb4 能省去很多的麻烦。
常用的 gbk、utf8、utf8mb4区别如下：
- gbk 包含了 GB2312 标准中的所有字符，不支持 Unicode 标准，所以只能在中国使用，在处理多语言时能力有限。
- utf8是一种可变长度的 Unicode 编码方法，兼容性也好，是一种广泛使用的标准，支持多种语言。缺点就是不支持emoji 表情。
- utf8mb4 是 utf8 的一个扩展，也是 MySQL 中的推荐字符集，尤其是支持表情符号和特殊字符。
18.排序规则
上面说了字符集，排序规则与字符集也是息息相关的。在 mysql 中，如果你的字符集设置的是 utf8mb4，那么你的排序规则也是 utf8mb4 开头的，常用的就是utf8mb4_general_ci，utf8mb4_bin。
- utf8mb4_general_ci 的排序规则对大小写是不敏感的，简单地说就是a与A相等，他会认为这俩是相同的字符。
- utf8mb4_bin是区分大小写的，a与A会被认为是不同的字符。
所以排序规则还是要根据我们的业务场景进行选择，比如用户的登录密码。
总结
本文总结了工作多年建表的一点心得，希望对你有所帮助，下面我们一起回顾一下：
- 表的字符集、排序规则统一，根据自己的业务需要选择合适的编码。
- 在起名上见名知意，不管是表名还是字段名、索引名，统一起名规则。
- 在字段上，控制表字段个数，防止宽表的产生，字段类型上满足业务的前提下选择占用存储空间少的字段，避免大字段的产生，可以使用冗余字段加速查询，对于不了解的类型少用或不用。
- 关于索引方面，每个表必须有主键索引，其次唯一索引使用时注意避坑。
- 最后使用支持事务的 Innodb 引擎。
