MySQL DATETIME 类型时间精度陷阱：一次由毫秒引发的数据“消失”之谜

原创已于 2025-08-23 13:14:05 修改 · 1.1k 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #java

于 2025-06-30 23:54:18 首次发布

Java 同时被 2 个专栏收录

27 篇文章

订阅专栏

mysql

12 篇文章

订阅专栏

本文提及MySQL 版本均指 5.7.26，涉及表、列值名称均为伪代码，场景是生产环境真实产生的问题，在此记录、分享。
本篇衍生篇已发布，大家快去看看吧：MySQL DATETIME 类型时间精度进阶陷阱：写入时四舍五入引发的“数据漂移“事件

💗💗💗您的点赞、收藏、评论是博主输出优质文章的的动力！！！💗💗💗

欢迎在评论区与博主沟通交流！！大量优质博文关注一波不亏！👇🏻 👇🏻 👇🏻

文章目录

MySQL DATETIME 类型时间精度陷阱：一次由毫秒引发的数据“消失”之谜

MySQL DATETIME 类型时间精度陷阱：一次由毫秒引发的数据“消失”之谜

问题现象：数据明明存在却查询不到

我们遇到了一个诡异现象，数据库中存在数据，但是生产环境执行查询时，日志输出返回为null，经过核对在Table1表中，确认存在一条记录：

column1 = 'xxxxxx' 
column2 = '2025-06-25 09:01:54' 
column3 = 1 
column4 = 0

但当执行以下查询时却返回空结果：

SELECT * FROM Table1
WHERE ( 
	column1 = 'xxxxxx' 
  AND column2 = '2025-06-25 09:01:54.999' 
  AND column3 = 1 
  AND column4 = 0 
)

排查过程：抽丝剥茧的真相追踪

第一阶段：基础排查（预期30分钟，实际5分钟）

✅ 数据存在性验证

-- 使用精确值查询
SELECT * FROM Table1
WHERE column1 = 'xxxxxx' 
  AND column2 = '2025-06-25 09:01:54' 
  AND column3 = 1 
  AND column4 = 0;

确认数据存在且符合条件

✅ SQL语法检查
- WHERE条件逻辑正确
- 字段名拼写无误
- 表名正确

第二阶段：深度对比分析（关键突破点）

通过 EXPLAIN 和查询日志对比两种SQL：

-- 有效查询
EXPLAIN SELECT ... WHERE column2 = '2025-06-25 09:01:54'

-- 失效查询
EXPLAIN SELECT ... WHERE column2 = '2025-06-25 09:01:54.999'

发现执行计划相同，都使用了索引，但结果不同。此时聚焦到时间字段的精度差异：

查询条件值	实际存储值	是否匹配
`2025-06-25 09:01:54.999`	`2025-06-25 09:01:54.000000`	❌
`2025-06-25 09:01:54`	`2025-06-25 09:01:54.000000`	✅

根因分析：MySQL的时间精度陷阱

DATETIME类型的存储真相

MySQL的 DATETIME 类型在5.6版本后支持微秒级精度，但精度定义在表结构中：

-- 查看表结构
SHOW CREATE TABLE Table1;

-- 输出关键信息
`column2` DATETIME NOT NULL  -- 未指定精度，默认为0

这意味着：

插入2025-06-25 09:01:54.999会被隐式截断为2025-06-25 09:01:54 （这里其实存在另外一个问题，MySQL会自动转换，对毫秒位进行四舍五入，我会在下一篇博客中介绍，MySQL DATETIME 类型时间精度进阶陷阱：写入时四舍五入引发的“数据漂移“事件）
查询时不会自动转换：'2025-06-25 09:01:54' ≠ '2025-06-25 09:01:54.999'

为什么应用层会出现精度不一致？

追查代码，发现问题是因为查询时 column2 字段类型是 java.util.Date，组装查询对象时，方法签名对象中的参数是 java.lang.Long，所以转换时出现了时间精度的问题，伪代码如下所示：

// 这里的timestampL是精确到毫秒级，雷炸了..
public void test(Long timestampL){
  Table1Entity t = new Table1Entity();
  t.setColumn2(new Date(timestampL));
  Table1Entity rT = queryOne(t);
  LOG.info("rt input:{}, output:{}", JsonUtils.toJson(t), JsonUtils.toJson(rT));  
}

当从其他微服务获取时间数据时，新的序列化格式引入了毫秒信息。

解决方案

快速止血

这里的快速止血可能不是最好方案，只是为了解决出现问题的业务场景；

我们这里直接修改 Table1Entity.java 类中的 column2 属性的set方法，直接把属性的时间毫秒位置成0；

考虑到此场景在写库 column2 值时，就已经把毫秒位给截断掉了，所以直接在应用层也同步做此操作；

 public void setColumn2(Date column2) {
        if (Objects.nonNull(column2)) {
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(column2);
            calendar.set(Calendar.MILLISECOND, 0);
            this.column2 = calendar.getTime();
        } else {
            this.column2 = null;
        }
    }

一些其他想法（未实施）

数据库层优化（中长期方案）

-- 方案1：明确定义精度（需业务停写）
ALTER TABLE Table1 
MODIFY column2 DATETIME(3); -- 精确到毫秒

-- 方案2：使用时间范围查询（兼容现有精度）
SELECT * FROM Table1 
WHERE column1 = 'xxxxxx'
  AND column2 >= '2025-06-25 09:01:54' 
  AND column2 < '2025-06-25 09:01:55'
  AND column3 = 1 
  AND column4 = 0