在mysql中,与日期、时间相关的数据类型有:DATE
、TIME
、DATETIME
、TIMESTAMP
这4种。
基本介绍
DATE
DATE就是日期,日期就是形如YYYY-MM-DD
格式的字符串,它的范围是:1000-01-01
-9999-12-31
。官网上讲,允许使用strings和numbers的值。
在测试中,发现可以有2种形式:
1 | -- 1.strings |
TIME
TIME就是时间,时间就是我们生活中口头上的时间了,形如:HH:MM:SS
,就是小时-分钟-秒数。它不仅可以表示一天当中的具体时间,还可以表示2个时间点之间的差值。比如说,前天和今天相差了2天的时间,就是:48:00:00
的表示。同样,官网上给出了strings和numbers的可用值形式。
1 | -- 1.strings |
实际上,真正的TIME的格式为:HH:MM:SS[.fraction]
,可以附带小数位,如:16:42:55.000000
,考虑到实际中没什么用,不用特意提及。
DATETIME
DATETIME,在官网上给出的定义是:DATE和TIME的结合体。它就表示一个具体的日期+时间。格式为:YYYY-MM-DD HH:MM:SS
,范围是:1000-01-01 00:00:00
-9999-12-31 23:59:59
。同样,可以使用strings和numbers的值形式。
1 | -- 1.strings |
实际上,真正的DATETIME的格式为:DATETIME[(fsp)]
->YYYY-MM-DD HH:MM:SS[.fraction]
,fsp的值为0-6,用来限定fraction的长度。因为在实际中用不上,我们也不用关注。
TIMESTAMP
TIMESTAMP,表示时间戳。它和DATETIME有很多相似的地方。我只关注实际中切实用到的部分。首先,在插入值的语法上和DATETIEM别无二致。能使用strings和numbers的形式。完全和DATETIME的插入举例一样。其次,有一个很大的限制就是,它的范围是:1970-01-01
-2038-01-19
,在这个范围外面的时间是无效的。
至于为什么日期时间的范围有差别,这和数据类型的存储结构有关,这里不提。
实际中的用法
这里,主要讲的是在项目的设计应用中,mysql数据库所选择的日期时间的数据类型。
1、DATE
我们常用的出生日期这一列,就是典型的YYYY-MM-DD
的格式。所以对于这一类的数据,我们都采用DATE。
2、TIMESTAMP
与DATE相比,TIMESTAMP还可以存储TIME部分,对于像一些操作的时间列来说,比如说登录时间、修改时间等这些操作,我们会尽可能地保存更加精确的时间。而且实际中,我们更切实的需求是最后一次的登录时间、修改时间,这也和TIMESTAMP的特性完全符合。
那DATETIME也可以做到,会不会使用DATETIME呢?答案是不会,由于储存空间的原因,DATETIME占用8个字节(8 byte),而TIMESTAMP只占用4个字节(4 byte),相同的需求功能下,我们当然选择使用TIMESTAMP。
当然,TIMESTAMP一个限制就是日期的范围,当我们需要用到2038以后的时间时,就要改用DATETIME了。
再谈TIMESTAMP和DATETIME
除了储存空间上的优势,TIMESTAMP还包含了时区的信息,在不同的时区,TIMESTAMP列显示的日期时间是不同的。当然,现在我们用不到时区,先了解一下。既然我们提到,实际生产中尽量用TIMESTAMP,那么TIMESTAMP还有哪些特性呢?
可以为null。
TIMESTAMP列的默认属性。
当我们创建一个表,包含了TIMESTAMP列时。我们再使用show create table tb_name
来查看建表语句,就会发现有趣的现象。1
`last_login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
建表语句说明:TIMESTAMP列默认是not null的,而且默认值是建表的时间,还有,当该行数据数据更新的时候,这个字段会自动更新为修改时间。
换句话说,对于我们实际中最后一次登录时间等类似的需求,我们只需要在建表时候定义这个字段,那么以后的操作都可以不涉及这个字段了,因为它会自动更新。
对于第2个特性,如果我们想要自定义使用的话,就需要在建表语句中指明TIMESTAMP列的具体行为。下面给出几条参考语句:一般用不到。
1 | -- 1.以后该列不自动更新(取消on update) |