前言:
在我们项目开发中,数据库及表的设计可以说是非常重要,我遇到过很多库表设计比较杂乱的项目,像表名、字段名命名混乱、字段类型设计混乱等等,此类数据库后续极难维护与拓展。我一直相信只有优秀的库表设计才能发挥出MySQL最大的性能,前面有篇文章也分享了数据库的使用规范,本篇文章主要讲几个库表设计的小技巧,希望对大家有所启发。
整型字段类型包含 tinyint、smallint、mediumint、int、bigint 五种,占用空间大小及存储范围如下图所示:
存储字节越小,占用空间越小。所以本着最小化存储的原则,我们要尽量选择合适的整型,下面给出几个常见案例及选择建议。
下面给出建表语句示范:
CREATE TABLE `tb_int` (
`increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`stu_age` tinyint unsigned NOT NULL COMMENT '学生年龄',
`is_deleted` tinyint unsigned DEFAULT '0' COMMENT '0:未删除 1:删除',
`col1` bigint NOT NULL COMMENT 'bigint字段',
PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='int测试表';
时间字段类型可以选用datetime和timestamp,下面用一张表展示下二者的区别:
timestamp翻译为汉语即"时间戳",它是当前时间到 Unix元年(1970 年 1 月 1 日 0 时 0 分 0 秒)的秒数,占用4个字节,而且是以UTC的格式储存,它会自动检索当前时区并进行转换。datetime以8个字节储存,不会进行时区的检索。也就是说,对于timestamp来说,如果储存时的时区和检索时的时区不一样,那么拿出来的数据也不一样。对于datetime来说,存什么拿到的就是什么。下面给出几个常见案例及选择建议。
如果timestamp字段经常用于查询,我们还可以使用MySQL内置的函数FROM_UNIXTIME()、UNIX_TIMESTAMP(),将日期和时间戳数字来回转换,转换后可以用 INT UNSIGNED 存储时间,数字是连续的,占用空间更小,并且可以使用索引提升查询性能。下面给出示范建表语句及时间戳相关转换SQL:
CREATE TABLE `tb_time` (
`increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`col1` datetime NOT NULL DEFAULT '2020-10-01 00:00:00' COMMENT '到期时间',
`unix_createtime` int unsigned NOT NULL COMMENT '创建时间戳',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`increment_id`),
KEY `idx_unix_createtime` (`unix_createtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='time测试表';
IP值一般使用char或varchar进行存储,但是当进行查找和统计时,字符类型不是很高效。MySQL数据库内置了两个IP相关的函数INET_ATON()、INET_NTOA(),可以实现 IP 地址和整数类型的转换。转换后使用可以INT UNSIGNED 来存储IP,转换后的数字是连续的,提高了查询性能,占用空间更小。
CREATE TABLE `tb_ip` (
`increment_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(100) NOT NULL COMMENT '姓名',
`inet_ip` int(10) unsigned NOT NULL COMMENT 'IP',
PRIMARY KEY (`increment_id`),
KEY `idx_inet_ip` (`inet_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ip测试表';
总结:
本篇文章分享了几个库表设计及字段类型选取的建议。这些案例都是常常见到的场景,对于int类型及时间类型的选取,本文也根据常见场景给出相关建议,希望大家读完这篇文章有所收获。其实库表设计是件复杂的事情,需要在项目前期多方人员共同规划讨论。还是那句话,只有优秀的库表设计才能发挥出MySQL最大的性能。