时间类型数据存储建议（Mysql）

Excellent的崽子

已于 2023-05-22 17:42:34 修改

阅读量1.4k

点赞数 1

文章标签： mysql 数据库 java 数据结构

于 2023-04-12 17:40:21 首次发布

本文链接：https://blog.csdn.net/qq_52946505/article/details/130112770

版权

Datetime 类型和 Timestamp 类型的使用和区别

MySql 中比较常见的时间类型为 Datetime 和 Timestamp 格式，那它们二者有什么区别呢？为什么一般使用 Timestamp 比较多一点呢？下面我们就来详细讲一讲。
首先说一下 Datetime 和 Timestamp 比较重要的区别，也是很多公司数据库为什么使用 Timestamp 而很少使用 Datetime 的原因。

Datetime 类型是不区分时区的，DateTime 类型保存的时间都是当前会话所设置的时区对应的时间。也就是不管你当前使用的时区怎么改变，它的值都不会根据时区的不同而改变，也就会导致改变时区以后从数据库读取出来的数据是错误的。
Timestamp 类型是区分时区的 , Timestamp 类型保存的时间也都是当前会话所设置的时区对应的时间。但是如果你后面更改了当前的时区，那么它的值是会跟着对应的时区进行改变的。

下面举一个实际的例子演示一下!
建表语句：

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;

插入语句：

-- 插入当前时间， 我们当前数据库的时区为 "SYSTEM"
-- 可以通过 SELECT @@session.time_zone; 来查询当前会话时区
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 |
±--------------------±--------------------+
| 2023-04-12 16:01:04 | 2023-04-12 16:01:04 |
±--------------------±--------------------+

在这里我们可以看到 DateTime 和 Timestamp 保存的时间是相同的，接下来我们改变当前会话的时区以后再来看一下。

更改当前会话时区：

-- 修改当前会话时区
SET time_zone = "+00:00";

再次查询数据以后，输出结果：

±--------------------±--------------------+
| date_time | time_stamp |
±--------------------±--------------------+
| 2023-04-12 16:01:04 | 2023-04-12 08:01:04 |
±--------------------±--------------------+

可以看到，这个时候 DateTime 和 Timestamp 类型的值就已经不一样了，Timestamp 根据当前时区的变化更新了对应的时间，而 DateTime 没有更新。所以说平时开发中还是建议使用 Timestamp 类型作为数据库时间类型，避免这种时区问题导致数据错误的情况出现。

接下来说一说 DateTime 和 Timestamp 类型另一个区别：存储空间不同。
DateTime 需要 8 字节的存储空间，Timestamp 类型只需要 4 字节的存储空间。

可以从下面数据看到对 MySql 这两个类型的介绍：

总结 Datetime 和 Timestamp 的区别：

Datetime 类型数据和时区无关，值不会因为时区的变化变化，Timestamp 类型的数据和时区有关，值会根据时区的变化而变化。
耗能不同，Datetime 类型的存储空间占比 8 字节，相比于 Timestamp 类型存储空间占比 4 字节要比较耗能。

使用字符串和数值型 String 或者 int、bigint 存储时间合适吗

使用字符串存储时间

我们有时候为了省事，简单明了等一些乱七八糟的原因会使用字符串类型去存储时间格式的数据，这个明显是不正确的，会导致以下三种问题：

时区问题，如果使用字符串类型保存时间格式，那么根本就没有时区的一个概念了，不管时区怎么变化数据都是刚存进来的样子，不会根据时区的改变而改变。
**占比空间大小问题，**字符串类型时间格式要占比的存储空间基本是要比使用专门的时间格式要大的，所以比较耗能。
**时间比较问题， **当我们需要对时间进行比较或者向后加几天向前减几天的时候，就会很难办，也没有办法使用时间类型 API。

所以总结下来一句话：不建议使用！

使用数值存储时间

也有小伙伴会使用数值去保存时间到数据库，一般都是获取时间的时间戳，然后以时间戳的形式存储。这会导致什么问题呢？会导致可读性差，如果只看数据库的情况下完全看不出来当前的一个具体时间是多少。
但是这种存储方式也有优点，使用它的进行日期排序以及对比等操作的效率会更高，跨系统也很方便，毕竟只是存放的数值，不管怎么跨时区，时间戳都是不会变的。

时间戳概念：

时间戳的定义是从一个基准时间开始算起，这个基准时间是「1970-1-1 00:00:00 +0:00」，从这个时间开始，用整数表示，以秒计时，随着时间的流逝这个时间整数不断增加。这样一来，我只需要一个数值，就可以完美地表示时间了，而且这个数值是一个绝对数值，即无论的身处地球的任何角落，这个表示时间的时间戳，都是一样的，生成的数值都是一样的，并且没有时区的概念，所以在系统的中时间的传输中，都不需要进行额外的转换了，只有在显示给用户的时候，才转换为字符串格式的本地时间。

总结

MySQL 中时间到底怎么存储才好？Datetime?Timestamp? 数值保存的时间戳？《高性能 MySQL 》这本神书的作者就是推荐 Timestamp，原因是数值表示时间不够直观。下面是原文：

除了特殊行为之外，通常也应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。有时候人们会将 Unix 时间截存储为整数值，但这不会带来任何收益。用整数保存时间截的格式通常不方便处理，所以我们不推荐这样做。

下面是对 Datetime 、Timestamp 、时间戳类型的一个总结表格：

日期类型	存储空间	日期格式	日期范围	是否存在时区问题
Datetime	8 字节	YYYY-MM-DD HH:MM:SS	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	是
Timestamp	4 字节	YYYY-MM-DD HH:MM:SS	‘1970-01-01 00:00:00’ UTC 到 ‘2038-01-19 03:14:07’	否
时间戳	4 字节	全数字如：1673402012	1970-01-01 00:00:00 之后的时间	否