MySQL使用BETWEEN查询dd-mm-yyyy格式日期区间结果包含往年数据

问题根源 #

你的查询逻辑错误本质是bill_date字段使用字符串类型存储日期，而非MySQL原生DATE/DATETIME类型：字符串比较是按字符位从左到右逐一比对，不是按日期的年/月/日优先级做逻辑判断。
举个例子，字符串'16-09-2020'和左边界'15-09-2021'比对时，第二位6 > 5就会直接判定该字符串大于左边界，和右边界'23-09-2021'比对时第一位1 < 2就会判定小于右边界，最终2020年的无关数据就会被误纳入结果集。

修复方案 #

临时查询修复（无需修改表结构） #

使用MySQL内置STR_TO_DATE函数，把字符串格式的日期统一转为原生日期类型后再做区间判断，正确查询语句如下：

SELECT * FROM `msr_bills` 
WHERE STR_TO_DATE(`bill_date`, '%d-%m-%Y') 
BETWEEN STR_TO_DATE('15-09-2021', '%d-%m-%Y') AND STR_TO_DATE('23-09-2021', '%d-%m-%Y');

注意格式符要和存储结构匹配：%d代表2位数日期，%m代表2位数月份，%Y代表4位数年份。

永久优化方案（推荐） #

直接修改bill_date字段为原生DATE类型，从根源避免字符串比对的逻辑错误，同时支持索引优化查询性能，操作步骤如下：

• 先备份全表数据，避免操作失误导致数据丢失
• 新增临时DATE类型字段存储转换后的日期

ALTER TABLE `msr_bills` ADD COLUMN `bill_date_new` DATE;

• 把原有字符串日期转换为DATE类型写入新字段

UPDATE `msr_bills` SET `bill_date_new` = STR_TO_DATE(`bill_date`, '%d-%m-%Y');

• 校验新字段数据无误后，替换原有字段

ALTER TABLE `msr_bills` DROP COLUMN `bill_date`;
ALTER TABLE `msr_bills` CHANGE COLUMN `bill_date_new` `bill_date` DATE;

修改完成后，直接使用MySQL标准日期格式yyyy-mm-dd查询即可，示例：

SELECT * FROM `msr_bills` WHERE `bill_date` BETWEEN '2021-09-15' AND '2021-09-23';

补充说明 #

如果暂时无法修改字段类型，也可以把存储的日期字符串顺序调整为yyyy-mm-dd格式，此时字符串逐位比对的结果和日期逻辑比对结果一致，也能避免跨年份的误判问题，但性能和数据合法性校验能力仍不如原生日期类型。

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