mysql 超时优化（不指定时区会踩坑）

mysql 超时优化（不指定时区会踩坑）2.1 时区获取2. 问题原因分析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 =

前言

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

1. Demo

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); } } @RestController public class UserController { @Autowired private 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"; } } @Mapper public 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();com.mysql.cj.protocol.a.NativeProtocol

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 case if (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标识GMT if (!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:

2.2 时区设置

那么 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 解决办法详细说明

设置 MySQL Server 的时区

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参数增加 serverTimezone

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() { //先读配置connectionTimeZone String 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 positions fractionalLength = 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