MySQL 8.0 如何创建一张规范的表

这一节内容，基于 MySQL8.0 版本，聊一下如何创建一张规范的表。

首先贴出一张相对规范的表结构：

CREATE TABLE student_info (
`id` INT  NOT NULL AUTO_INCREMENT COMMENT '主键',
`stu_name` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '姓名',
`stu_class` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '班级',
`stu_num` INT NOT NULL DEFAULT '0' COMMENT '学号',
`stu_score` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '总分',
`tuition` DECIMAL(5, 2) NOT NULL DEFAULT '0' COMMENT '学费',
`phone_number` VARCHAR(20) NOT NULL DEFAULT '0' COMMENT '电话号码',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
`status` TINYINT NOT NULL DEFAULT '1' COMMENT '1代表记录有效，0代表记录无效',
PRIMARY KEY (`id`),
UNIQUE KEY uniq_stu_num (`stu_num`),
KEY idx_stu_score (`stu_score`),
KEY idx_update_time_tuition (`update_time`, `tuition`)
) ENGINE = INNODB charset = utf8mb4 COMMENT '学生信息表';

这里对上面设置的原因进行解释：

1 表、字段全采用小写。

防止因为大小写问题找不到表或者弄错表。

2 int 类型不再加上最大显示宽度，也就是不适用类似int(11) 的形式。

具体原因可复习：MySQL 5.7 和 8.0 几处细节上的差异。

3 每张表必须显式定义主键，可用自增 int 类型或者有序 UUID。

如果 InnoDB 表没有显式定义主键，则可能会选择唯一索引做为主键，但是唯一索引很可能不是递增的，写入数据时，很可能会导致数据页频繁分裂，从而导致写入效率低和页空间浪费。这也是选择自增 int 类型或者有序 UUID 做为主键的原因。

4 增加 comment 来描述字段和表的含义。比如：status TINYINT NOT NULL DEFAULT '1' COMMENT '1代表记录有效，0代表记录无效'。

方便其他人知道字段的含义，并且不加 comment，可能过一段时间自己都忘记字段作用是什么了。跟写代码加注释一个意思。

5 通常建议包含 create_time 和 update_time 字段，即表必须包含记录创建时间和修改时间的字段。

方便知道记录什么时候创建，什么时候更新的，分析问题的时候很方便。如果有数据归档，也可以根据这两个字段进行归档处理。

6 核心业务表增加记录标记字段。

如上表的 status 字段，写入记录时，默认记录为 1，表示记录有效，如果记录用不到，则把 status 更新成 0，避免物理删除，增加数据安全性。

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

• 能用 int 的就不用 char 或者 varchar；
• 能用 tinyint 的就不用 int；
• 使用 UNSIGNED 存储非负数值；
• 只存储年使用 YEAR 类型；
• 只存储日期使用 DATE 类型。

8 存储精确浮点数必须使用 DECIMAL 替代 FLOAT 和 DOUBLE。

在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得不到正确的结果。

9 尽可能不使用 TEXT、BLOB 类型。

会浪费更多的磁盘和内存空间，非必要的大量大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。如果实在有某个字段过长需要使用 TEXT、BLOB 类型，则建议独立出来一张表，用主键来对应，避免影响原表的查询效率。

10 经常做为条件、排序、关联的字段增加索引。

加快查询速度，降低锁等待时间。

11 具有唯一性的字段，添加成唯一索引，比如上面的 stu_num 字段。

万一业务没完全解决唯一性，那数据库还有一层唯一性保证。

12 几个字段同时作为条件的概率很高时，或者方便查询能走覆盖索引，可以考虑创建联合索引。

走覆盖索引，避免回表，提高查询速度。

13 字符集使用 utf8mb4，无乱码风险；与 utf8 编码相比，utf8mb4 能支持 Emoji 表情。

utf8 的升级版，建议 8.0 都采用这个字符集。

14 存储引擎使用 InnoDB。

99% 的情况使用 InnoDB 就对了。

15 单表字段数目建议小于 30；

字段数太多影响性能，并且不好维护。

大概想到的就是这些，当然，创建一张规范的表，还需要结合线上的环境，比如是否有分库分表、是否会经常归档历史数据等