MySQL 驱动时区和时间处理

字数统计: 1.4k阅读时长: 6 min

 2020/01/24   Share

本文以MySQL8以及mysql-connector-java-8.0.18为例，但是其他本版或者其他数据应该也差不多。

在进行数据库开发的时候，和时间打交道就会涉及到时区，一个日期时间字段从应用层 → 数据库客户端 → 数据库服务端 传递过程中会跟以下几个时区打交道：

JVM 时区(默认取操作系统时区，见后文)
数据库客户端设置的时区参数(serverTimezone)，数据库会话的时区
数据库服务端的时区(默认取操作系统时区，见后文)

要想在时区问题少踩坑(比如存在库里面的时间多了几个小时)可以这样做：

数据库服务端的时区参数time_zone设置一个明确的值，比如+8:00。这个不是必须的，但是建议设置。
数据库客户端通过serverTimezone参数设置自己的时区，这一步至关重要，它应该和java.util.TimeZone.getDefaultRef()的输出一致。

时区

每次创建会话前，先取服务端的时区STZ和客户端时区CTZ(比如serverTimezone=GMT+8)
如果CTZ没有设置就尝试将STZ转换为客户端时区，作为当前会话的时区。这一步可能会出错，因为平台有差异性。

源代码

→ com.mysql.cj.jdbcConnectionImpl.initializePropsFromServer()

→ com.mysql.cj.protocol.a.NativeProtocol.initServerSession()

→ com.mysql.cj.protocol.a.NativeProtocol.configureTimezone()

/**
 * Configures the client's timezone if required.
 * 
 * @throws CJException
 *             if the timezone the server is configured to use can't be
 *             mapped to a Java timezone.
 */
public void configureTimezone() {
    String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");

    if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {
        configuredTimeZoneOnServer = this.serverSession.getServerVariable("system_time_zone");
    }

    String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue();

    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 {
                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...
        //
        if (!canonicalTimezone.equalsIgnoreCase("GMT") && this.serverSession.getServerTimeZone().getID().equals("GMT")) {
            throw ExceptionFactory.createException(WrongArgumentException.class, Messages.getString("Connection.9", new Object[] { canonicalTimezone }),
                    getExceptionInterceptor());
        }
    }

    this.serverSession.setDefaultTimeZone(this.serverSession.getServerTimeZone());
}

日期时间处理（客户端）

时间类型的处理发生在SQL参数绑定阶段，Java 日期时间类型被转换为SQL语句(字符串)，具体处理如下:

数据库驱动会将用户字段类型(如LocalDateTime) 转换为java.sql.Timestamp
将java.sql.Timestamp转换为字符串，绑定到SQL语句的参数上，转换时会进行时区转换:
- 将时间从客户端系统时区(也就是jvm的时区) 转换到当前会话的时区，比假设入参的LocalDateTime值为2019-1-1 13:00:00，当前会话的时区是GMT+8，那么转换的结果是2019-1-1 21:00:00
- 客户端系统时区在jvm启动时从操作系统获取，代码位于java.util.TimeZone.setDefaultZone，但实现是native，所以最终要看jvm的实现代码。
- jvm在处理日期时间的时候默认都是用系统时区来进行时区换算

在服务端应该还会有一个相反的过程，将字符串转换为时间戳，也会有时区转换。

jvm 取时区的细节参考：Java读取系统默认时区

源代码

→ com.mysql.cj.AbstractQueryBindings.setObject(int parameterIndex, Object parameterObj)

→ java.sql.Timestamp.valueOf(LocalDateTime dateTime)，这里生成的Timestamp 包含时区信息，就是jvm的时区。

→ com.mysql.cj.QueryBindings.setTimestamp(int parameterIndex, Timestamp x)

→ com.mysql.cj.ClientPreparedQueryBindings.setTimestamp(int parameterIndex, Timestamp x, Calendar targetCalendar, int fractionalLength)，这里进行了时区转换：jvm时区 → 数据库回话时区。


/**
    * Obtains an instance of {@code Timestamp} from a {@code LocalDateTime}
    * object, with the same year, month, day of month, hours, minutes,
    * seconds and nanos date-time value as the provided {@code LocalDateTime}.
    * <p>
    * The provided {@code LocalDateTime} is interpreted as the local
    * date-time in the local time zone.
    *
    * @param dateTime a {@code LocalDateTime} to convert
    * @return a {@code Timestamp} object
    * @exception NullPointerException if {@code dateTime} is null.
    * @since 1.8
    */
@SuppressWarnings("deprecation")
public static Timestamp valueOf(LocalDateTime dateTime) {
    return new Timestamp(dateTime.getYear() - 1900,
                            dateTime.getMonthValue() - 1,
                            dateTime.getDayOfMonth(),
                            dateTime.getHour(),
                            dateTime.getMinute(),
                            dateTime.getSecond(),
                            dateTime.getNano());
}

@Override
public void setTimestamp(int parameterIndex, Timestamp x, Calendar targetCalendar, int fractionalLength) {
    if (x == null) {
        setNull(parameterIndex);
    } else {

        x = (Timestamp) x.clone();

        if (!this.session.getServerSession().getCapabilities().serverSupportsFracSecs()
                || !this.sendFractionalSeconds.getValue() && fractionalLength == 0) {
            x = TimeUtil.truncateFractionalSeconds(x);
        }

        if (fractionalLength < 0) {
            // default to 6 fractional positions
            fractionalLength = 6;
        }

        x = TimeUtil.adjustTimestampNanosPrecision(x, fractionalLength, !this.session.getServerSession().isServerTruncatesFracSecs());

        this.tsdf = TimeUtil.getSimpleDateFormat(this.tsdf, "''yyyy-MM-dd HH:mm:ss", targetCalendar,
                targetCalendar != null ? null : this.session.getServerSession().getDefaultTimeZone());

        StringBuffer buf = new StringBuffer();
        buf.append(this.tsdf.format(x));
        if (this.session.getServerSession().getCapabilities().serverSupportsFracSecs()) {
            buf.append('.');
            buf.append(TimeUtil.formatNanos(x.getNanos(), 6));
        }
        buf.append('\'');

        setValue(parameterIndex, buf.toString(), MysqlType.TIMESTAMP);
    }
}

例子

假设当前环境的时区信息如下

JDBC 连接的时区参数是:serverTimezone=GMT+8
数据库的时区设置如下

show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |

jvm的时区为UTC

你有一个实体

1
2
3

public class Jclazz implements Serializable {
	private LocalDateTime lockUntil;
}

字段类型是lockUntil赋值为

1 2	// 注意此时jvm的时区为UTC lockUntil = LocalDateTime.of(9999,12,31,23,59,59,0);

MySQL服务端收到的将会是10000-01-01 07:59:59.0，会出错。

原因：数据库回话的时区是GMT+8，而jvm的时区是UTC，因此被加上了8个小时。这个问题其实和数据库的时区设置没有直接关系，只有没有指定serverTimezone时，才会用数据库服务端的时区作为回话时区。

总结：serverTimezone应该设置和客户端系统的时区一致。如果出现了时区换算错误的问题呢，依次检查(前面的优先生效)：

TZ 环境变量
/etc/timezone
/etc/localtime

原文作者：jclazz

原文链接：http://note.eta.pub/2020/01/24/jdbc-time-zone/

发表日期：January 24th 2020, 3:54:43 pm

更新日期：June 29th 2023, 9:12:32 am

Next Post

docker小抄
Previous Post

Spring Security EnableOAuth2Sso注解实现原理

CATALOG

1. 时区
2. 日期时间处理（客户端）

例子



缺失模块。
1、请确保node版本大于6.2
2、在博客根目录（注意不是archer根目录）执行以下命令：
npm i hexo-generator-json-content --save
3、在根目录_config.yml里添加配置：

jsonContent:
  meta: false
  pages: false
  posts:
    title: true
    date: true
    path: true
    text: false
    raw: false
    content: false
    slug: false
    updated: false
    comments: false
    link: false
    permalink: false
    excerpt: false
    categories: true
    tags: true