升级Mysql5.6,时间被四舍五入

生产环境升级Mysql 5.6.35 版本后,某接口频繁出现检查modifyTime不匹配的情况。排查后,发现DB表里的modifyTime 比Java程序生成的modifyTime多了1秒。

例子

 

数据表里的create_time是: 2017-02-17 10:54:48.0 (第一次插入时,modify_time = create_time,但后来资讯被审核,modify_time改变,故以create_time参照)

然而,对比SQL日志里的原始数据,create_time是 2017-02-17 10:54:47.716

 

——————————————————————-

2017-02-17 10:54:47.716  ——>  2017-02-17 10:54:48.0

时间被四舍五入了!

原因

根本原因是 Mysql 5.6.4开始,date,datetime,timestamp  支持小数(fractional second part),最大支持6位精度(微秒)
。悲剧的是,
如果字段的定义长度 < 小数部分长度,Mysql 5.6会对小数部分进行四舍五入。

从Mysql 5.1迁移到5.6时, 资讯审核表的timestamp长度保持为0 ,而应用传给Mysql的时间值包含了小数部分,最终被Mysql 四舍五入,变为2017-02-17 10:54:48.0 , 增加了1秒。

 

 

Mysql 5.6 官方文档如下:

MySQL 5.6.4 and up expands fractional seconds support for TIMEDATETIME,
and TIMESTAMP values,
with up to microseconds (6 digits) precision:


Inserting a TIMEDATE,
or 
TIMESTAMP value
with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding, as shown in this example:

….

No warning or error is given when such rounding occurs. This behavior follows the SQL standard, and is not affected by the server’s sql_mode setting.

Mysql  5.6 对小数进行 四舍五入操作,不会报错或Warining,貌似也没有开关配置是否允许四舍五入。消无声息就给你做了,你还不能拒绝这个功能,比较坑。

 

JDBC

其实除了需要 Mysql 为 5.6.4以上版本,还需要JDBC配合,你才会中招。

mysql-connector-java驱动,由5.1.6升级至5.1.30时,datetime字段的毫秒处理不同。”com.mysql.jdbc.PreparedStatement“中的

(1)5.1.6版本,直接丢弃毫秒部分(代码飘黄部分)

(2)5.1.30版本,判断若server端版本为5.6.4及以上时,则将小数部分一同提交至server(代码飘黄部分)

 

解决方案

暂时想到 3种方案:

1、time/datetime/ timestamp 字段增加长度定义, 例如,改成 timestamp(3)  会存储毫秒,但以后等值查询的条件,如果不带毫秒,查不出结果
2、在应用程序层面修改,入库前,拦截datetime、timestamp,去掉小数部分

3、使用5.1.6版本以下的mysql-connector-java驱动(?未验证)

IT文库 » 升级Mysql5.6,时间被四舍五入
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址