MySQL中DD-MMM-YY格式字符串日期列筛选无效怎么办？

问题根源 #

你用字符串类型存储日期，直接做字符串比较是按字符字典序而非日期逻辑判断，比如字符串'01-Jan-23'会被判定为小于'20-Sep-22'（因为第一个字符0比2小），但实际日期是前者更大，这就导致筛选返回了所有记录。

解决方案 #

1. 临时查询方案（适合一次性需求）

将字符串日期转换为日期类型后再比较，不同数据库用对应的转换函数：

• MySQL/MariaDB：使用STR_TO_DATE函数，指定匹配格式%d-%b-%y

  SELECT * FROM mytable WHERE STR_TO_DATE(`date_column`, '%d-%b-%y') > STR_TO_DATE('20-SEP-22', '%d-%b-%y');
  

  注意：需确保数据库语言环境支持英文月份缩写（如Sep），否则无法正确解析。
• Oracle：使用TO_DATE函数，格式模型为'DD-MON-RR'

  SELECT * FROM mytable WHERE TO_DATE(date_column, 'DD-MON-RR') > TO_DATE('20-SEP-22', 'DD-MON-RR');
  

  （RR格式会自动处理两位年份的世纪转换，比如22会识别为2022年）

2. 永久优化方案（适合1000万条数据的长期查询，性能更好）

字符串日期的查询性能差且逻辑易出错，建议替换为日期类型列：

1. 添加日期类型列

   -- MySQL示例
   ALTER TABLE mytable ADD COLUMN actual_date DATE;
   

2. 批量转换数据到新列

   UPDATE mytable SET actual_date = STR_TO_DATE(`date_column`, '%d-%b-%y');
   

3. 给新列创建索引，提升大表查询速度

   CREATE INDEX idx_actual_date ON mytable(actual_date);
   

4. 后续查询使用新列

   SELECT * FROM mytable WHERE actual_date > '2022-09-20';
   

额外注意 #

• 确保日期字符串的月份缩写大小写与函数解析规则一致（比如数据是Jul就不要用JUL作为查询值）；
• 批量更新1000万条数据时，建议分批次执行，避免锁表影响业务。

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