**前言：** 在我们项目开发中，数据库及表的设计可以说是非常重要，我遇到过很多库表设计比较杂乱的项目，像表名、字段名命名混乱、字段类型设计混乱等等，此类数据库后续极难维护与拓展。我一直相信只有优秀的库表设计才能发挥出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 类型。 下面给出建表语句示范： ```sql 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： ```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，转换后的数字是连续的，提高了查询性能，占用空间更小。 ```sql 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); ``` **总结：**  本篇文章分享了几个库表设计及字段类型选取的建议。这些案例都是常常见到的场景，对于int类型及时间类型的选取，本文也根据常见场景给出相关建议，希望大家读完这篇文章有所收获。其实库表设计是件复杂的事情，需要在项目前期多方人员共同规划讨论。还是那句话，只有优秀的库表设计才能发挥出MySQL最大的性能。