MySQL中varchar类型d-m-Y格式日期区间查询错误求助

问题原因 #

你当前的区间查询出错，核心原因是varchar类型的字符串比较是按字典序执行的，而你的日期存储格式是d-m-Y（日-月-年），字符串从左到右依次对比日、月、年，这和实际的日期逻辑顺序（年-月-日）完全相反。比如字符串02-09-2022会被判定为大于01-10-2022（因为前两位02 > 01），导致所有日为02的记录都被误纳入区间，不管月份是否符合要求。

临时解决：正确的查询语句 #

不要将转换后的日期再转回字符串比较，而是直接把存储的varchar日期转换为DATE类型，再和转换后的日期区间做对比：

SELECT * 
FROM inspection 
WHERE STR_TO_DATE(`date`, '%d-%m-%Y') BETWEEN STR_TO_DATE('01-10-2022', '%d-%m-%Y') AND STR_TO_DATE('02-10-2022', '%d-%m-%Y');

也可以直接将参数转换为标准DATE格式后对比，效果一致：

SELECT * 
FROM inspection 
WHERE STR_TO_DATE(`date`, '%d-%m-%Y') BETWEEN '2022-10-01' AND '2022-10-02';

长远优化：修改字段类型为DATE #

用varchar存储日期是不合理的设计——不仅会导致查询逻辑混乱，还无法利用索引，严重影响查询性能。建议将字段类型改为DATE：

1. 新增DATE类型的临时字段：

ALTER TABLE inspection ADD COLUMN date_real DATE;

2. 将原有varchar日期转换后写入新字段：

UPDATE inspection SET date_real = STR_TO_DATE(`date`, '%d-%m-%Y');

3. 验证数据无误后，删除原字段并将新字段重命名为原名称：

ALTER TABLE inspection DROP COLUMN `date`;
ALTER TABLE inspection CHANGE COLUMN date_real `date` DATE;

之后插入或查询日期时，直接使用DATE类型的语法即可，无需再做字符串转换。

内容的提问来源于stack exchange，提问作者MAZHAR IQBAL RANA