lixmet
lixmet
发布于 1周前

MySQL:datetime与timestamp的区别及使用选择

datetime与timestamp的区别

1、存储空间不同
在MySQL,timestamp在内部存储为整型,占用4个字节,而datetime占用8个字节。

2、存储方式不同
timestamp存储时,会从插入时间的客户端时区转换为UTC(世界标准时间)时间存储,而datetime不会做时间的转换直接存储。

3、存储的时间范围不同

  • timestamp:UTC时间,1970-01-01 00:00:01.000000到 2038-01-19 03:14:07.999999
  • datetime:与时区无关,1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999

4、自动更新
在同一个表里,允许有一个字段自动更新时间。如

CREATE TABLE `tb` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `mydatetime` datetime DEFAULT NULL,
 `mytimestamp` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

其中,mytimestamp为自动更新时间,ON UPDATE CURRENT_TIMESTAMP。

使用选择

在datetime和timestamp这几个不同里,存储方式和时间范围的不同会影响到我们对两个类型的选择。

timestamp始终会存储为UTC时间,也就是说它是一个固定时间,只是在不同的时区表示不同而已。而datetime则是一个静态的时间,与时区无关。

在使用选择上需要知道不同的存储方式会带来的问题

问题一

在跨时区做数据迁移,datetime类型的数据不能自动修正,需要我们使用其他方式做数据修正。因为它是一个静态的时间。

示例

CREATE TABLE `time_test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `mydatetime` datetime DEFAULT NULL,
 `mytimestamp` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

查看系统时区

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name  | Value |
+------------------+--------+
| system_time_zone | CST  |
| time_zone    | SYSTEM |
+------------------+--------+

往time_test插入数据

mysql>INSERT INTO time_test (mydatetime,mytimestamp) VALUES (NOW(),NOW());
mysql>SELECT * FROM time_test;
+----+---------------------+---------------------+
| id | mydatetime       | mytimestamp      |
+----+---------------------+---------------------+
| 1 | 2017-12-08 22:14:56 | 2017-12-08 22:14:56 |
+----+---------------------+---------------------+

修改时区

mysql>SET time_zone='+9:00';
mysql>SELECT * FROM time_test;
+----+---------------------+---------------------+
| id | mydatetime       | mytimestamp      |
+----+---------------------+---------------------+
| 1 | 2017-12-08 22:14:56 | 2017-12-08 23:14:56 |
+----+---------------------+---------------------+

把时区修改为“+9:00”,即东九区,mytimestamp时间显示比之前多了一个小时。而mydatetime没有变化

继续插入一行数据

mysql>INSERT INTO time_test (mydatetime,mytimestamp) VALUES (NOW(),NOW());
mysql>SELECT * FROM time_test;
+----+---------------------+---------------------+
| id | mydatetime       | mytimestamp      |
+----+---------------------+---------------------+
| 1 | 2017-12-08 22:14:56 | 2017-12-08 23:14:56 |
| 2 | 2017-12-08 23:17:53 | 2017-12-08 23:17:53 |
+----+---------------------+---------------------+

注意,此时为东九区,mydatetime插入的也是东九区当时的时间,而第一行数据仍然为旧的数据。

修改为东八区

mysql>SET time_zone='+8:00';
mysql>INSERT INTO time_test (mydatetime,mytimestamp) VALUES (NOW(),NOW());
mysql>SELECT * FROM time_test;
+----+---------------------+---------------------+
| id | mydatetime       | mytimestamp      |
+----+---------------------+---------------------+
| 1 | 2017-12-08 22:14:56 | 2017-12-08 22:14:56 |
| 2 | 2017-12-08 23:17:53 | 2017-12-08 22:17:53 |
+----+---------------------+---------------------+

此时,mytimestamp两个时间都显示为东八区的时间,而mydatetime还是原来插入的时间。

问题二

timestamp所能表示的时间范围小:UTC时间,1970-01-01 00:00:01.000000到 2038-01-19 03:14:07.999999

解决

timestamp所能表示的时间范围小是MySQL设计上决定的,我们在应用层解决不了。datetime在跨时区的问题我们可以在应用层解决。

解决的思路和存储timestamp的方式是一样的,在应用层把datetime的时间统一转为UTC,然后我们再存进MySQL。如果需要在不同时区显示,只需要在应用层把UTC时间转换为时区时间即可。