在设计数据库的时候,经常需要用到时间这个字段来存储创建时间或者是更新时间等,这样我们可以很清楚地了解每一条数据的操作时间,以便进一步对数据进行分析。MYSQL 存储时间有 datetime、timestamp、int 及 date 类型,那到底用哪一种好呢?下面我们就来分析一下。
date:翻译成中文是日期的意思,它只保存日期,不保存时分秒。MySQL检索并且以 'YYYY-MM-DD' 格式显示 date 值,支持的范围是 '1000-01-01' 到 '9999-12-31' 。 这用来存储数据的创建时间或更新时间精度显然是不够的,直接 pass。
datetime:MySQL 检索并且以 'YYYY-MM-DD HH:MM:SS' 格式显示 datetime 值,支持的范围是 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' 。它占用的存储空间是 8 个字节,这显然是大了点。
timestamp:是时间戳的意思,它以 UTC(世界统一时间)格式保存,存储时对当前的时区进行转换,检索时再转换回当前的时区。它的取值范围是 '1970-01-01 00:00:00' 到 '2037-12-31 23:59:59',只占用 4 个字节的存储空间,并且 timestamp 具有自动初始化和自动更新的特性,如我们可以设置字段 update_time 的默认值为 CURRENT_TIMESTAMP,并根据当前时间戳更新,那么在每次插入数据的时候,就不用管 update_time 字段了,它会自动填充当前时间,而更新数据的时候,update_time 字段也会自动修改为当前更新时间。
为了更清楚地说明 timestamp 的设置,下面是创建数据库的简单例子:
CREATE TABLE `test` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT,
`create_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
这里有一个地方要注意,默认值为 CURRENT_TIMESTAMP 并根据当前时间戳更新只能设置一个,因此 create_time 字段只能设置默认值为 '0000-00-00 00:00:00'。
timestamp 真是太方便了,曾经有很大一段时间,我一直使用 timestamp 类型来定义时间字段,直到后来我发现了 int。
int:相信大家对这个类型非常熟悉。int 用于存放数字,占用 4 个字节的存储空间,带符号的范围从 -2,147,483,648 到 2,147,483,647,无符号的范围从 0 到 4,294,967,295。因此 int 最大可存储的日期为 '2106-02-07 14:28:15'。受到语言的限制,如果使用 PHP 的 date() 函数将时间戳转化为时间,其有效的时间戳典型范围是格林威治时间 1901 年 12 月 13 日 20:45:54 到 2038 年 1 月 19 日 03:14:07。(此范围符合 32 位有符号整数的最小值和最大值)。不过在 PHP 5.1 之前此范围在某些系统(如 Windows)中限制为从 1970 年 1 月 1 日到 2038 年 1 月 19 日。MYSQL的时间戳转换函数FROM_UNIXTIME的最大值为 2038-1-19 11:14:7,不过这在目前还是够用的,至少比 timestamp 大。
使用int类型最大的原因是其检索速度更快,由于其是数字,对于时间的比较直接是大小比较,建立索引占用的空间也很小。可能有人会说它可读性极差,无法直观的看到数据,小编认为直接查看数据库的数据本身就有问题,这些数据不应该通过应用程序展示出来的吗?那应用程序完全可以将时间戳转化为时间再显示,没有任何问题。因此,推荐使用 int 类型存储时间字段。
今天刚好是母亲节,祝天下所有的母亲节日快乐!