[Mysql教程系列]介绍一下MySQL表设计规范

表设计规范

1、表引擎取决于实际应用场景；日志及报表类表建议用myisam，与交易，审核，金额相关的表建议用innodb引擎。如无说明，建表时一律采用innodb引擎。

2、默认使用utf8mb4字符集，数据库排序规则使用utf8mb4_general_ci，（由于数据库定义使用了默认，数据表可以不再定义，但为保险起见，建议都写上）。

为什么字符集不选择utf8，排序规则不使用utf8_general_ci？

采用utf8编码的MySQL无法保存占位是4个字节的Emoji表情。为了使后端的项目，全面支持客户端输入的Emoji表情，升级编码为utf8mb4是最佳解决方案。对于JDBC连接串设置了characterEncoding为utf8或者做了上述配置仍旧无法正常插入emoji数据的情况，需要在代码中指定连接的字符集为utf8mb4。

3、所有表、字段均应用 comment 列属性来描述此表、字段所代表的真正含义，如枚举值则建议将该字段中使用的内容都定义出来。

4、如无说明，表中的第一个id字段一定是主键且为自动增长，禁止在非事务内作为上下文作为条件进行数据传递。禁止使用varchar类型作为主键语句设计。

5、如无说明，表必须包含create_time和modify_time字段，即表必须包含记录创建时间和修改时间的字段

6、如无说明，表必须包含is_del，用来标示数据是否被删除，原则上数据库数据不允许物理删除。

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

• 能用int的就不用char或者varchar
• 能用tinyint的就不用int
• 使用UNSIGNED存储非负数值。
• 不建议使用ENUM、SET类型，使用TINYINT来代替
• 使用短数据类型，比如取值范围为0-80时，使用TINYINT UNSIGNED
• 存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE

• 时间字段，除特殊情况一律采用int来记录unix_timestamp

  • 存储年使用YEAR类型。
  • 存储日期使用DATE类型。
  • 存储时间（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，DATETIME使用8个字节。
• 建议使用INT UNSIGNED存储IPV4。
• 尽可能不使用TEXT、BLOB类型
• 禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。建议使用其他方式存储（TFS/SFS），MySQL只保存指针信息。
• 单条记录大小禁止超过8k（列长度(中文)3(UTF8)+列长度(英文)1）

datetime与timestamp有什么不同？

相同点：TIMESTAMP列的显示格式与DATETIME列相同。显示宽度固定在19字符，并且格式为YYYY-MM-DD HH:MM:SS。
不同点：

• TIMESTAMP

  • 4个字节储存，时间范围：1970-01-01 08:00:01 ~ 2038-01-19 11:14:07
  • 值以UTC格式保存，涉及时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区。

• datetime

  • 8个字节储存，时间范围：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • 实际格式储存，与时区无关

如何使用TIMESTAMP的自动赋值属性？

• 将当前时间作为ts的默认值：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP。
• 当行更新时，更新ts的值：ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP。
• 可以将1和2结合起来：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

如何使用INT UNSIGNED存储ip？

使用INT UNSIGNED而不是char(15)来存储ipv4地址，通过MySQL函数inet_ntoa和inet_aton来进行转化。Ipv6地址目前没有转化函数，需要使用DECIMAL或者两个bigINT来存储。

8、如无备注，所有字段都设置NOT NULL，并设置默认值；

9、禁止在数据库中存储明文密码

10、如无备注，所有的布尔值字段，如is_hot、is_deleted，都必须设置一个默认值，并设为0；

11、如无备注，排序字段order_id在程序中默认使用降序排列；

12、整形定义中不添加长度，比如使用INT，而不是INT[4]

INT[M]，M值代表什么含义？

注意数值类型括号后面的数字只是表示宽度而跟存储范围没有关系。很多人他们认为INT(4)和INT(10)其取值范围分别是 (-9999到9999)和(-9999999999到9999999999)，这种理解是错误的。其实对整型中的 M值与 ZEROFILL 属性结合使用时可以实现列值等宽。不管INT[M]中M值是多少，其取值范围还是 (-2147483648到2147483647 有符号时)，(0到4294967295无符号时)。

显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。当结合可选扩展属性ZEROFILL使用时默认补充的空格用零代替。例如：对于声明为INT(5) ZEROFILL的列，值4检索为00004。请注意如果在整数列保存超过显示宽度的一个值，当MySQL为复杂联接生成临时表时会遇到问题，因为在这些情况下MySQL相信数据适合原列宽度，如果为一个数值列指定ZEROFILL, MySQL自动为该列添加UNSIGNED属性。

13、使用VARBINARY存储大小写敏感的变长字符串

什么时候用CHAR，什么时候用VARCHAR？

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如，CHAR(30)可以占用30个字符。

• CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。
• VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。

同CHAR对比，VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。

char适合存储用户密码的MD5哈希值，它的长度总是一样的。对于经常改变的值，char也好于varchar,因为固定长度的行不容易产生碎片，对于很短的列，char的效率也高于varchar。char(1)字符串对于单字节字符集只会占用一个字节，但是varchar(1)则会占用2个字节，因为1个字节用来存储长度信息。

推荐

• [[Java网络系列面试题]常见web攻击有哪些？](https://mp.weixin.qq.com/s?__...
• [[Java网络安全系列面试题] GET 和 POST 的区别在哪里？](https://mp.weixin.qq.com/s?__...
• [[Java网络安全系列面试题] HTTP和HTTPS协议区别和联系都有哪些？](https://mp.weixin.qq.com/s?__...
• [[Mysql教程系列]介绍一下MySQL命名规范](https://mp.weixin.qq.com/s?__...

文末

文章收录至
Github: https://github.com/CoderMerlin/coder-programming
Gitee: https://gitee.com/573059382/coder-programming
欢迎关注并star~