datetime对应的jdbc mysql_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的时刻，前端显示是正确的。