选择MySQL数据存储日期类型：深入分析Datetime与Timestamp，及数值时间戳的优劣势

MySQL中日期类型的选择是后端面试中的常见考点，即使在日常开发中也必不可少。无论是记录操作时间、转账时间，还是用户下单时间，时间对于我们的开发至关重要。正确地存储和处理日期不仅能提高系统效率，还能有效避免因时间问题带来的潜在业务影响。因此，深入了解MySQL中可用的日期数据类型至关重要。

切勿使用字符串型存储日期

在学习数据库的初期，许多新手像我一样，曾经尝试用字符串来表示日期，认为这是一种简单且直观的方法。然而，这种做法并不科学，主要存在以下两个问题：

1. 字符串占用的存储空间较大。
2. 字符串存储日期的效率较低（需要逐字符比较），且无法调用日期相关的API进行计算和比较。

Datetime与Timestamp的选择

Datetime和Timestamp是MySQL中两种相似的日期存储类型，它们都可以精确到秒。那么，应如何选择这两者呢？

时区信息的差异

Datetime类型没有时区信息，存储的时间是当前会话所设置的时区的对应时间。这意味着一旦时区发生变化，比如服务器迁移或客户端连接时区设置改变，读取数据库中的时间可能会出现错误。

Timestamp与时区相关，其字段的值会随服务器时区的改变而变化，换句话说，在不同的时区查询同一条记录时，字段值可能会不同。

下面是一个实际的演示：

建表SQL语句：

CREATE TABLE `time_zone_test` (  
  `id` bigint(20) NOT NULL AUTO_INCREMENT,  
  `date_time` datetime DEFAULT NULL,  
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

插入数据：

INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW());  

查看数据：

select date_time,time_stamp from time_zone_test;  

结果：

+---------------------+---------------------+  
| date_time           | time_stamp          |  
+---------------------+---------------------+  
| 2020-01-11 09:53:32 | 2020-01-11 09:53:32 |  
+---------------------+---------------------+  

若我们修改当前会话的时区：

set time_zone='+8:00';  

再次查看数据：

+---------------------+---------------------+  
| date_time           | time_stamp          |  
+---------------------+---------------------+  
| 2020-01-11 09:53:32 | 2020-01-11 17:53:32 |  
+---------------------+---------------------+  

存储空间需求

在MySQL中，Datetime和Timestamp所需的存储空间如下（详见官方文档：https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html）：

在MySQL 5.6.4之前，Datetime和Timestamp的存储空间分别为8字节和4字节。从MySQL 5.6.4开始，它们的存储空间将根据毫秒精度的不同而变化：Datetime的范围为58字节，Timestamp的范围为47字节。

表示范围

Timestamp的时间范围相对较小，仅限到2038年：

• Datetime：1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.499999
• Timestamp：1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.499999

性能考量

由于TIMESTAMP需要进行时区转换，因此从毫秒数转换到TIMESTAMP时，不仅需要调用一个简单的函数，还要调用系统底层的操作系统函数。为了确保操作系统时区的一致性，该系统函数需要进行加锁操作，从而降低了效率。

DATETIME则不涉及时区转换，因此不会遇到此问题。为了避免TIMESTAMP的时区转换问题，建议使用明确的时区，而不是依赖于操作系统的时区设置。

数值时间戳是否更优？

在许多情况下，我们也可以使用INT或BIGINT类型的数值表示时间，即数值时间戳。使用这种存储方式具有Timestamp类型的一些优点，并且在进行日期排序和比较等操作时效率更高，跨系统传输也更加方便，因为存储的仅仅是数值。然而，它的缺点也很明显，即可读性较差，用户无法直观地看到具体时间。

时间戳的定义是从基准时间开始计算，这个基准时间为“1970-1-1 00:00:00 +0:00”，以秒为单位进行整数计时。这个数值是绝对的，无论身处何地，表示的时间都是一致的，并且没有时区的概念，因此在系统中的时间传输无需额外转换，只有在显示给用户时才将其转换为本地时间的字符串格式。

数据库中的实际操作如下：

mysql> select UNIX_TIMESTAMP('2020-01-11 09:53:32');  
+---------------------------------------+  
| UNIX_TIMESTAMP('2020-01-11 09:53:32') |  
+---------------------------------------+  
|                                 1578707612 |  
+---------------------------------------+  
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME(1578707612);  
+---------------------------+  
| FROM_UNIXTIME(1578707612) |  
+---------------------------+  
| 2020-01-11 09:53:32       |  
+---------------------------+  
1 row in set (0.01 sec)  

总结

在MySQL中，如何存储时间是一个重要的问题：选择Datetime、Timestamp还是数值时间戳？并不存在绝对的答案。尽管许多程序员认为数值型时间戳高效且兼容性强，但也有人认为它的表现不够直观。《高性能MySQL》的作者推荐使用Timestamp，主要是因为数值表示时间不够直观。以下是三种存储方式的简单对比：

选择最合适的存储方式应根据实际场景来决定。