写点什么

mysql 的 timestamp 会存在时区问题?,java 技术专家方向

用户头像
极客good
关注
发布于: 刚刚

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



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



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



4、如上,定义为timestamp类型的列time_stampcreate_timestamp不管是手动插入的,还是now()函数插入的,东 9 区都比东 8 区的时间大 1 个小时,这是正确的,说明timestamp类型是时区相关的,然而定义为datetime类型的date_timecreate_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接口将数据查询出来,如下:



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


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



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



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


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




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


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



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


复制代码


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


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



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



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


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



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


serverTimezone 的本质




为了找出问题所在,我调试了一下 my


【一线大厂Java面试题解析+核心总结学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开:qq.cn.hn/FTf 免费领取
复制代码


sql 的 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 应用,再查询一把看看,如下:



返回的是毫秒级时间缀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变量的时区,如下:



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



对于 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,美国时区:



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


Entity 中日期属性是 String 呢?




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



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



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



用户头像

极客good

关注

还未添加个人签名 2021.03.18 加入

还未添加个人简介

评论

发布
暂无评论
mysql的timestamp会存在时区问题?,java技术专家方向