MySQL表设计优化与索引 (九)

日期和时间类型

MySQL有多个表示各种日期和时间值的数据类型, 比如YEAR和DATE. MySQL存储时间的最精确粒度是秒。 然而, 能做微妙粒度的临时计算, 我们会列出一些绕过存储引擎限制的一些方法。

许多时间类型都没有可替换类型, 因而不存在什么是最佳选择这么一说。 唯一的问题是当需要一起存储日期和时间时如何做?MySQL提供了两个非常类似的数据类型来做这个事情: DATETIME和TIMESTAMP. 多大多数应用来说, 两个都行, 但是在某些场合, 一个可能比另一个更合适:

DATETIME

这个类型能够存储很大范围的值, 从1001年到9999年, 准确到秒的精度。它把日期和时间打包成整数以YYYYMMDDHHMMSS 格式表示, 与时区无关。用8个字节的存储空间。

缺省情况下, MySQL以可排序,无歧义的格式显示DATETIME值, 比如 2008-01-16 22:37:08, 这是ANSI下标准显示日期时间的方式。

TIMESTAMP

如名字所指出的,TIMESTAMP 类型存储从1970年1月1日(格林威治时间)到目前为止经过的秒数 –与UNIX时间戳一样。TIMESTAMP 使用4个字节来存储值, 所能表示的范围以比DATETIME要小: 从1970年到2038年。MySQL提供了 FROM_UNIXTIME( ) 和 UNIX_TIMESTAMP( ) 两个函数来进行Unix时间戳与日期类型的相互转换。

新版本的MySQL中TIMESTAMP 采用了与DATETIME 一样的格式来表示值,但是老版本的MySQL没有在各个部分件显示分隔符。 这只是一个显示格式的区别而已,TIMESTAMP 的存储格式在各个版本与DATETIME一致。

TIMESTAMP 对值的显示与时区相关,MySQL服务器,操作系统和客户端连接都有相应的时区设置。

因此,对于与GMT有5个小时时差的东部时间,一个值为0的TIMESTAMP 类型实际显示的内容可能会是1969-12-31 19:00:00。

TIMESTAMP 也有一些DATETIME所不具备的一些属性。 缺省情况下, 插入记录时,MySQL会把第一个没指定具体值得TIMESTAMP 类型的字段自动设置为当前值,在修改时, 如果没有在修改语句中显式的指定值, 第一个DATESTAMP类型的字段的值也会被更行为当前值。也可以通过配置修改修改和插入记录时对TIMESTAMP 列的处理行为。最后, TIMESTAMP列缺省不为NULL, 这与其他数据类型不一样。

除了一些特殊行为,在一般情况下,如果能够使用TIMESTAMP,就使用它, 因为它比DATETIME的空间效率要高。有时候,有人把Unix时间戳存储成整数, 但是实际上没有任何好处,因为格式转换很不方便,我们不推荐这么做。

如果需要存储比秒精度更高的日期和时间数据怎么办呢?MySQL当前并没有提供合适的数据类型, 但是可以根据需要选择自己的存储格式:可以使用BIGINT类型存储毫秒精度的数据,或者使用DOUBLE类型,把秒后面的数值当做小数点后面的小数部分。 这两种方法都不错。