最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

如何创建一张规范的MySQL表

XAMPP下载 中文小张 109浏览 0评论

之前整理过一份稍微规范一点的建表语句,在这里分享下,如果有更好的建议,可以在文章下面留言哟。

如下:

  • 规范表示例

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表情。

转载请注明:XAMPP中文组官网 » 如何创建一张规范的MySQL表