MySQL库表设计的技巧有哪些

共计 2792 个字符，预计需要花费 7 分钟才能阅读完成。

MySQL 库表设计的技巧有哪些，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

1.int 类型的选用

整型字段类型包含
tinyint、smallint、mediumint、int、bigint 五种，占用空间大小及存储范围如下图所示：

存储字节越小，占用空间越小。所以本着最小化存储的原则，我们要尽量选择合适的整型，下面给出几个常见案例及选择建议。

根据存储范围选择合适的类型，比如人的年龄用 unsigned tinyint（范围 0~255，人的寿命不会超过 255 岁）；海龟就必须是 smallint，但如果是太阳的年龄，就必须是 int。

若存储的数据为非负数值，建议使用 UNSIGNED 标识，可以扩大正数的存储范围。

短数据使用 TINYINT 或 SMALLINT，比如：人类年龄，城市代码。

存储状态变量的字段用 TINYINT，比如：是否删除，0 代表未删除 1 代表已删除。

主键列，无负数，建议使用 INT UNSIGNED 或者 BIGINT UNSIGNED；预估字段数字取值会超过 42 亿，使用 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 测试表

2. 时间类型的选用

时间字段类型可以选用 datetime 和 timestamp，下面用一张表展示下二者的区别：

timestamp 翻译为汉语即”时间戳”，它是当前时间到 Unix 元年 (1970 年 1 月 1 日 0 时 0 分 0 秒) 的秒数，占用 4 个字节，而且是以 UTC 的格式储存，它会自动检索当前时区并进行转换。datetime 以 8 个字节储存，不会进行时区的检索。也就是说，对于 timestamp 来说，如果储存时的时区和检索时的时区不一样，那么拿出来的数据也不一样。对于 datetime 来说，存什么拿到的就是什么。下面给出几个常见案例及选择建议。

根据存储范围来选取，比如生产时间，保质期等时间建议选取 datetime，因为 datetime 能存储的范围更广。

记录本行数据的插入时间和修改时间建议使用 timestamp。

和时区相关的时间字段选用 timestamp。

如果只是想表示年、日期、时间的还可以使用 year、date、time，它们分别占据 1、3、3 字节，而 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 测试表 
#  插入数据
insert into tb_time (unix_createtime,create_time) values 
(UNIX_TIMESTAMP(now()),now());
#  时间戳数字与时间相互转换
select UNIX_TIMESTAMP(2020-05-06 00:00:00)
select FROM_UNIXTIME(1588694400)

3. 存储 IP 值

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 测试表 
#  插入数据
insert into `tb_ip` (`name`,`inet_ip`) values 
(wang ,INET_ATON( 192.168.0.1)),( lisi ,INET_ATON( 192.168.0.2 
#  相互转换
select INET_ATON( 192.168.0.1 
select INET_NTOA(3232235521);

看完上述内容，你们掌握 MySQL 库表设计的技巧有哪些的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！