MySQL日期范围查询无结果求助：跨10月31日至11月1日查询返回空

你遇到的问题本质是字符串字典序比较 vs 日期时间顺序比较的差异：

当你用DATE_FORMAT(field_date,'%d.%m.%Y')把datetime字段转成dd.mm.yyyy格式的字符串后，MySQL会按照字符串的字典序来进行BETWEEN比较。而字符串比较是从左到右逐个字符对比的：

• '31.10.2019'的第一个字符是3，'01.11.2019'的第一个字符是0
• 因为3 > 0，所以'31.10.2019'作为字符串比'01.11.2019'大
• 你的BETWEEN条件相当于字段值 >= '31.10.2019' AND 字段值 <= '01.11.2019'，这是一个矛盾的范围，自然返回空结果

解决思路是：不要对查询字段使用日期格式化函数（这样会导致索引失效，影响性能），而是将你的查询条件字符串转换为日期类型，或者直接使用MySQL支持的标准日期格式。

方案1：用STR_TO_DATE转换查询条件 #

把你输入的dd.mm.yyyy格式字符串转成日期类型，直接和datetime字段比较：

SELECT field_1, DATE_FORMAT(field_date,'%d.%m.%Y') AS dt 
FROM mytable 
WHERE field_date BETWEEN STR_TO_DATE('31.10.2019', '%d.%m.%Y') 
                     AND STR_TO_DATE('01.11.2019', '%d.%m.%Y');

方案2：使用MySQL标准日期格式（推荐） #

MySQL默认支持YYYY-MM-DD格式的日期字符串，可以直接用来比较，不需要转换函数：

SELECT field_1, DATE_FORMAT(field_date,'%d.%m.%Y') AS dt 
FROM mytable 
WHERE field_date BETWEEN '2019-10-31' AND '2019-11-01';

注意事项（针对datetime类型） #

因为field_date是datetime类型，包含时间部分：

• 如果用'2019-11-01'这样的条件，MySQL会默认解析为'2019-11-01 00:00:00'，这样会漏掉当天0点之后的记录
• 如果需要包含2019-11-01全天的记录，建议修改条件为：

  WHERE field_date >= '2019-10-31' 
    AND field_date < '2019-11-02'; -- 用小于下一天的方式，避免时间精度问题
  

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