java连接mysql时区_mysql-connector-java 6.x 时区设置

最近遇到了一个问题，java从mysql从读出的时间与本地时间有14个小时的时间差，经查证、测试解决了此问题，在此总结一下：

一、使用mysql-connector-java 6.x版本驱动需要做如下配置：

1、引入pom

mysql

mysql-connector-java

6.0.6

2、设置jdbc连接、驱动jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&serverTimezone=UTC&characterEncoding=utf-8&allowMultiQueries=true

com.mysql.cj.jdbc.Driver

二、设置mysql数据时区

1、查看mysql时区show variables like "%time_zone%"

2、修改mysql时区

(1)方法一set global time_zone = ‘+8:00‘; ##修改mysql全局时区为北京时间，即我们所在的东8区

set time_zone = ‘+8:00‘; ##修改当前会话时区

flush privileges; #立即生效

(2)方法二、通过修改my.cnf配置文件来修改时区# vim /etc/my.cnf ##在[mysqld]区域中加上

default-time_zone = ‘+8:00‘

# /etc/init.d/mysqld restart ##重启mysql使新时区生效

三、分析时区设置问题

通过上面的设置，我们把mysql数据库的时区设置成了中国的时区‘utc+8’，而serverTimezone=UTC设置的是utc时区，两者不同，所以会发现从数据库读出的时间与本地时间差几个小时。要解决这个问题，需要让serverTimezone的设置与数据库的时区保持一致。我们可以选择修改serverTimezone的设置为serverTimezone=Asia/Shanghai，或者修改数据库的时区为‘+0:00’。

四、serverTimezone支持的值说明

1、utc时区：serverTimezone=UTC

2、中国时区：serverTimezone=GMT%2B8(GMT+8这种方式支持全部时区，通过修改最后的数字实现，不过%2B不能写成+)

3、查看官方源码，可以看到serverTimezone支持的相关设置如下，如：中国的时区可设置为：serverTimezone=Asia/Shanghai#Windows Zones

#Mon Sep 28 16:41:59 WEST 2015

AUS\ Central\ Daylight\ Time=Australia/Darwin

AUS\ Central\ Standard\ Time=Australia/Darwin

AUS\ Eastern\ Daylight\ Time=Australia/Sydney

AUS\ Eastern\ Standard\ Time=Australia/Sydney

Afghanistan\ Daylight\ Time=Asia/Kabul

Afghanistan\ Standard\ Time=Asia/Kabul

Alaskan\ Daylight\ Time=America/Anchorage

Alaskan\ Standard\ Time=America/Anchorage

Arab\ Daylight\ Time=Asia/Riyadh

Arab\ Standard\ Time=Asia/Riyadh

Arabian\ Daylight\ Time=Asia/Dubai

Arabian\ Standard\ Time=Asia/Dubai

Arabic\ Daylight\ Time=Asia/Baghdad

Arabic\ Standard\ Time=Asia/Baghdad

Argentina\ Daylight\ Time=America/Buenos_Aires

Argentina\ Standard\ Time=America/Buenos_Aires

Atlantic\ Daylight\ Time=America/Halifax

Atlantic\ Standard\ Time=America/Halifax

Azerbaijan\ Daylight\ Time=Asia/Baku

Azerbaijan\ Standard\ Time=Asia/Baku

Azores\ Daylight\ Time=Atlantic/Azores

Azores\ Standard\ Time=Atlantic/Azores

Bahia\ Daylight\ Time=America/Bahia

Bahia\ Standard\ Time=America/Bahia

Bangladesh\ Daylight\ Time=Asia/Dhaka

Bangladesh\ Standard\ Time=Asia/Dhaka

Belarus\ Daylight\ Time=Europe/Minsk

Belarus\ Standard\ Time=Europe/Minsk

Canada\ Central\ Daylight\ Time=America/Regina

Canada\ Central\ Standard\ Time=America/Regina

Cape\ Verde\ Daylight\ Time=Atlantic/Cape_Verde

Cape\ Verde\ Standard\ Time=Atlantic/Cape_Verde

Caucasus\ Daylight\ Time=Asia/Yerevan

Caucasus\ Standard\ Time=Asia/Yerevan

Cen.\ Australia\ Daylight\ Time=Australia/Adelaide

Cen.\ Australia\ Standard\ Time=Australia/Adelaide

Central\ America\ Daylight\ Time=America/Guatemala

Central\ America\ Standard\ Time=America/Guatemala

Central\ Asia\ Daylight\ Time=Asia/Almaty

Central\ Asia\ Standard\ Time=Asia/Almaty

Central\ Brazilian\ Daylight\ Time=America/Cuiaba

Central\ Brazilian\ Standard\ Time=America/Cuiaba

Central\ Daylight\ Time=America/Chicago

Central\ Daylight\ Time\ (Mexico)=America/Mexico_City

Central\ Europe\ Daylight\ Time=Europe/Budapest

Central\ Europe\ Standard\ Time=Europe/Budapest

Central\ European\ Daylight\ Time=Europe/Warsaw

Central\ European\ Standard\ Time=Europe/Warsaw

Central\ Pacific\ Daylight\ Time=Pacific/Guadalcanal

Central\ Pacific\ Standard\ Time=Pacific/Guadalcanal

Central\ Standard\ Time=America/Chicago

Central\ Standard\ Time\ (Mexico)=America/Mexico_City

China\ Daylight\ Time=Asia/Shanghai

China\ Standard\ Time=Asia/Shanghai

Dateline\ Daylight\ Time=Etc/GMT+12

Dateline\ Standard\ Time=Etc/GMT+12

E.\ Africa\ Daylight\ Time=Africa/Nairobi

E.\ Africa\ Standard\ Time=Africa/Nairobi

E.\ Australia\ Daylight\ Time=Australia/Brisbane

E.\ Australia\ Standard\ Time=Australia/Brisbane

E.\ South\ America\ Daylight\ Time=America/Sao_Paulo</