mysql时区问题（不指定时区会踩坑）

使用的驱动是com.mysql.cj.JDBC.Driver。当应用启动后，首次发起数据库操作，就会创建JDBC的代码，MyBatis把这事情干了，获取连接，从连接池，笔者使用HikariDataSource,HikariPool连接池。

旧项目 MySQL Java 升级驱动，本来一切都好好的，但是升级到 8.x 的驱动后，发现入库的时间比实际时间相差 13 个小时，这就很奇怪了，如果相差 8 小时，那么还可以说是时区不对，从驱动源码分析看看。

pom 依赖，构造一个真实案例，这里的 8.0.22 版本。

<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>Spring-boot-starter-web</artifactId><version>2.5.4</version></dependency><dependency><groupId>org.MyBatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.0</version><exclusions><exclusion><artifactId>slf4j-api</artifactId><groupId>org.slf4j</groupId></exclusion></exclusions></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.22</version><scope>runtime</scope></dependency></dependencies>

随意写一个 da o controller main 。

@SpringBootApplication@MapperScan("com.feng.mysql.rep")public class MySQLDateMain {public static void main(String[] args) {SpringApplication.run(MySQLDateMain.class, args);}} @RestControllerpublic class UserController {@Autowiredprivate UserRepository userRepository;@RequestMapping(value = "/Users/User", method = RequestMethod.POST)public String addUser(){UserEntity userEntity = new UserEntity();userEntity.setAge(12);userEntity.setName("tom");userEntity.setCreateDate(new Date(SYSTEM.currentTimeMillis()));userEntity.setUpdateDate(new Timestamp(System.currentTimeMillis()));userRepository.insertUser(userEntity);return "ok";}} @Mapperpublic interface UserRepository {@Insert("insert into User (name, age, createDate, updateDate) values (#{name}, #{age}, #{createDate}, #{updateDate})")int insertUser(UserEntity userEntity);}

数据库设计：

CREATE TABLE `work`.`User`(`id` int(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,`age` int NULL DEFAULT NULL,`createDate` timestamp NULL DEFAULT NULL,`updateDate` datetime NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 29 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

1.1 验证

调用 HTTP 接口 http://localhost:8080/Users/User

可以看到与真实时间相差 13 小时，诡异了，明明数据库时间是正确的， 而且我的系统时间也是正确的，那么我们可以就只能在驱动找原因，因为当我使用。

2. 问题原因分析

2.1 时区获取

上一步，我们看见系统时间，数据库时间都是正确的，那么做文章的推断就是驱动造成的，以 8.0.22 驱动为例。

使用的驱动是 com.mysql.cj.JDBC.Driver 。

当应用启动后，首次发起数据库操作，就会创建 JDBC 的代码，MyBatis 把这事情干了，获取连接，从连接池，笔者使用 HikariDataSource,HikariPool连接池。

在 com.mysql.cj.jdbc.ConnectionImpl 里面会初始化 session 的拦截器，属性Variables、列映射、自动提交信息等等，其中有一行代码初始化时区：

this.session.getProtocol().initServersession();

public void configureTimezone() {//获取MySQL server端的时区String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");//如果是SYSTEM，则获取系统时区if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {configuredTimeZoneOnServer = this.serverSession.getServerVariable("system_time_zone");}//配置文件获取时区serverTimezone配置，即可以手动配置，这是一个解决问题的手段String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue();//未指定时区，且读取到MySQL时区，就if (configuredTimeZoneOnServer != null) {// user can override this with driver properties, so don't detect if that's the caseif (canonicalTimezone == null || StringUtils.isEmptyOrWhitespaceOnly(canonicalTimezone)) {try {//规范时区？难道直接读取的不规范:sweat_smile:，这步很重要canonicalTimezone = TimeUtil.getCanonicalTimezone(configuredTimeZoneOnServer, getExceptionInterceptor());} catch (IllegalArgumentexception iae) {throw ExceptionFactory.createException(WrongArgumentException.class, iae.getMessage(), getExceptionInterceptor());}}}if (canonicalTimezone != null && canonicalTimezone.length() > 0) {//设置时区，时间错位的源头this.serverSession.setServerTimeZone(TimeZone.getTimeZone(canonicalTimezone));// The Calendar class has the behavior of mapping unknown timezones to 'GMT' instead of throwing an exception, so we must check for this...//时区不规范，比如不是GMT，然而ID标识GMTif (!canonicalTimezone.equalsIgnoreCase("GMT") && this.serverSession.getServerTimeZone().getID().equals("GMT")) {throw ExceptionFactory.createException(WrongArgumentException.class, Messages.getString("Connection.9", new Object[] {canonicalTimezone}),getExceptionInterceptor());}}}

规范时区：

/*** Returns the 'official' Java timezone name for the given timezone* * @param timezoneStr*the 'common' timezone name* @param exceptionInterceptor*exception interceptor* * @return the Java timezone name for the given timezone*/public static String getCanonicalTimezone(String timezoneStr, ExceptionInterceptor exceptionInterceptor) {if (timezoneStr == null) {return null;}timezoneStr = timezoneStr.trim();// handle ' /-hh:mm' form ...//顾名思义if (timezoneStr.length() > 2) {if ((timezoneStr.charAt(0) == ' ' || timezoneStr.charAt(0) == '-') && Character.isDigit(timezoneStr.charAt(1))) { return "GMT"timezoneStr;}}synchronized(TimeUtil.class) {if (timeZoneMappings == null) { loadTimeZoneMappings(exceptionInterceptor);}}String canonicalTz;//时区缓存去找关键字if ((canonicalTz = timeZoneMappings.getProperty(timezoneStr)) != null) {return canonicalTz;}throw ExceptionFactory.createException(InvalidConnectionAttributeException.class,Messages.getString("TimeUtil.UnrecognizedTimezoneId", new Object[] { timezoneStr}), exceptionInterceptor);}

比如我的数据库时区是CST，拿到了：

这是系统时区，拿到的是 CST，根源是读取了内置的时区值：

然而这个文件没有 CST 时区定义，需要去 JDK 去拿，然后缓存。这就说明一个道理，CST 这个时区定义不明确。

时区就是 CST 了，仅仅是 CST 时区而已，这里并不能说明 CST 有什么问题， 真正的问题是 CST 怎么比东八区少 13 个小时呢？

this.serverSession.setServerTimeZone(TimeZone.getTimeZone(canonicalTimezone));

根源就是这几句代码：

public static TimeZone getTimeZone(String var0) {return ZoneInfoFile.getZoneInfo(var0);}

开始初始化，sun.timezone.ids.oldmapping 这个一般不会设置。

读取 $JAVA_HOME/lib/tzdb.dat，这是一个 JDK 时区存储文件。

其中 PRC 就是中国时区，但是这个文件并未定义 CST。

CST在这里定义的： addOldMapping();

private static void addOldMapping() {String[][] var0 = oldMappings;int var1 = var0.length;for (int var2 = 0; var2 < var1;var2) {String[] var3 = var0[var2];//这里就把CST时区设置为芝加哥时区aliases.put(var3[0], var3[1]);}if (USE_OLDMAPPING) {aliases.put("EST", "America/New_York");aliases.put("MST", "America/Denver");aliases.put("HST", "Pacific/Honolulu");} else {zones.put("EST", new ZoneInfo("EST", -18000000));zones.put("MST", new ZoneInfo("MST", -25200000));zones.put("HST", new ZoneInfo("HST", -36000000));}}

oldMappings 是啥呢？

private static String[][] oldMappings = new String[][] {{"ACT","Australia/Darwin"}, {"AET","Australia/Sydney"}, {"AGT","America/Argentina/Buenos_Aires"}, {"ART","Africa/Cairo"}, {"AST","America/Anchorage"}, {"BET","America/Sao_Paulo"}, {"BST","Asia/Dhaka"}, {"CAT","Africa/Harare"}, {"CNT","America/St_Johns"}, {"CST","America/Chicago"}, {"CTT","Asia/Shanghai"}, {"EAT","Africa/Addis_Ababa"}, {"ECT","Europe/Paris"}, {"IET","America/Indiana/Indianapolis"}, {"IST","Asia/Kolkata"}, {"JST","Asia/Tokyo"}, {"MIT","Pacific/Apia"}, {"NET","Asia/Yerevan"}, {"NST","Pacific/Auckland"}, {"PLT","Asia/Karachi"}, {"PNT","America/Phoenix"}, {"PRT","America/Puerto_Rico"}, {"PST","America/Los_Angeles"}, {"SST","Pacific/Guadalcanal"}, {"VST","Asia/Ho_Chi_Minh"}};

{"CST", "America/Chicago"} :sob:

private static ZoneInfo getZoneInfo0(String var0) {try {//缓存获取ZoneInfo var1 = (ZoneInfo) zones.get(var0);if (var1 != null) {return var1;} else {String var2 = var0;if (aliases.containsKey(var0)) {var2 = (String) aliases.get(var0);}int var3 = Arrays.binarySearch(regions, var2);if (var3 < 0) {return null;} else {byte[] var4 = ruleArray[indices[var3]];DataInputStream var5 = new DataInputStream(new ByteArrayInputStream(var4));var1 = getZoneInfo(var5, var2);//首次获取，存缓存zones.put(var0, var1);return var1;}}} catch (Exception var6) {throw new RuntimeException("Invalid binary time-zone data: TZDB:"var0", version: "versionId, var6);}}

就这样 CST 时区就被 JDK 认为是 美国芝加哥的时区 了，:confounded:

那么 JDBC 在哪里设置时间的呢?

进一步可以看到在服务器上时区都是 OK 的。

但是，在 com.mysql.cj.ClientPreparedQueryBindings 的 setTimestamp 方法中，获取了 session 时区，然后 format，:sweat_smile:

时间从此丢失 13 小时，原因是 format 的锅，因为 用的美国芝加哥时间格式化 ，如果使用 long 时间的话或者什么都不处理就没有问题。

SimpleDateFormat 设置 CST 时区，前面已经分析了，这个时区就是美国芝加哥时区。

JDK 会认为 CST 是美国芝加哥的时区，UTC-5，但是我们的时间是 UTC 8，换算成 US的时间就是，当前时间 - 8 - 5，即时间少 13 小时。这里不设置时区（即使用客户端时区）即可正常返回时间。

那么 CST 时区是什么呢？笔者写博客的时间 是2021-09-22，是 CST 的夏令时 。

CST 是中部标准时间，现在是 UTC-5，即夏令时，冬季还会变成 UTC-6。

标准的 US 的 CST 时间是 UTC-6，我当前的时间是 23:56。

关键在于 CST 定义非常模糊，而 MySQL 驱动调用 SimpleDateFormat，使用的 CST 为美国芝加哥时区，当前的季节为 UTC-5。

3. 解决办法

根据上面的分析，解决 CST 时区的方法非常多。

• 设置 MySQL Server 的时区为非 CST 时区；
• 设置 MySQL 的系统时区为非 CST 时区；
• 通过参数增加 serverTimezone设 置为明确的 MySQL 驱动的 properties 定义的时区；
• 修改 MySQL Java 驱动，获取时区通过客户端获取，比如当前运行环境，通过 JDK 获取。

3.1 解决办法详细说明

set global time_zone = ' 08:00';

或者修改 MySQL 的配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf。

[mysqld] 节点下增加：

default-time-zone = ' 08:00'

以 Ubuntu 为例：

timedatectl set-timezone Asia/Shanghai

jdbc:mysql://localhost:3306/work?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=Asia/Shanghai

修改MySQL驱动

比如获取时区通过 client 端获取，Date 数据使用什么时区，就使用这个时区 format，但是一般而言我们不会自己发布驱动，跟随 MySQL 官方更新，只有大厂有机会自己运营 MySQL 驱动。

3.2 官方解决方案

笔者在浏览 MySQL 8.0.x 驱动发布的时候在 8.0.23 版本发现了特别的发布记录，笔者在初始时使用 8.0.22 版本是有深意的，:smile:

MySQL :: MySQL Connector/J 8.0 Release Notes :: Changes in MySQL Connector/J 8.0.23 (2021-01-18, General Availability)

看来官方修复了。:smile:

来源码看看。果然，不配置就客户端获取时区了TimeZone.getDefault();

public void configureTimeZone() {//先读配置connectionTimeZoneString connectionTimeZone = getPropertySet().getStringProperty(PropertyKey.connectionTimeZone).getValue();TimeZone selectedTz = null;//如果没配参数，或者参数配LOCAL，就取客户端时区//配置其他选择，基本上参数决定了时区，不再MySQL server去获取时区了if (connectionTimeZone == null || StringUtils.isEmptyOrWhitespaceOnly(connectionTimeZone) || "LOCAL".equals(connectionTimeZone)) {selectedTz = TimeZone.getDefault();} else if ("SERVER".equals(connectionTimeZone)) {// Session time zone will be detected after the first ServerSession.getSessionTimeZone() call.return;} else {selectedTz = TimeZone.getTimeZone(ZoneId.of(connectionTimeZone)); // TODO use ZoneId.of(String zoneId, Map<String, String> aliasMap) for custom abbreviations support}//设置时区this.serverSession.setSessionTimeZone(selectedTz);//默认不再强制把时区塞进session 的 Variables中if (getPropertySet().getBooleanProperty(PropertyKey.forceConnectionTimeZoneToSession).getValue()) {// TODO don't send 'SET SESSION time_zone' if time_zone is already equal to the selectedTz (but it requires time zone detection)StringBuilder query = new StringBuilder("SET SESSION time_zone='");ZoneId zid = selectedTz.toZoneId().normalized();if (zid instanceof ZoneOffset) {String offsetStr = ((ZoneOffset) zid).getId().replace("Z", " 00:00");query.append(offsetStr);this.serverSession.getServerVariables().put("time_zone", offsetStr);} else {query.append(selectedTz.getID());this.serverSession.getServerVariables().put("time_zone", selectedTz.getID());}query.append("'");sendCommand(this.commandBuilder.buildComQuery(null, query.toString()), false, 0);}}

再看看设置参数的地方，这里设计有点改变，通过 QueryBindings 接口抽象了处理逻辑：

public void setTimestamp(int parameterIndex, Timestamp x) throws java.sql.SQLException {synchronized(checkClosed().getConnectionMutex()) {((PreparedQuery << ? > ) this.query).getQueryBindings().setTimestamp(getCoreParameterIndex(parameterIndex), x, MysqlType.TIMESTAMP);}}

实现 com.mysql.cj.ClientPreparedQueryBindings：

public void bindTimestamp(int parameterIndex, Timestamp x, Calendar targetCalendar, int fractionalLength, MysqlType targetMysqlType) {if (fractionalLength < 0) {// default to 6 fractional positionsfractionalLength = 6;}x = TimeUtil.adjustNanosPrecision(x, fractionalLength, !this.session.getServerSession().isServerTruncatesFracSecs());StringBuffer buf = new StringBuffer();if (targetCalendar != null) {buf.append(TimeUtil.getSimpleDateFormat("''yyyy-MM-dd HH:mm:ss", targetCalendar).format(x));} else {this.tsdf = TimeUtil.getSimpleDateFormat(this.tsdf, "''yyyy-MM-dd HH:mm:ss", targetMysqlType == MysqlType.TIMESTAMP && this.preserveInstants.getValue() ? this.session.getServerSession().getSessionTimeZone() : this.session.getServerSession().getDefaultTimeZone());buf.append(this.tsdf.format(x));}if (this.session.getServerSession().getCapabilities().serverSupportsFracSecs() && x.getNanos() > 0) {buf.append('.');buf.append(TimeUtil.formatNanos(x.getNanos(), 6));}buf.append('\'');setValue(parameterIndex, buf.toString(), targetMysqlType);}

时区就是刚刚设置的，亚洲/上海。

一个时区问题，居然里面有这么多玩头，MySQL 居然在 8.0.23 才修复这个，难道 MySQL 认为大家都会配置时区，还是服务器都不使用 CST 时区。 另外如果使用 UTC 时区，是一个精准的时区，表示 0 区时间，就会从一个坑跳另一个坑。 所以， 还是精准用 Asia/Shanghai 吧，或者驱动升级 8.0.23 及 以上版本，不配置时区。

原文链接： blog.csdn.net/fenglllle/article/details/120423274