MySQL 5.7中str_to_date函数返回NULL问题咨询

原因分析 #

MySQL 5.7默认的sql_mode包含NO_ZERO_DATE规则。当使用STR_TO_DATE仅解析时间字符串（无日期部分）时，函数会自动补全默认日期'0000-00-00'，而这个日期属于无效的零日期，触发NO_ZERO_DATE限制，最终导致函数返回NULL并抛出警告。

执行SHOW WARNINGS;可查看具体警告信息，通常会得到：

Warning | 1411 | Incorrect datetime value: '15:30:00.000000' for function str_to_date

解决方法 #

1. 临时调整会话级sql_mode #

执行以下命令移除NO_ZERO_DATE及关联的NO_ZERO_IN_DATE规则（保留其他必要模式）：

SET SESSION sql_mode = REPLACE(@@sql_mode, 'NO_ZERO_DATE,NO_ZERO_IN_DATE', '');

调整后重新执行原查询，会返回0000-00-00 15:30:00.000000。

2. 永久修改全局sql_mode #

编辑MySQL配置文件（如Linux的/etc/my.cnf、Windows的my.ini），修改sql_mode配置项，移除NO_ZERO_DATE和NO_ZERO_IN_DATE：

sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

保存配置后重启MySQL服务，修改永久生效。

3. 显式补充合法日期 #

如果不想修改sql_mode，可在时间字符串前拼接一个合法日期，确保生成有效的datetime值：

SELECT STR_TO_DATE('2024-01-01 15:30:00.000000', '%Y-%m-%d %H:%i:%s.%f');

返回结果为2024-01-01 15:30:00.000000。

4. 使用STR_TO_TIME获取纯时间结果 #

若仅需时间部分，改用STR_TO_TIME函数，它不会补全日期，直接返回TIME类型值：

SELECT STR_TO_TIME('15:30:00.000000', '%H:%i:%s.%f');

返回结果为15:30:00.000000。

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