MySQL BETWEEN日期查询遗漏最后一天数据问题排查

问题现象 #

使用BETWEEN按日期范围筛选数据时，无法返回最后一天的完整数据。例如查询2022-09-20至2022-09-23的数据，仅返回2022-09-20到2022-09-22的结果，缺失2022-09-23的记录。

执行的SQL语句 #

SELECT Delivery.Delivery, `Order`.`Order`, Order.Remittance, Delivery.Date 
FROM Delivery e
INNER JOIN `Order` p 
ON Delivery.Delivery = Order.Delivery 
WHERE Delivery.Date BETWEEN '2022-09-20' AND '2022-09-23'
GROUP BY Delivery.Delivery, `Order`.`Order`, Order.Remit 
ORDER BY Delivery.date ASC

注意：Order是MySQL关键字，建议用反引号包裹避免语法冲突；另外SELECT中的Order.Remittance与GROUP BY中的Order.Remit字段名不一致，需修正后才能正常执行。

当前返回结果 #

DELIVERY ORDER REMIT DATE
1111     1111  1111  2022-09-20
2222     2222  2222  2022-09-21
3333     3333  3333  2022-09-22

预期返回结果 #

DELIVERY ORDER REMIT DATE
1111     1111  1111  2022-09-20
2222     2222  2222  2022-09-21
3333     3333  3333  2022-09-22
4444     4444  4444  2022-09-23

问题原因 #

核心问题出在日期字段类型和BETWEEN的匹配逻辑上：

• 如果Delivery.Date是datetime/timestamp类型（包含时分秒信息），MySQL会将字符串'2022-09-23'自动解析为'2022-09-23 00:00:00'。
• BETWEEN是闭区间匹配，仅会包含<= '2022-09-23 00:00:00'的记录，而2022-09-23当天00:00:00之后的所有数据都会被排除。

解决方案 #

推荐以下三种可靠的修正方式：

1. 调整结束日期为次日0点（最优，不影响索引） #

将条件改为小于次日的0点，确保包含目标日期的所有时间数据：

WHERE Delivery.Date >= '2022-09-20' 
  AND Delivery.Date < '2022-09-24'

2. 截断日期字段的时间部分（需注意索引失效） #

使用DATE()函数提取日期部分，忽略时分秒：

WHERE DATE(Delivery.Date) BETWEEN '2022-09-20' AND '2022-09-23'

提示：这种方式会导致Delivery.Date上的索引无法被使用，数据量大时查询性能会下降。

3. 指定结束时间为当天最后一刻（适用于秒级精度字段） #

明确匹配到当天的23:59:59：

WHERE Delivery.Date BETWEEN '2022-09-20 00:00:00' AND '2022-09-23 23:59:59'

注意：如果字段是毫秒级精度（如datetime(3)），这种方式会漏掉2022-09-23 23:59:59.001及之后的记录，不如第一种方法通用。

内容的提问来源于stack exchange，提问作者Bilbit Bolson