填坑之慎用Between时间检索

人生无常，大肠包小肠；代码无常，一坑接一坑，坑坑不一样！

问题描述

最近遇到一个问题，使用Mybatis查询表中数据，多么朴质无华的操作呀，两份中搞定！结果打脸来的如此之快，使用接口调用一直查询不到数据！what？这特么分明是没数据呀，这还搞个毛！不死心的我，拷出sql放到mysql图形工具执行，擦，竟然查询到数据了！！！找谁说理去？？？

问题代码

• Mapper.java

  @Repository
  public interface OrgQualityScoreMapper {
  	List<OrgQualityMonthScoreEntity> queryQualityScoreByOrgCode(@Param("orgCode") String orgCode, @Param("startTime") LocalDate startTime, @Param("endTime") LocalDate entTime);
  }
  
  
  

• Mapper.xml

  <select id="queryQualityScoreByOrgCode" resultType="cn.com.yto56.custom.entity.crm.OrgQualityMonthScoreEntity">
      select org_code as orgCode, rpt_date as rptDate, score as score
      from t_bigdata_branch_quality_score_m
      where rpt_date between #{startTime} and #{endTime}
        and org_code = #{orgCode}
        and sum_type = 'M'
  </select>
  
  

• Console打印

  JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@5648d684] will not be managed by Spring
  ==>  Preparing: select org_code as orgCode, rpt_date as rptDate, score as score from t_bigdata_branch_quality_score_m where rpt_date between ? and ? and org_code = ? and sum_type = 'M'
  ==> Parameters: 2024-01-01(Date), 2024-06-01(Date), 210045(String)
  <==      Total: 0
  Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6c13664]
  

• DBeaver执行：

  select org_code as orgCode, rpt_date as rptDate, score as score 
  from t_bigdata_branch_quality_score_m 
  where rpt_date between '2024-01-01' and '2024-06-01'
   and org_code = '210045' 
   and sum_type = 'M'
  
  执行结果：
  orgCode|rptDate   |score  |
  -------+----------+-------+
  210045 |2024-06-01|89.9100|
  
  

问题排查

到底是哪里出了问题，会出现这种问题呢？Mysql的源码咱也看不懂呀，遇事不决，量子力学，只能求助百度了，结果百度跳出一大堆解决方案：有的说Between包左不包右，有的说左右都包括，后来看了一个可行的说法：

1. 普通数字，Between and查询，左右都会包含
2. 时间查询，Between and查询，包左不包右

验证了一下，哎呦我去，还真是这问题！
用我广大网名的话来说就是，Between and时间检索时， 不包含右边，如检索"2024-06-01"时，mysql会自动为我们处理为"2024-05-30 23:59:59"，原来如此，懂了！！！

问题处理

问题排查到了，处理起来就so easy了，方式很多，根据自己的爱好选择吧，我就列举两个常用的吧！

扩展右边界

既然时间查询时，Between and包左不包右，那就把右侧边界拓展一天，或者拓展为当月月底即可，如：

• 调用Mapper接口

  //调用接口，在右边界(endMonth)多加一天
  List<OrgQualityMonthScoreEntity> qualityList = orgQualityScoreMapper.queryQualityScoreByOrgCode(orgCode, startMonth, endMonth.plusDays(1));
  

• console打印

  JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@735be32] will not be managed by Spring
  ==>  Preparing: select org_code as orgCode, rpt_date as rptDate, score as score from t_bigdata_branch_quality_score_m where rpt_date between ? and ? and org_code = ? and sum_type = 'M'
  ==> Parameters: 2024-01-01(Date), 2024-06-02(Date), 210045(String)
  <==    Columns: orgCode, rptDate, score
  <==        Row: 210045, 2024-06-01, 89.9100
  <==      Total: 1
  

因为这里的表是月维度统计，所以只有每个月1号的数据，所以拓展一天没问题，但如果你的数据中拓展日期有数据可能会有影响，所以不推荐使用此方法

时间转字符串

在此之前，我通过DBeaver查询时是可以查询到数据，那是因为我们在输入的时候传入的string类型，而IDEA传入的时候是Date类型，所以我们也可以通过将日期转字符串方式进行查询， 如：

• Mapper接口

  List<OrgQualityMonthScoreEntity> qualityList = orgQualityScoreMapper.queryQualityScoreByOrgCode(orgCode, startMonth.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")), endMonth.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
  

• console打印调用

  JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@ffb7fc1] will not be managed by Spring
  ==>  Preparing: select org_code as orgCode, rpt_date as rptDate, score as score from t_bigdata_branch_quality_score_m where rpt_date between ? and ? and org_code = ? and sum_type = 'M'
  ==> Parameters: 2024-01-01(String), 2024-06-01(String), 210045(String)
  <==    Columns: orgCode, rptDate, score
  <==        Row: 210045, 2024-06-01, 89.9100
  <==      Total: 1
  

至此，大功告捷！！！