MySQL日期范围查询无返回值问题：日期字段为varchar类型

问题根因 #

你遇到的问题核心是varchar类型的日期用字符串逻辑比较，和实际日期大小逻辑不一致：

• 你存储的日期格式是MM/DD/YYYY，字符串逐位比较时，11/28/2020的首字符是1，比你现有所有数据01/13/2021的首字符0更大，所以BETWEEN 11/28/2020 AND 当前日期的条件会过滤掉所有2021年1月开头的记录，自然返回空结果。
• 你之前转换日期不生效，大概率是STR_TO_DATE的格式参数写错了，没有和你存储的月/日/年格式匹配。

具体解决方案 #

方案1：直接修改查询条件（无需改表，快速生效）

把WHERE子句中的日期条件，统一转成DATE类型后再比较，注意格式符要严格对应你存储的MM/DD/YYYY格式，替换后的条件如下：

AND STR_TO_DATE(t1.procedureDate, '%m/%d/%Y') 
    BETWEEN STR_TO_DATE('%VAR2%', '%m/%d/%Y') 
    AND STR_TO_DATE('%CURRENT_DATE%', '%Y-%m-%d')

注意：如果你的%CURRENT_DATE%参数也是MM/DD/YYYY格式，把最后一个格式符改成%m/%d/%Y即可。另外要保证%VAR2%替换后是带引号的字符串格式，避免SQL语法错误。

方案2：改表结构彻底解决（推荐长期使用）

直接把procedureDate字段的数据类型从varchar改成DATE类型，一劳永逸避免字符串比较的坑，还可以加索引大幅提升日期范围查询的性能：

-- 先把字段内容转成标准日期格式，再修改类型
ALTER TABLE BackbenchKidneyChartTrainee 
MODIFY COLUMN procedureDate DATE 
USING STR_TO_DATE(procedureDate, '%m/%d/%Y');

如果不能修改原表结构，可以加一个存储生成列来存转换后的日期，查询时直接用生成列即可：

-- 新增自动计算的日期类型生成列
ALTER TABLE BackbenchKidneyChartTrainee 
ADD COLUMN procedure_date_dt DATE AS (STR_TO_DATE(procedureDate, '%m/%d/%Y')) STORED;
-- 可以给生成列加索引提升查询速度
CREATE INDEX idx_procedure_dt ON BackbenchKidneyChartTrainee(procedure_date_dt);

额外注意事项 #

如果你的表中存在格式不合法的日期值（比如00/00/0000、缺字符的异常值），STR_TO_DATE会返回NULL，这些记录会被条件过滤，你可以加IFNULL函数自定义异常值的处理逻辑。

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