MySQL使用INSERT..SELECT插入时报str_to_date日期格式错误原因问询

异常原因 #

该问题是MySQL查询优化器在不同执行场景下的策略差异导致的：

• 单独执行SELECT语句时，MySQL在非严格查询模式下遇到STR_TO_DATE转换非法日期的情况，会静默返回NULL，不会抛出错误，因此WHERE条件可以正常过滤出符合要求的行。
• 执行INSERT INTO ... SELECT语句时，MySQL为了保障插入数据的合法性，默认会开启严格校验，同时优化器会调整执行计划，预先计算全表扫描涉及的函数值，此时STR_TO_DATE遇到非法日期字符串就会直接抛出转换错误，不会再静默返回NULL。

解决方案 #

你可以选择以下任意一种方案解决问题，无需修改表结构：

方案1：使用INSERT IGNORE跳过错误 #

INSERT IGNORE INTO validation(invoice_id,validation_message)
SELECT id,'INVALID_DATE' FROM invoice WHERE STR_TO_DATE(paid_date, '%Y-%m-%d') IS NULL;

IGNORE关键字会将转换错误降级为警告，自动跳过触发错误的行，不影响合法结果的插入，完全适配你筛选无效日期的需求。

方案2：前置格式校验避免触发转换错误 #

如果不想使用IGNORE，可以先通过正则匹配过滤格式明显不符合要求的日期字符串，再调用STR_TO_DATE：

INSERT INTO validation(invoice_id,validation_message)
SELECT id,'INVALID_DATE' FROM invoice 
WHERE paid_date NOT REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
OR STR_TO_DATE(paid_date, '%Y-%m-%d') IS NULL;

该写法先筛掉格式不匹配的字符串，避免非法值传入STR_TO_DATE触发报错。

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