MySQL分组查询结果不一致：7月交易日报漏数问题咨询

问题描述 #

我有一张名为transaction的表存储支付系统交易记录，想要生成2023年7月的每日交易报告，执行以下查询时：

SELECT DAY(CREATED), count(*) FROM `transaction`
WHERE STATUS = 'approved' AND MONTH(CREATED) = 07 AND YEAR(CREATED) = 2023
GROUP BY DAY(CREATED)

结果跳过了部分日期（比如7号无数据返回），但添加DAY(CREATED) = 7条件后：

SELECT DAY(CREATED), count(*) FROM `transaction`
WHERE STATUS = 'approved' AND MONTH(CREATED) = 07 AND YEAR(CREATED) = 2023 AND DAY(CREATED) = 7
GROUP BY DAY(CREATED);

却能正常返回7号的交易统计数。请问这种结果不一致的原因是什么？如何修改第一个查询解决问题？

原因分析 #

• 八进制字面量混淆：MySQL中以0开头的数字会被解析为八进制数，07虽然等价于十进制的7，但这种写法容易引发类型转换问题，在复杂查询场景下可能导致过滤逻辑异常。
• 函数导致索引失效与执行计划偏差：使用MONTH(CREATED)、YEAR(CREATED)这类函数会让MySQL无法利用CREATED字段的索引（若存在），只能执行全表扫描。当表数据量较大时，全表扫描可能因统计信息不准确、优化器执行计划选择偏差，导致部分符合条件的记录被遗漏。
• 时区或函数计算潜在问题：若数据库时区与业务时区不一致，MONTH()/YEAR()函数的计算结果可能不符合预期，但此场景下添加DAY(CREATED)=7能查到结果，说明该因素概率较低。

解决方法 #

基础修复（仅返回有交易的日期） #

避免使用日期函数过滤，改用时间范围查询直接匹配CREATED字段，既消除函数带来的潜在问题，又能利用索引提升效率：

SELECT DAY(CREATED), COUNT(*) 
FROM `transaction`
WHERE STATUS = 'approved' 
  AND CREATED >= '2023-07-01 00:00:00' 
  AND CREATED < '2023-08-01 00:00:00'
GROUP BY DAY(CREATED)

进阶修复（返回所有日期，无交易显示0） #

如果需要完整的7月每日报告（包括无交易的日期），可以生成包含7月所有日期的临时表，再与交易统计结果左连接：

WITH july_dates AS (
  SELECT ADDDATE('2023-07-01', INTERVAL (n-1) DAY) AS date
  FROM (
    SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
    UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
    UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25
    UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
    UNION ALL SELECT 31
  ) numbers
)
SELECT DAY(j.date) AS day, COALESCE(t.count, 0) AS transaction_count
FROM july_dates j
LEFT JOIN (
  SELECT DAY(CREATED) AS day, COUNT(*) AS count
  FROM `transaction`
  WHERE STATUS = 'approved' 
    AND CREATED >= '2023-07-01 00:00:00' 
    AND CREATED < '2023-08-01 00:00:00'
  GROUP BY DAY(CREATED)
) t ON DAY(j.date) = t.day
ORDER BY j.date;

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