如何创建一张规范的MySQL表

到底怎样才能创建一张规范的MySQL表？

为什么一定需要有无业务意义的主键，并且还需要自增？

应该在哪些字段添加索引？

或许有些人会有以上类似的疑问。这几天通过自己的整理加上好友的指导，整理了一份稍微规范一点的建表语句。

如下：

规范表示例

MySQL 5.5

CREATE TABLE student_info (
    `id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    `stu_name` VARCHAR (10) NOT NULL DEFAULT '' COMMENT '姓名',
    `stu_class` VARCHAR (10) NOT NULL DEFAULT '' COMMENT '班级',
    `stu_num` INT (11) 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` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    `update_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' 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 '学生信息表';

MySQL 5.6及以上版本

CREATE TABLE student_info (
    `id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    `stu_name` VARCHAR (10) NOT NULL DEFAULT '' COMMENT '姓名',
    `stu_class` VARCHAR (10) NOT NULL DEFAULT '' COMMENT '班级',
    `stu_num` INT (11) 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、表的命名：使用有意义的英文词汇，词汇中间以下划线分割，全部采用小写；如示例表表名student_info

2、表必须有无符号int型自增主键，对应示例表中id字段。

必须得有主键的原因：

采用RBR模式复制，无主键的表删除，会导致备库夯住

使用自增的原因：

数据写入可以提高插入性能，避免page分裂，减少表碎片。

3、必须把字段定义为NOT NULL并且提供默认值

原因：

a.null的列使索引、统计都更加复杂，使优化更加困难

b.NULL并不是空值，也会占用空间，所以在MySQL进行比较时，NULL会参与字段比较，所以对效率有一部分影响

4、所有表、字段都应该有 comment ，来描述表、字段所代表的含义，方便同事查看。

5、能用SMALLINT或者tinyint的情况就不用int，如字段 stu_score就使用的是SMALLINT

原因：使用SMALLINT或者tinyint能节约存储空间

6、涉及到钱的字段建议使用DECIMAL，如示例表字段tuition

7、电话号码建议使用varchar(20)，如示例表字段phone_number

原因：

a.涉及到区号或者国家代号，可能出现+-()

b.不会有谁用手机号做运算吧

c.varchar可以支持模糊查询

8、表建议增加create_time和update_time，以记录某条数据的创建时间和修改时间。

注意：这里5.5和5.6有区别，5.5使用的是TIMESTAMP，并且5.5不支持多个CURRENT_TIMESTAMP 默认值，因此如上示例设计；5.6版本使用了datetime，因为datetime支持的范围更广（范围为：'1000-01-01 00:00:00'到'9999-12-31 23:59:59'），并且create_time和update_time两个字段都设置了CURRENT_TIMESTAMP（从5.6.5开始支持多个字段默认值设置为CURRENT_TIMESTAMP）

原因：增加这两个字段方便统计和归档。

9、表建议包含一个状态标记字段，来标识数据是否被删除，而不使用物理删除；比如示例表字段status。

10、不建议使用ENUM，使用TINYINT来代替；如示例表字段status使用的是TINYINT类型。

原因：增加新的ENUM值要做DDL操作

11、使用唯一索引约束字段值唯一的数据，唯一索引以uniq_字段名方式命名；如示例表中的uniq_stu_num

12、在经常作为查询条件的字段上添加索引，普通索引以idx_字段名方式命名；如示例表中的idx_stu_score

13、经常同时出现在where条件中的几个字段可以放在联合索引中；如idx_update_time_tuition；需要注意的是应该把选择性更大的列放在联合索引的最左边。

14、尽量不使用TEXT、BLOB类型

原因：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

15、建议使用innodb存储引擎

原因：innodb支持事务，是行级锁，并发性能更好、CPU及内存缓存页优化使得资源利用率更高。

16、建议使用utf8mb4字符集

原因：万国码，无乱码风险；与utf8编码相比，能支持Emoji表情。