如何创建一张规范的MySQL表
|
《如何创建一张规范的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 '主键', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',`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脸色. 《如何创建一张规范的MySQL表》是否对您有启发，欢迎查看更多与《如何创建一张规范的MySQL表》相关教程，学精学透。编程之家PHP学院为您提供精彩教程。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |