MySQL中Timestamp和DateTime在JDBC和shell中的表现差异
各种博客说的很多,然后并没有讲的清楚的。先不论DateTime占8字节,Timestamp占4字节,表示范围不同。包括不同之处在MySQL server指定不同的timezone时,Timestamp返回不同的值(String 表示),DateTime不变。这些都对,然而项目中遇到的问题依然让人困惑。
有几点需要先说明:MySQL表中的数据是通过SQL来插入的,而SQL通过字符串的形式嵌入时间类型的字面量,string表示的Timestamp在存储的时候需要结合一个TimeZone才能确定一个全球时刻,这个TimeZone就是connection指定的TimeZone或者是系统的TimeZone,后续查询的时候都是根据这个认定的时刻根据连接的TimeZone进行转换。
SQL返回的结果是以字符串为准的。JDBC也是通过字符串再进行转换的,不管是DateTime还是Timestamp类型都是转换为java.sql.Timestamp,这里又是一个string到对象的过程。
以下实验:
设置连接的timezone:
MariaDB [test]> set time_zone='+6:00';
Query OK, 0 rows affected (0.009 sec)
birth字段是DateTime,birthts是Timestamp类型。SQL写入的字符串的不同效果:
MariaDB [test]> update LEOUSER set birth='2012-12-12 10:10:10', birthts='2012-12-12 10:10:10';
MariaDB [test]> select * from LEOUSER;
+----+-------------+---------------------+---------------------+----------+------------+
| id | name | birth | birthts | leotime | leodate |
+----+-------------+---------------------+---------------------+----------+------------+
| 1 | leo| 2012-12-12 10:10:10 | 2012-12-12 10:10:10 | 13:23:45 | 2019-01-03 |
在本连接中查看都是一样的效果。切换到GMT+8的timezone的连接查看得到
MariaDB [test]> select * from LEOUSER;
+----+-------------+---------------------+---------------------+----------+------------+
| id | name | birth | birthts | leotime | leodate |
+----+-------------+---------------------+---------------------+----------+------------+
| 1 | leo| 2012-12-12 10:10:10 | 2012-12-12 12:10:10 | 13:23:45 | 2019-01-03 |
结论:datetime的效果和存储string字面量是一样的;Timestamp的存储的效果是二元组(string,连接timezone)联合确定的时刻。
上例中,12日10点GMT+6 = 12日12点GMT+8 = 12日4点GMT
再看JDBC情况:
连接时指定jdbc:mysql://server:port/dbname?useLegacyDatetimeCode=false&serverTimezone=GMT%2B8
执行“update LEOUSER set birth = ?, birthts = ? where id=1;”传递同一个java.util.Date对象。查看到mysql已经修改。然后JDBC执行select,发现datetime和Timestamp字段的值都是一样的。
修改url中timezone为GMT+3,发现datetime和Timestamp返回的对象值还是相等的,都与前一步不等
前面说好的Datetime和Timestamp的差异呢?为什么始终是相等的Timestamp对象?
到mysqlsrver上执行shell看到的结果是相等,set timezone=‘+3:00’之后,再次select果然不一样了。为什么?
我之前以为mysql Timestamp类型是特定时刻,而Datetime是一个字符串表示。然而这些显然都是不对的理解。
由于之前既定的理解是错误的,严重影响对现象的思考。经过无数次实验,对JDBC mysql驱动debug,终于明白:
=============================重点如下==================================
jdbc的读写都是转换为字面的SQL字符串,包括parameter。一个完整的语句发送过去。MySQL返回的string始终不随JDBC Connection的Timezone变化,JDBC转化为java.sql.Timestamp对象时结合Connection的Timezone转化string。写入是一个逆过程,Timestamp对象结合Connection的timezone转化为SQLstring,发送到MySQL,不关心server端如何执行和存储,只知道下次JDBC读取时仍然是此时转化好的SQLstring。
====
JDBC读取和写入分两步:
1. Timetamp对象转SQLstring,这个是结合Connection,SQLstring发送到server执行
2. 读取返回SQLString结果(不随Connection的tz改变而改变,永远和1中发送的SQLstring保持一致),结合Connection的tz,联合构造Timestamp对象。
所以只要读取和写入的Connection的tz相同,JDBC可以保证结果一致,不管MySQL是Datetime还是Timestamp。
JDBC driver不指定timezone时,使用system的timezone。
====
mysql shell中读取和写入都是SQLstring层次。
对于JDBC执行SQLstring的:insert into ‘yyyyMMdd13:00:00’,mysqlserver的timezone设置了GMT+3,这个对于JDBC是无法感知的。
shell执行select,获取到的SQLstring结果就是‘yyyyMMdd13:00:00’,不论是DateTime还是Timestamp。但是如果mysql指定当前session的timezone为GMT+8后执行select,获取到Timestamp的SQLstring结果是‘yyyyMMdd18:00:00’,DateTime还是‘yyyyMMdd13:00:00’。这是MySQL自己不同类型的特性。
但是改变session或者mysqlserver的timezone不是一个明智的举动。应该保持服务端的timezone明确而固定(例如迁移到不同server时依然指定迁移前的timezone),session临时改变timezone用于测试。
====
所以JDBC Connection的timezone和mysqlsession的timezone不是相同的东西。JDBC不感知MySQL的存储类型是DateTime还是Timestamp
=================
这解释了工作中本地的Tomcat保存updatetime到qa的mysql,通过QA环境的tomcat的web上看到的时间是未来8小时的。
项目中没有明确指定JDBC Connection的timezone,使用了系统的timezone,server是GMT,本地死GMT+8。假设现在14点,本地Tomcat存到MySQL首先要转换为String,“14”点,存到表相应列(DateTime类型)。在QA server上查询到的也是String “14”,JDBC驱动程序得到字符串后结合本机系统Timezone,parse 字符串得到14点-GMT这一时刻的Timestamp对象,这是一个未来+8hour的时刻,前端显示是正确的。