解决MySQL日期字段类型不匹配导致的时间区间查询异常

前言

在今天的开发过程中，我碰到了一个关于时间查询的bug。在使用MyBatis Plus的Lambda表达式进行时间区间查询时，发现某些满足条件的数据无法被正确检索出来。经过一番分析，发现问题根源在于查询条件与数据库实际存储的时间格式不匹配。

问题描述

场景是这样的：在我们的系统中，数据库表SysOss中的createTime字段存储的是完整的日期时间信息，格式为yyyy-MM-dd HH:mm:ss。然而，在进行查询时，前端传递过来的查询条件beginCreateTime和endCreateTime却是仅包含日期部分的字符串，格式为yyyy-MM-dd。

当我们使用如下代码进行时间区间的查询时：

lqw.between(params.get("beginCreateTime") != null && params.get("endCreateTime") != null,
    SysOss::getCreateTime, params.get("beginCreateTime"), params.get("endCreateTime"));

由于数据库中的createTime包含了时间部分，而查询条件仅指定了日期部分，这就导致了即使是同一天的数据，也无法匹配到——比如数据库中有一条记录的createTime是2024-03-26 19:07:00，而查询条件为beginCreateTime和endCreateTime均为2024-03-26时，这条记录未能被正确筛选出来。

解决方案

针对这个问题，我提出了两种不同的解决方案：

方案一：扩展查询条件的时间范围

调整查询语句，将结束时间的条件增加至当天的最末时刻，即：

lqw.between(params.get("beginCreateTime") != null && params.get("endCreateTime") != null,
    SysOss::getCreateTime, params.get("beginCreateTime"), params.get("endCreateTime") + " 23:59:59");

这样，查询条件实际上变成了从beginCreateTime的零点到endCreateTime的23点59分59秒之间，可以确保涵盖全天的数据。

方案二：使用SQL函数进行格式转换与比较

另一种解决方法是对数据库中的createTime字段做格式化处理，使其与查询条件在同一格式下进行比较：

// 开始时间的条件处理
lqw.apply(params.get("beginCreateTime") != null, 
    "date_format(`create_time`, '%Y-%m-%d') >= date_format({0}, '%Y-%m-%d')", params.get("beginCreateTime"));

// 结束时间的条件处理
lqw.apply(params.get("endCreateTime") != null, 
    "date_format(`create_time`, '%Y-%m-%d') <= date_format({0}, '%Y-%m-%d')", params.get("endCreateTime"));

这种方式利用MySQL的date_format函数将数据库中的createTime字段转为日期格式并与查询条件进行比较，确保只比较日期部分，忽略时间部分。

总结

通过对查询条件或数据库字段进行适当的转换和处理，上述两种解决方案均能有效解决因日期时间格式差异引发的查询异常问题。在实际应用中，可以根据项目需求和数据库性能等因素综合考量选择合适的方法进行优化。同时，这也提醒我们在进行日期时间类型的交互和处理时，充分关注数据格式的统一性和兼容性。