MySQL含日期转换条件的子查询插入时出现1292错误求助

已知STATUS_DATE为LONGTEXT类型，值为'2014-11-30 00:00:00.000'。单独执行以下查询时可正常返回结果：

SELECT 'Wrong DATE format'
FROM  DATE_TABLE
WHERE CONVERT(STR_TO_DATE( STATUS_DATE, '%Y-%c-%e %H:%i:%s'), CHAR(20)) <> STATUS_DATE;

但将该查询作为子查询插入到ERROR_LOG表（THE_ERROR字段为VARCHAR(4000)）时：

INSERT INTO ERROR_LOG (THE_ERROR)
SELECT 'Wrong DATE format'
FROM  DATE_TABLE
WHERE CONVERT(STR_TO_DATE( STATUS_DATE, '%Y-%c-%e %H:%i:%s'), CHAR(20)) <> STATUS_DATE;

触发如下错误：

Error Code: 1292. Truncated incorrect datetime value: '2014-11-30 00:00:00.000'

使用CONVERT函数的目的是确保WHERE条件中的结果保持字符串类型，避免转为DATE类型，但仍存在隐式日期转换，且仅在插入时出错，请问问题出在哪里？

原因与解决方案 #

问题根源 #

1. 格式串不匹配：STR_TO_DATE使用的格式串'%Y-%c-%e %H:%i:%s'无法解析带毫秒的'2014-11-30 00:00:00.000'，因为格式串中没有.%f部分匹配毫秒后缀，导致STR_TO_DATE返回NULL。
2. 执行场景的校验严格性差异：单独执行SELECT时，MySQL查询优化器对无效日期转换的处理更宽松；而INSERT操作会触发更严格的行级校验（尤其是sql_mode包含STRICT_TRANS_TABLES或STRICT_ALL_TABLES时），此时无效日期转换的警告会升级为错误。
3. 隐式转换触发：即使使用了CONVERT，STR_TO_DATE返回NULL后，后续的字符串比较操作会触发隐式类型转换，进一步触发截断错误。

解决方案 #

方案1：修正格式串匹配毫秒部分

修改STR_TO_DATE的格式串，加入毫秒匹配规则.%f，同时调整CONVERT的CHAR长度以匹配完整日期字符串：

INSERT INTO ERROR_LOG (THE_ERROR)
SELECT 'Wrong DATE format'
FROM  DATE_TABLE
WHERE CONVERT(STR_TO_DATE(STATUS_DATE, '%Y-%c-%e %H:%i:%s.%f'), CHAR(23)) <> STATUS_DATE;

（注：CHAR(23)对应YYYY-MM-DD HH:MM:SS.SSS的完整长度）

方案2：直接用正则做字符串格式校验

如果不需要实际转换为日期类型，仅需校验字符串格式，用正则表达式可彻底避免日期转换相关的隐式问题：

INSERT INTO ERROR_LOG (THE_ERROR)
SELECT 'Wrong DATE format'
FROM  DATE_TABLE
WHERE STATUS_DATE NOT REGEXP '^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2} [0-9]{2}:[0-9]{2}:[0-9]{2}(\\.[0-9]{1,3})?$';

方案3：临时调整sql_mode（不推荐）

若需临时绕过严格校验，可执行以下命令关闭严格模式，但这会降低数据校验的严谨性，不建议生产环境使用：

SET sql_mode = REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', '');

内容的提问来源于stack exchange，提问作者Hisham M. Najem