DATETIME类型和TIMESTAMP类型的差别

DATETIME类型

DATETIME类型存放数据范围从1001年到9999年，精度为秒，将时间和日期封装成格式为YYYYMMDDHHmmss的整数中，使用8个字节的存储空间。
DATETIME类型不存放任何时区相关信息。
DATETIME类型默认为NULL

 

TIMESTAMP类型

TIMESTAMP类型保存从1970-01-01 00:00（格林尼治标准时间）以来的秒数。
TIMESTAMP精确到秒，使用4个字节的存储空间，存储范围从1970年到2038年。
TIMESTAMP列默认为NOT NULL


在MySQL中，可以使用函数UNIX_TIMESTAMP和FROM_UNIXTIME来进行整数秒和TIMESTAMP时间的转换。
如：
SELECT UNIX_TIMESTAMP(NOW());
SELECT FROM_UNIXTIME(1468602420);

 

 

Timestarmp列可以设置两个属性：
1、DEFAULT CURRENT_TIMESTAMP 表示插入记录行时，如果未对该列指定值，则使用当前时间来为该字段赋值
2、ON UPDATE CURRENT_TIMESTAMP 表示在更新记录时，如果为更新该事件戳列，使用当前时间来更新该字段

 

 

如果在定义时间戳字段时列定义为：c1 timestamp DEFAULT CURRENT_TIMESTAMP，那么c1列仅在插入且未指定值时会被赋值为当前时间，在更新且未指定更新值时该时间戳列不会发生值变化。

CREATE TABLE tb1002(id int primary key,c1 timestamp DEFAULT CURRENT_TIMESTAMP); insert into tb1002(id) select 1 union select 2; update tb1002 set id=3 where id=1; select * from tb1002; +----+---------------------+
| id | c1                  |
+----+---------------------+
|  2 | 2016-07-29 23:53:51 |
|  3 | 2016-07-29 23:53:51 |
+----+---------------------+
从结果可以发现更新操作未更新时间戳列

 

 

如果在定义时间戳字段时列定义为：c1 timestamp ON UPDATE CURRENT_TIMESTAMP，那么C1列仅在更新且未指定更新值时会被更新为当前时间，而在插入时如果未指明插入值则将该事件戳列赋值为‘0000-00-00 00:00:00’

CREATE TABLE tb1003(id int primary key,c1 timestamp ON UPDATE CURRENT_TIMESTAMP); insert into tb1003(id) select 1; select * from tb1003; +----+---------------------+
| id | c1                  |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
+----+---------------------+ 从结果可以发现插入时列未被赋值为当前时间而被赋值为'0000-00-00 00:00:00’。

 

 

当时间戳字段被设置默认值,如在定义时间戳字段时列定义为：c1 timestamp default 0,，则不会为字段增加默认NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP属性

CREATE TABLE tb1004(id int primary key,c1 timestamp default 0); 查看建表语句变为： CREATE TABLE `tb1004` ( `id` int(11) NOT NULL, `c1` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 insert into tb1004(id) select 1 union select 2; update tb1004 set id=3 where id=1; select * from tb1004; +----+---------------------+
| id | c1                  |
+----+---------------------+
|  2 | 0000-00-00 00:00:00 |
|  3 | 0000-00-00 00:00:00 |
+----+---------------------+ 从结果可以发现，无论是插入还是更新，都没有将时间戳字段更新为当前时间，而是使用默认值！