MySQL-时区问题

发表于 | 分类于 ， |
| 字数统计: 2,323

问题描述

在使用MySQL数据库做Java项目开发时，会需要插入数据到数据库，也会需要通过接口返回数据库中的数据，这其中就可能会碰到Java程序中的时间与保存到数据库中的时间不一致的问题。

涉及的对象

时区问题的产生，会受到系统中的三个对象影响，第一个MySQL数据库，第二个连接数据库的JDBC，第三个是Java程序中的序列化模块。下面对这三者进行分析。

MySQL

查询时区

MySQL数据库是有时区的，可以通过如下语句查询：

1
SHOW VARIABLES LIKE "%time_zone%";

得到的结果类似如下：

system_time_zone是指运行 MySQL 服务的服务器的时区，要改变该参数值，通常做法是用环境变量 TZ 来指定。
time_zone是 MySQL 处理需要用到时区信息的数据时所使用的时区，如果不显式指定，默认使用 system_time_zone。

上图中，system_time_zone的值是CST，这其实是一个比较混乱的时区，它包含了四个不同时区：

  • Central Standard Time (USA) UT-6:00 美国标准时间
  • Central Standard Time (Australia) UT+9:30 澳大利亚标准时间
  • China Standard Time UT+8:00 中国标准时间
  • Cuba Standard Time UT-4:00 古巴标准时间

经测试，本文示例中使用MySQL时区实际是中国标准时间这个时区。测试方式可以通过执行如下语句确定。

1
SELECT NOW();

设置时区

MySQL可以通过如下几种方式设置时区（time_zone）。
第一种是sql客户端执行命令：

1
mysql> SET GLOBAL time_zone = '${timezone}';

或者

1
mysql> SET time_zone = '${timezone}';

区别是前者是全局设置，针对所有连接，后者只针对当前连接的客户端生效。可以通过执行mysql> flush privileges;使命令立即生效。
这种命令行的设置当 MySQL 服务重启之后就会失效。

第二种方式是在启动MySQL服务时增加参数--default-time-zone='${timezone}'。

第三种是修改MySQL的配置文件，在my.cnf中的 [mysqld]配置项下面增加 default-time-zone='${timezone}'。注意：一定要在 [mysqld] 之下加 ，否则会出现类似于unknown variable 'default-time-zone=+8:00'这种报错。

timezone可以设置为那些值呢？

  1. 可以设置为'SYSTEM' 表示使用服务器的时区。
  2. 可以使用 UTC 偏移量，如'+10:00'， '-6:00'等。
  3. 可以使用时区名，如'Europe/Helsinki', 'US/Eastern', 'MET'等，注意只有在 MySQL 中有时区信息表并且有相应信息数据的时候才能使用时区名（在/usr/share/zoneinfo目录下）。

插入数据

MySQL中存储时间可以使用TIMESTAMP类型或者DATETIME类型，当插入数据保存的时候会有不同。先看一个示例，我们创建一个测试表：

1
2
3
4
5
6
CREATE TABLE `t_test_time` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`create_timestamp` TIMESTAMP NULL DEFAULT NULL,
	`create_datetime` DATETIME NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE
)

执行如下语句插入一条数据：

1
INSERT INTO t_test_time(id, create_timestamp, create_datetime) VALUES (1, '2021-01-01 14:32:12', '2021-01-01 14:32:12');

可以通过客户端工具查看这条数据。

这看起来似乎没什么异样。前面提到了我们使用的数据库实际上使用的是'+08:00'这个timezone，那么我们修改一下timezone会有什么现象呢？执行set time_zone = '+07:00';后可以再次查看，发现 create_timestamp 这个字段存储的值变了。

是什么原因导致的？实质上是TIMESTAMP类型数据保存的是一个时间戳（时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总豪秒数），保存的时候会根据数据库的timezone进行相应计算（这个例子中减8个小时的毫秒数），查询的时候也会会根据timezone计算（这个例子中是加上了7个小时的毫秒数），而DATETIME类型的数据则是插入的时候是什么值就原样存储，不做任何转换，查询的时候也是原样返回不做任何处理，所以就有了上面的结果。

所以我们可以知道TIMESTAMP类型的数据存储的是一个绝对值，查询时会转成 MySQL 的 timezone 对应的值，当 timezone 改变之后它仍能得到一个正确的结果，而DATETIME类型的数据则会在MySQL时区变化后不能正确地表示对应的时间了。

JDBC

接下来我们看看JDBC对时区有什么影响。

MyBatis

首先本文演示项目中是用到了MyBatis这个OOM框架，并且使用 xml 配置文件的方式执行 SQL，在 xml 配置文件中通常会需要配置 ResultMap，也就是数据库的列对实体类的属性做映射。一般情况下，我们会使用java.util.Date作为实体类的日期类型，在 JDBC 中时间的类型有三种jdbcType，分别是 DATE、TIME 和 TIMESTAMP，jdbcType="DATE"查询得到的数据格式是yyyy-MM-dd（只有日期），jdbcType="TIME"查询得到的数据格式是HH:mm:ss（只有时分秒、毫秒），只有设置了jdbcType="TIMESTAMP"才能获得完整的时间描述，也就是既包含日期又包含时间。所以一般实体类的属性是java.util.Date类型的话，在 MyBatis 的配置文件中会设置jdbcType="TIMESTAMP"。注：对于java.util.Date类型的字段，如果配置文件中不指定jdbcType，MyBatis的默认也是TIMESTAMP。

因此，基于本文中的演示示例可以简单写一个查询、插入功能。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<resultMap id="BaseResultMap" type="com.lzumetal.springboot.demodatabase.entity.TestTime">
  <result column="id" jdbcType="INTEGER" property="id" />
  <result column="create_timestamp" jdbcType="TIMESTAMP" property="createTimestamp" />
  <result column="create_datetime" jdbcType="TIMESTAMP" property="createDateTime" />
</resultMap>

<select id="getById" resultMap="BaseResultMap">
  select id, create_timestamp, create_datetime
  from t_test_time
  WHERE id = #{id}
</select>

<insert id="insert" parameterType="com.lzumetal.springboot.demodatabase.entity.TestTime">
  insert into t_test_time (create_timestamp,  create_datetime)
  values (#{createTimestamp,jdbcType=TIMESTAMP}, #{createDateTime,jdbcType=TIMESTAMP})
</insert>

数据库配置

在jdbcURL中我们可以指定serverTimezone参数，这里暂时设置为GMT+0看看会有什么结果。

1
2
3
4
spring.datasource.url=jdbc:mysql://192.168.0.100:3306/test?characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

MySQL 的jar包中关于serverTimezone配置的源码位于com.mysql.cj.protocol.a.NativeProtocol的configureTimezone方法中。

测试代码

其他相关的代码如下：

dao

1
2
3
4
5
6
7
@Mapper
public interface TestTimeMapper {

    TestTime getById(@Param("id") Integer id);

    void insert(TestTime entity);
}

service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Slf4j
@Service
public class TestTimeService {

    @Autowired
    private TestTimeMapper testTimeMapper;

    public TestTime getById(Integer id) {
        return testTimeMapper.getById(id);
    }

    public void addOne() {
        Date now = new Date();
        TestTime entity = new TestTime();
        entity.setCreateTimestamp(now);
        entity.setCreateDateTime(now);
        log.info("插入数据|{}", entity);
        testTimeMapper.insert(entity);
    }
}

controller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@Slf4j
@RestController
@RequestMapping("/time")
public class TimeController {

    @Autowired
    private TestTimeService testTimeService;


    @RequestMapping("/getById")
    public ResponseData getById(Integer id) {
        TestTime testTime = testTimeService.getById(id);
        log.info("根据id查询|{}", testTime);
        return ResponseData.data(testTime);
    }


    @RequestMapping("/addOne")
    public ResponseData addOne() {
        testTimeService.addOne();
        return ResponseData.success();
    }
}

测试

使用 postman 测试查询接口，可以看到打印的日志：

1
c.l.s.d.controller.TimeController        : 根据id查询|TestTime(id=1, createTimestamp=Fri Jan 01 22:32:12 CST 2021, createDateTime=Fri Jan 01 22:32:12 CST 2021)

通过jdbc从数据库中查出的时间多了8个小时，但postman中接口返回数据又是正常的。

这种现象肯定是不正常的，接下来我们再调用新增接口看看，日志打印：

1
c.l.s.d.service.TestTimeService          : 插入数据|TestTime(id=null, createTimestamp=Wed Jan 26 11:08:00 CST 2022, createDateTime=Wed Jan 26 11:08:00 CST 2022)

查看数据库：

数据库保存的时间少了8个小时。

原因分析：//todo 待分析
解决办法：前面我们已经分析了本文使用的数据实际的 timezone 参数应该是 GMT+08:00，所以在项目的jdbcURL配置项中也要配置serverTimezone=GMT+8，配置之后可以看到打印的日志和数据库中数据可以匹配上了。

1
spring.datasource.url=jdbc:mysql://192.168.0.100:3306/test?characterEncoding=UTF-8&serverTimezone=GMT%2B8

Jackson

上面修改了jdbcURL的serverTimezone=GMT+8后，会看到又出现了另外一个问题，用postman调用接口返回的时间不是数据库中查到的时间，而是少了8个小时。

这是因为SpringBoot对响应数据默认使用Jackson进行序列化，在@JsonFormat这个注解中也有一个时区属性，默认是使用UTC，也就是0时区的时间，所以就减去了8个小时。

1
2
3
4
5
6
7
8
9
/**
 * Value that indicates that default {@link java.util.TimeZone}
 * (from deserialization or serialization context) should be used:
 * annotation does not define value to use.
 *<p>
 * NOTE: default here does NOT mean JVM defaults but Jackson databindings
 * default, usually UTC, but may be changed on <code>ObjectMapper</code>.
 */
public final static String DEFAULT_TIMEZONE = "##default";

解决办法：

  1. 日期类型的字段上加@JsonFormat注解，并且属性值设置为timezone="GMT+8"。

    1
    2
    3
    4
    5
    @JsonFormat(timezone = "GMT+8")
    private Date createTimestamp;

    @JsonFormat(timezone = "GMT+8")
    private Date createDateTime;

  2. 配置一个bean。

    1
    2
    3
    4
    5
    @Bean
    public Jackson2ObjectMapperBuilderCustomizer jacksonObjectMapperCustomization() {
        return jacksonObjectMapperBuilder ->
                jacksonObjectMapperBuilder.timeZone(TimeZone.getTimeZone("GMT+8"));
    }

  3. springboot 配置的方式。

    1
    spring.jackson.time-zone=GMT+8

总结

  1. 数据库设计时，最好统一使用TIMESTAMP类型。
  2. jdbcURL中的serverTimezone要和数据库保持一致。
------ 本文完 ------