如何创建一张规范的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脸色.