乐趣区

关于java:关于数据库中如何存储时间的一点思考

jiezi

1. 切记不要用字符串存储日期

我记得我在大学的时候就这样干过，而且当初很多对数据库不太理解的老手也会这样干，可见，这种存储日期的形式的长处还是有的，就是简略直白，容易上手。

然而，这是不正确的做法，次要会有上面两个问题：

  1. 字符串占用的空间更大！
  2. 字符串存储的日期比拟效率比拟低（一一字符进行比对），无奈用日期相干的 API 进行计算和比拟。

2.Datetime 和 Timestamp 之间抉择

Datetime 和 Timestamp 是 MySQL 提供的两种比拟类似的保留工夫的数据类型。他们两者到底该如何抉择呢？

通常咱们都会首选 Timestamp。 上面说一下为什么这样做!

2.1 DateTime 类型没有时区信息的

DateTime 类型是没有时区信息的（时区无关），DateTime 类型保留的工夫都是以后会话所设置的时区对应的工夫。这样就会有什么问题呢？当你的时区更换之后，比方你的服务器更换地址或者更换客户端连贯时区设置的话，就会导致你从数据库中读出的工夫谬误。不要小看这个问题，很多零碎就是因为这个问题闹出了很多笑话。

Timestamp 和时区无关 。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 时区设置的一个罕用 sql 命令 

# 查看以后会话时区
SELECT @@session.time_zone;
# 设置以后会话时区
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
# 数据库全局时区设置
SELECT @@global.time_zone;
# 设置全局时区
SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';

2.2 DateTime 类型消耗空间更大

Timestamp 只须要应用 4 个字节的存储空间，然而 DateTime 须要消耗 8 个字节的存储空间。然而，这样同样造成了一个问题，Timestamp 示意的工夫范畴更小。

  • DateTime：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

Timestamp 在不同版本的 MySQL 中有细微差别。

3 再看 MySQL 日期类型存储空间

下图是 MySQL 5.6 版本中日期类型所占的存储空间：

能够看出 5.6.4 之后的 MySQL 多出了一个须要 0 ～ 3 字节的小数位。Datatime 和 Timestamp 会有几种不同的存储空间占用。

为了不便，本文咱们还是默认 Timestamp 只须要应用 4 个字节的存储空间，然而 DateTime 须要消耗 8 个字节的存储空间。

4. 数值型工夫戳是更好的抉择吗？

很多时候，咱们也会应用 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)`

5. 总结

MySQL 中工夫到底怎么存储才好？Datetime?Timestamp? 数值保留的工夫戳？

如同并没有一个银弹，很多程序员会感觉数值型工夫戳是真的好，效率又高还各种兼容，然而很多人又感觉它体现的不够直观。这里插一嘴，《高性能 MySQL》这本神书的作者就是举荐 Timestamp，起因是数值示意工夫不够直观。上面是原文：

每种形式都有各自的劣势，依据理论场景才是王道。上面再对这三种形式做一个简略的比照，以供大家理论开发中抉择正确的寄存工夫的数据类型：

退出移动版