跳到主要内容

Mysql 时间四舍五入

· 阅读需 4 分钟
tanzhuo

MySQL 的 DATETIME 默认精确到秒，而 Java 的 LocalDateTime 精确到纳秒。把带毫秒的时间写入 MySQL 时，超出列精度的部分会被四舍五入，存进去的时间可能凭空多出一秒。

问题现象​

例如，Java 侧的时间是 2022-01-01 12:34:56.789，写入一个普通 DATETIME 列后，查出来变成了 2022-01-01 12:34:57——毫秒部分被四舍五入进位了。

这个偏差平时不起眼，但在几个场景下会暴露出来：

  1. 单测里把实体存库再查出来，用 assertEquals 比较时间字段，偶发失败——只有毫秒大于等于 500 时才进位，所以问题时隐时现。

  2. 用创建时间做游标分页或范围查询，边界记录被多算或漏算一条。

  3. 日志里的时间和库里的时间差一秒，排查链路时对不上。

原理简述​

MySQL 从 5.6.4 开始，DATETIME、TIMESTAMP、TIME 都支持小数秒，精度由类型定义里的 fsp（fractional seconds precision）决定，取值 0 到 6，默认为 0，也就是只到秒。

关键行为是：当插入值的精度高于列精度时，MySQL 按 SQL 标准做四舍五入，而不是截断，且默认不产生任何告警。所以 .789 会把秒位进上去，而 .400 则被静默丢弃——两种结果都和 Java 内存里的值不一致。

Java 这边，LocalDateTime 内部以纳秒保存时间，JDBC 驱动写库时会把小数秒一并传给服务端，于是舍入就发生在 MySQL 一侧。java.sql.Timestamp 同样是纳秒精度，只把 Java 类型换成它并不能避免数据库端的舍入，问题的根子在列的精度定义上。

解决方案​

1) 提高列精度​

如果业务需要毫秒精度（绝大多数记录创建、更新时间的场景够用），直接把列定义成 DATETIME(3)：

ALTER TABLE t_order
  MODIFY create_time DATETIME(3) NOT NULL;

需要微秒就用 DATETIME(6)。注意配套的默认值函数也要带精度，NOW() 只到秒，要写 NOW(3) 或 CURRENT_TIMESTAMP(3)，否则默认值本身就没有小数秒。

2) 在 Java 侧提前截断​

如果不想动表结构，就在写库前把精度对齐到秒：

// 丢弃秒以下的部分，和 DATETIME(0) 的精度对齐
LocalDateTime now = LocalDateTime.now().truncatedTo(ChronoUnit.SECONDS);

这样内存里的值和落库的值完全一致，比较、断言都不会再有偏差。公共的实体基类或审计字段填充器里统一处理一次即可。

踩坑与注意​

  • 舍入是"进位"而不是"抹零"。很多人下意识以为多余精度会被截掉，实际是四舍五入，这正是"时间多一秒"这类怪问题的来源。MySQL 8.0 提供了 TIME_TRUNCATE_FRACTIONAL 这个 sql_mode，可以把行为改成截断，但它影响全局行为，改之前要评估存量业务。
  • 改成 DATETIME(3) 后，存量数据的小数秒都是 .000，新旧数据混合排序时注意这一点。
  • fsp 越高占用的存储越多，按需选择即可，没必要一律用 DATETIME(6)。

小结​

时间"多一秒"的本质是精度不匹配：Java 传纳秒，MySQL 列只存到秒，多余部分被四舍五入。两条路解决——要么把列改成 DATETIME(3)/DATETIME(6) 让数据库存得下，要么在 Java 侧 truncatedTo(ChronoUnit.SECONDS) 提前对齐。原则只有一条：让内存里的精度和列定义的精度一致，写进去的就是读出来的。

标签：

评论 / COMMENTS