mysql的timestamp存在时区问题怎么解决

34次阅读
没有评论

共计 8674 个字符,预计需要花费 22 分钟才能阅读完成。

本篇内容介绍了“mysql 的 timestamp 存在时区问题怎么解决”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

众所周知,mysql 中有两个时间类型,timestamp 与 datetime,但当在网上搜索 timestamp 与 datetime 区别时,会发现网上有不少与时区有关的完全相反的结论,主要两种:

timestamp 没有时区问题,而 datetime 有时区问题,原因是 timestamp 是以 UTC 格式存储的,而 datetime 存储类似于时间字符串的形式

timestamp 也有时区问题

两种观点让人迷惑,那 timestamp 到底会不会有时区问题呢?

基本概念

时区:

由于地域的限制,人们发明了时区的概念,用来适应人们在时间感受上的差异,比如中国的时区是东 8 区,表示为 +8:00,或 GMT+8,而日本的时区是东 9 区,表示为 +9:00,或 GMT+9,当中国是早上 8 点时,日本是早上 9 点,即东 8 区的 8 点与东 9 区的 9 点,这两个时间是相等的。

另外时间还有如下两个概念:

绝对时间:

如 unix 时间缀,是 1970-01-01 00:00:00 开始到现在的秒数,如:1582416000,这种表示是绝对时间,不受时区影响,也叫纪元时 epoch。

本地时间:

相对于某一时区的时间,是本地时间,比如东 8 区的 2020-02-23 08:00:00,是中国人的本地时间,而在此时,日本人的本地时间是 2020-02-23 09:00:00,所以本地时间都是与某一时区相关的,脱离时区看本地时间,是没有意义的,因为你并不知道这具体是指的什么时间点。

比如在 Java 中,Date 对象是绝对时间,通过 SimpleDateFormat 格式化出来的 yyyy-MM-dd HH:mm:ss 形式的时间字符串,是本地时间,如果 SimpleDateFormat 没有调用 setTimeZone() 显示指定时区,那么默认用的是 jvm 运行在的操作系统上的时区,我们开发机上的时区基本都是 GMT+8。

timestamp 与 datetime 区别

如下,我创建了一张表,里面 time_stamp 是 timestamp 类型,date_time 是 datetime 类型,create_timestamp、create_datetime 是 timestamp 与 datetime 类型,但是它们可以由数据库自动生成。

CREATE TABLE `time_test` (
 `id` bigint unsigned,
 `time_stamp` timestamp,
 `date_time` datetime,
 `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  创建时间 ,
 `create_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  创建时间 ,
 PRIMARY KEY (`id`)
)

1、首先将数据库时区设置为 +8:00,即中国的东 8 区

2、然后如下手动插入一个固定时间的数据,以及用 now() 函数插入当前时间

3、当插入完数据后,然后我们修改当前会话的时区为 +9:00,即日本的东 9 区,然后再次查看数据

4、如上,定义为 timestamp 类型的列 time_stamp、create_timestamp 不管是手动插入的,还是 now() 函数插入的,东 9 区都比东 8 区的时间大 1 个小时,这是正确的,说明 timestamp 类型是时区相关的,然而定义为 datetime 类型的 date_time、create_datetime 字段,时间都没有变化,这说明 datetime 类型是时区无关的。

结论:

timestamp 在存储上是包含时区的,而 datetime 是不包含时区,说明网上的第一种说法是对的。

再看个例子

我们将东 8 区的的 2020-02-23 08:00:00 转换为 unix 时间缀(绝对时间),再插入数据库试试?

如下,使用 linux 的 date 命令转换时间串为 unix 时间缀:

$  date  --date= 2020-02-23 08:00:00 +08:00  +%s
1582416000

然后用 mysql 的 from_unixtime() 函数,将 unix 时间缀转换为 mysql 时间类型来插入数据。

如上,查询出来的时间,也是东 9 区的 9 点,时间也是正确的。

为什么网上又说 timestamp 类型存在时区问题?

我发现网上说 timestamp 有时区问题,都是应用端插入数据,然后到数据库中去看,结果发现时间不一样,因此我打算在 Java 中写个 Demo 试一下,看能不能重现这个问题。

1、首先,下面是 Java 中 Entity 的定义,与上面的 time_test 表对应,注意,这里面时间属性都是用 Date 类型定义的,如下:

2、然后,我写了两个接口 /insert 与 /queryAll 来插入与查询数据,如下:

3、然后我把数据库的时区设置为 +09:00 时区,即日本的东 9 区,如下:

4、然后调用 /insert 接口插入数据,注意我接口传入的时间是东 8 区的 8 点,如下:

5、插入完后,去数据库中查询一把,如下:

可以看到,time_stamp 字段时间是 9 点,且我已将数据库时区设置为东 9 区,东 9 区的 9 点与东 8 区的 8 点,这两个时间实际是相等的,因此时间数据没错。

6、然后我使用 /queryAll 接口将数据查询出来,如下:

mysql 的 timestamp 存在时区问题怎么解决

timeStamp 属性是 1582416000000,这是毫秒级的时间缀,秒级则是 1582416000,对应是东 8 区的 2020-02-23 08:00:00,时间数据也没错!

7、然后我又将 mysql 时区修改回 +8:00,并重启我们的 java 应用,如下:

mysql 的 timestamp 存在时区问题怎么解决

8、再查询一下数据,如下:

mysql 的 timestamp 存在时区问题怎么解决

timeStamp 属性还是 1582416000000,时间没有变化,这也是正确的。

那为什么网上会说 timestamp 存在时区问题?

经过一翻查看,我发现他们都提到了 jdbc 的 serverTimezone,会不会是这个配置错误导致的呢?就先试试吧!

1、如图,我把数据库时区修改回 +9:00 时区,然后故意把 jdbc 的 url 上的 serverTimezone 配置为与数据库不一致的 GMT+ 8 时区,然后重启 java 应用,如下:

mysql 的 timestamp 存在时区问题怎么解决

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8 useUnicode=true characterEncoding=utf8

其中 GMT%2B8 就是 GMT+8,因为在 url 上需要 urlencode,所以就变成了 GMT%2B8。

2、重新插入数据,注意插入的时间还是东 8 区的 8 点,如下:

mysql 的 timestamp 存在时区问题怎么解决

3、然后,我再到数据库中查询一把,如下:

mysql 的 timestamp 存在时区问题怎么解决

time_stamp 中时间竟然是 8 点!要知道我们虽然插入的是东 8 区的 8 点,但当前会话可是东 9 区的,东 8 区的 8 点等于东 9 区的 9 点,所以正确显示应该为 9 点才对,时间差了 1 小时!

4、然后,我又调用 /queryAll 接口查询了一把,想看看 mybatis 查询出来的时间数据对不对,如下:

mysql 的 timestamp 存在时区问题怎么解决

可以看到 timeStamp 是 1582416000000,秒级是 1582416000,这个时间就是东 8 区的 8 点,东 9 区的 9 点啊!查询出来的时间竟然是正确的,为什么???

serverTimezone 的本质

为了找出问题所在,我调试了一下 mysql 的 jdbc 驱动代码,终于弄明白了原因,主要可以看看如下这几点:

1.mysql 驱动创建连接后,会调用 com.mysql.jdbc.ConnectionImpl#configureTimezone() 来配置此连接的时区,如果配置了 serverTimezone,则会使用 serverTimezone 配置的时区,没配置时会去取数据库中的 time_zone 变量,这就是为什么我们没有配置 serverTimezone 变量时,结果也是正确的。

// 若使用普通驱动,使用此方法配置 mysql 连接的时区
com.mysql.jdbc.ConnectionImpl#configureTimezone()
// 若使用 cj 驱动,使用此方法配置 mysql 连接的时区
com.mysql.cj.protocol.a.NativeProtocol#configureTimezone()

2. 调用 jdbc 的 setTimestamp() 方法时,实际调用的是 com.mysql.cj.jdbc.ClientPreparedStatement#setTimestamp(),这里面会根据 serverTimezone 指定的时区,将对应的 Timestamp 对象转换为 serverTimezone 指定时区的本地时间字符串。

3. 执行 sql 语句时,会执行 com.mysql.cj.jdbc.ClientPreparedStatement#execute(),这里面 sendPacket 变量保存着真实会发送到 mysql 的 sql 语句。

注:看的是 8.0.11 版本 mysql-connector-java 驱动源码,不同版本代码会稍有差异,比如 5.2.16 版本驱动,jdbc url 上需要同时配置这两个配置:useTimezone=true serverTimezone=GMT%2B8,且 setTimestamp() 对应的是 com.mysql.jdbc.PreparedStatement#setTimestampInternal 方法。

原理总结如下:

mysql 驱动在发送 sql 前,会将 jdbc 中的 Date 对象参数,根据 serverTimeZone 配置的时区转化为日期字符串后,再发送 sql 请求给 mysql server,同样在 mysql server 返回查询结果后,结果中的日期值也是日期字符串,mysql 驱动会根据 serverTimeZone 配置的时区,将日期字符串转化为 Date 对象。

因此,当 serverTimeZone 与数据库实际时区不一致时,会发生时区转换错误,导致时间偏差,如下:

a、比如 sql 参数是一个 Date 对象,时间值是东 8 区的 2020-02-23 08:00:00,注意它里面存储的可不是 2020-02-23 08:00:00 这个字符串,它是 Date 对象 (绝对时间),只是我用文字表达出来是东 8 区的 2020-02-23 08:00:00。

b、然后,由于 serverTimeZone 配置的是东 8 区,mysql 驱动会将这个 Date 对象转为 2020-02-23 08:00:00,注意这时已经是字符串了,然后再将 sql 发送给 mysql,注意这里的 sql 里面已经将 Date 参数替换为 2020-02-23 08:00:00 了,因为 Date 对象本身是无法走网络的。

c、然后 mysql 数据库接收到这个时间字符串 2020-02-23 08:00:00 后,由于数据库时区配置是东 9 区,它会认为这个时间是东 9 区的,它会以东 9 区解析这个时间字符串,这时数据库保存的时间是东 9 区的 2020-02-23 08:00:00,也就是东 8 区的 2020-02-23 07:00:00,保存的时间就偏差了 1 个小时。

d、查询结果里时间为什么又对了呢,因为查询结果返回了东 9 区的时间字符串,而 java 应用又将其理解为是东 8 区的时间,负负得正了!

将 serverTimezone 与 mysql 时区保持一致

so,那么如果我们将 serverTimezone 配置改正确,即与数据库保持一致时,应该查询到的时间就会是错的,会少 1 个小时。

1、jdbc url 中使用与数据库一样的东 9 区 GMT+9,如下:

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9 useUnicode=true characterEncoding=utf8

其中的 GMT%2B9,即是 GMT+9。

2、然后重启 Java 应用,再查询一把看看,如下:

mysql 的 timestamp 存在时区问题怎么解决

返回的是毫秒级时间缀 1582412400000,秒级就是 1582412400,使用 linux 的 date 命令转换为时间字符串形式:

$  date  --date= @1582412400  + %F %T %z 
2020-02-23 07:00:00 +0800

看到没,它是东 8 区的 7 点,刚好差了 1 个小时。

3、所以,使用 mysql 的 timestamp 类型时,对于 java 应用来说,一定要保证 jdbc url 中的 serverTimezone 与数据库中的时区配置是一致的。

另外一点是,当没有配置 serverTimezone 时,mysql 驱动会自动读取 mysql server 中配置的时区,这里面也有坑!如下:

mysql 驱动自动读取数据库时区的坑

3.1 mysql 安装好后,默认时区是 SYSTEM,而 SYSTEM 指的是 system_time_zone 变量的时区,如下:

mysql 的 timestamp 存在时区问题怎么解决

3.2 当 mysql 驱动读到 time_zone 变量是 SYSTEM 时,会再去读取 system_time_zone 变量,而 system_time_zone 对于国内来说,默认是 CST,这是一个混乱的时区,是 4 个不同时区的缩写,如下:

mysql 的 timestamp 存在时区问题怎么解决

对于 Linux 或 MySQL,会认为 CST 是中国标准时间 (+8:00),但 Java 却认为 CST 是美国标准时间 (-6:00)(注:可能和 Java 运行在 Windows 中有关):

如下,linux 中 CST 等于 +0800,即中国时区:

$  date  + %F %T %Z %z 
2021-09-12 18:35:49 CST +0800

如下,java 中 CST 等于 -06:00,美国时区:

mysql 的 timestamp 存在时区问题怎么解决

3.3 因此 mysql 驱动取到 CST 这个时区值时,它会以为这是 -6:00 时区,但 MySQL 却理解为 +8:00 时区,因此 MySQL 时区一定不要配置为 CST,而要配置为具体的时区,如 +8:00,但如果 MySQL 时区为 CST 且不可修改的情况下,一定要配置 jdbc 的 serverTimezone 为清晰的时区 (如:GMT+8)。

Entity 中日期属性是 String 呢?

1、我们将 Entity 对象中的时间属性改为 String(不推荐),如下:

mysql 的 timestamp 存在时区问题怎么解决

2、然后也写两个接口,/insert2 与 /queryAll2,如下:

mysql 的 timestamp 存在时区问题怎么解决

3、然后插入数据,注意这时我是直接将无时区的 8 点,作为参数给到 sql 的,如下:

mysql 的 timestamp 存在时区问题怎么解决

4、然后再查询一把,如下:

mysql 的 timestamp 存在时区问题怎么解决

如上所示,time_stamp 字段值是 8 点,但此时数据库时区是东 9 区,所以这是东 9 区的 8 点。

5、然后我将数据库与 jdbc 中 serverTimezone 都改为东 8 区呢,改完后重启 Java 应用,如下:

mysql 的 timestamp 存在时区问题怎么解决

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8 useUnicode=true characterEncoding=utf8

6、再次插入数据,参数还是无时区的 8 点,如下:

mysql 的 timestamp 存在时区问题怎么解决

7、再查询一把,如下:

mysql 的 timestamp 存在时区问题怎么解决

如上所示,time_stamp 字段值是 8 点,但现在数据库时间是东 8 区,所以这是东 8 区的 8 点。

8、然后我再将 jdbc url 上的 serverTimezone 调整为东 9 区,然后重启 Java 应用,如下:

url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9 useUnicode=true characterEncoding=utf8

现在 serverTimezone 与数据库中不一致,数据库是东 8 区,serverTimezone 是东 9 区。

9、我们再次插入无时区的 8 点,如下:

mysql 的 timestamp 存在时区问题怎么解决

10、然后再查询一把,如下:

mysql 的 timestamp 存在时区问题怎么解决

time_stamp 字段值还是 8 点,数据库是东 8 区,所以这是东 8 区的 8 点,但我们 serverTimezone 与数据库的时区不一致啊,没看到时间有偏差,为什么?

解释一下

前面说过了,对于 jdbc 中的 Date 对象,在发送给 mysql 前,会先根据 serverTimezone 转换为相应时区的时间字符串,但现在 Entity 中时间属性是 String 类型,mysql 驱动不会进行转换,所以不管 serverTimezone 怎么配置,对 String 类型的时间串都没影响。

这样的话,似乎 java 中日期类型用时间字符串来存还好些,不容易出错,但请再认真考虑一下,调用方传了一个无时区的 8 点,数据库自作主张,就将其认为是东 9 区的 8 点,但如果这个时间字符串实际是东 8 区的 8 点呢?这时如果保存到数据库中为东 9 区的 8 点,那数据就存错了!

那如果目前 api 接口就传的无时区的时间串,Entity 中就定义的 String,怎么解决呢?

1、询问接口定义人员,这个接口的时间串指的是哪个时区的,比如是东 8 区的 2020-02-23 08:00:00。

2、然后接口接收到时间后,要以东 8 区将时间字符串转换为 Date 对象,如下:

SimpleDateFormat sdf = new SimpleDateFormat( yyyy-MM-dd HH:mm:ss 
sdf.setTimeZone(TimeZone.getTimeZone( GMT+8));
Date date = sdf.parse(2020-02-23 08:00:00

3、然后如果 Entity 中时间属性定义的是 String,那么我们要再将 Date 对象以数据库的时区格式化为对应的时间字符串,比如数据库时区是东 9 区,那么格式化后就是 2020-02-23 09:00:00,如下:

SimpleDateFormat sdf = new SimpleDateFormat( yyyy-MM-dd HH:mm:ss 
sdf.setTimeZone(TimeZone.getTimeZone( GMT+9));
String dateStr = sdf.format(date);
entity.setTimeStamp(dateStr);

4、然后将 Entity 保存到 mysql 中的,就也会是东 9 区的 2020-02-23 09:00:00,结果正确。

所以,使用 String 类型来存储时间数据,要想将时间值保存正确,超级麻烦,不建议在实际开发中这种使用。

最佳实践

1、大多数团队会规定 api 中传递时间要用 unix 时间缀,因为如果你传一个 2020-02-23 08:00:00 时间值,它到底是哪个时区的 8 点呢?对于 unix 时间缀,就不会有此问题,因为它是绝对时间。而如果某些特殊原因,一定要使用时间字符串,最好使用 ISO8601 规范那种带时区的时间串,比如:2020-02-23T08:00:00+08:00。

2、Mybatis 中 Entity 定义要与数据库定义一致,数据库中是 timestamp,那么 Entity 中要定义为 Date 对象,因为 mysql 驱动在执行 sql 时,会自动根据 serverTimezone 配置帮你转换为数据库时区的时间串,如果你自己来转换,你极有可能因为忘记调用 setTimeZone() 方法,而使用当前 java 应用所在机器的默认时区,一旦 java 应用所在机器的时区与数据库的时区不一致,就会出现时区问题。

3、jdbc 的 serverTimezone 参数,要配置正确,当不配置时,mysql 驱动会自动读取 mysql server 的时区,此时一定要将 mysql server 的时区指定为清晰的时区 (如:+08:00),切勿使用 CST。

4、如果数据库时区修改后,jdbc 的 serverTimezone 也要跟着修改,并重启 Java 应用,就算没有配置 serverTimezone,也需要重启,因为 mysql 驱动初始化连接时,会将当前数据库时区缓存到一个 java 变量中,不重启 Java 应用它不会变。

数据库中用 timestamp 还是 int 来存储时间?

如果用 int 型时间缀存储,不管数据库时区是啥,都不影响,因为存储的是绝对时间,看起来完美解决了时区问题。

但从某些角度看,这种方案只是把时区问题从数据库端推到应用端去了,时区问题将出现在将时间字符串转换为时间缀的过程中,比如某程序员从 api 接口中拿到时间字符串后,没考虑时区,直接转为 unix 时间缀,就可能出现时区问题。

因此,对于不带时区的时间串解析,一定要问清楚这是哪个时区的时间,并在代码中显式指定!

另外,用 int 存储时间还有如下 3 个不好的点:

开发人员看到这个字段后,无法一目了然的了解到这个时间缀大概是个什么时间,需要去转换一下,会很繁琐。

像 update_time 这样的字段,数据库提供了 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 的机制,这样在更新任何字段时,update_time 会自动更新,而如果使用 int 存储,就需要程序员每次更新表时,重新 set 这个字段,容易遗忘。

由于 int 只有 4 个字节,用它来存储时间,会在 2038 年后溢出,而对于 timestamp 来说,MySQL 将其底层存储统一修改为 8 个字节,相对来说还是比较容易的。

当然,也并不是建议不用 int,这是见仁见智的,不管用 timestamp 还是 int,都没有致命性问题的。

“mysql 的 timestamp 存在时区问题怎么解决”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站,丸趣 TV 小编将为大家输出更多高质量的实用文章!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-15发表,共计8674字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)