MySQL时间戳精度

谁践踏了优雅 2022-10-11 01:17 189阅读 0赞

## 现象 ##

应用MySQL Connector/J（mysql-connector-java）升级，由5.1.20升级到5.1.30。

灰度发布后，发现查询功能异常，**按照时间查询不到之前创建过的订单了**。

private Date searchDate;

对应查询SQL如下所示：其中MySQL中`order_tbl`表中的create\_timestamp字段为TimeStamp类型。

select * from order_tbl where create_timestamp = #{searchDate}

## 排查过程 ##

### 一、打印SQL参数 ###

打印下SQL中的searchDate查询参数，判断异常机器和正常机器是否一致。  
缩小排查范围。如果不一致，大概率是应用层哪块处理不一致导致的问题；反之基本可以确认是数据库驱动或者数据库层面的问题（因为本次也升级了MySQL Connector/J的版本）。

项目使用的是Mybaits ORM框架，这里直接是打开了Mybaits输出sql语句的配置。  
发现灰度异常机器和正常机器输出的SQL参数 searchDate 是一致的，如：`2020-01-29 09:21:31.031`，都是带有小数位的。  
结合应用变更，基本可以断定是由Connector/J版本升级导致的。

### 二、MySQL Connector/J Release Notes ###

查下对应版本的Release Notes。

发现在[5-1-23版本Release Notes][5-1-23_Release Notes]里有一项BugFix

> The nativeSQL() method would always truncate fractional seconds rather than preserving the fractional part in the output string. Now Connector/J checks the server version: it preserves the fractional part for MySQL 5.6.4 and greater, and truncates the fractional part for older versions. (Bug \#14748459, Bug \#60598)

大意是：修改了nativeSQL方法，低版本Connector/J发给数据库的时间是秒级别的，小数秒位会被丢弃。

本次修改，如果MySQL server版本大于等于5.6.4，将会保留这个小数秒。（这也是查询异常的原因，DB里面的历史数据是不带小数秒的，查询的时候是以小数秒查询，必然是查询不出来的）

对应的代码变更如下：  
[https://github.com/mysql/mysql-connector-j/compare/5.1.22…5.1.23][https_github.com_mysql_mysql-connector-j_compare_5.1.22_5.1.23]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0X2JldHRlcg_size_16_color_FFFFFF_t_70]

为什么是5.6.4版本？

> MySQL 5.6 has fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision:

[https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html][https_dev.mysql.com_doc_refman_5.6_en_fractional-seconds.html]

## 解决方案 ##

方案1：设置create\_timestamp字段小数精度优点：彻底解决问题；缺点：影响面比较大，数据整体都会受影响。

方案2：业务上看是否能去除类似的查询，尤其是时间类型的字段，受版本影响比较大，不建议作为严格匹配的条件。

方案3：查询前对searchDate进行强制转化，去除掉小数位。存在代码改动，并且改动不彻底。不建议。

[5-1-23_Release Notes]: https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-23.html
[https_github.com_mysql_mysql-connector-j_compare_5.1.22_5.1.23]: https://github.com/mysql/mysql-connector-j/compare/5.1.22...5.1.23
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d0X2JldHRlcg_size_16_color_FFFFFF_t_70]: /images/20221005/64edfcbc56e04e66b567110caaea39f0.png
[https_dev.mysql.com_doc_refman_5.6_en_fractional-seconds.html]: https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html