MySQL错误代码：1411。函数str_to_date的日期时间值不正确：''

Question

我被要求对一款现有应用程序进行更改，该应用程序目前仅部署在Linux生产服务器上。我已经开始在我的Windows本地计算机上使应用程序大部分工作。我在本地PC上安装了来自生产环境的MySQL DB的完整副本。生产DB是Linux上的MySQL v5.0.95，我的本地DB是Windows上的MySQL v5.5，两者都处于InnoDB模式。

我的问题出在以下语句上。为方便其他人帮助而通用化。

update atable 
set adate=DATE_ADD(str_to_date('','%m/%d/%Y'), INTERVAL 0 DAY)
where anum='1'

在某些情况下，传递一个空字符串并不会在生产环境中造成任何问题，并允许记录被保存/更新，但在本地会抛出SQLException。因此，我直接在我的本地数据库中尝试了SQL语句，在两种情况下都得到了以下错误消息。

错误代码：1411。函数str_to_date的''不正确的日期时间值

我查看了生产环境中的my.cnf和本地的my.ini，寻找任何主要差异，我还尝试在本地使用sql-mode "ALLOW_INVALID_DATES"，但结果没有改变。

我知道我可以更改代码以不传递这些空字符串，但是有很多类似的语句，目前我不希望对所有这些语句进行更改。这个客户的预算和时间有限，我想专注于他们的新需求。我正在寻找如何使我的本地环境像生产环境一样工作的建议，如果可能的话。

感谢您的时间。

- ItBHarsH

---

“''”不是一个有效的日期..应该添加什么日期？当前日期吗？ - Explosion Pills

不，它在本地和生产环境中都传递了一个空字符串。我知道这很奇怪，而且我也不确定它在生产环境中是如何工作的，但它确实可以。 - ItBHarsH

Answer 1

SQLException并非直接来自MySQL，而可能是由客户端语言触发的。MySQL只会生成一个警告，您可以忽略它。无论如何，ALLOW_INVALID_DATES SQL模式实际上应该能解决问题。

警告：

mysql> SET @@SESSION.sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (date_created) VALUES (str_to_date('','%m/%d/%Y'));
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '' for function str_to_date |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

没有警告：

mysql> SET @@SESSION.sql_mode='ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (date_created) VALUES (str_to_date('','%m/%d/%Y'));
Query OK, 1 row affected (0.03 sec)

编辑：如果您正在寻找重写查询的方法，可以尝试以下方式：

update atable 
set adate=NULL
where anum='1'

当然，这需要adate是可空的。

- Álvaro González

---

抱歉如果我表达不清。我的代码中出现了SQLException，它报告的是当我在MySQL Workbench的查询窗口运行查询时得到的相同错误。以下输出是我在我的代码之外直接在MySQL Workbench查询窗口中执行原始查询时得到的结果。`ACTION: update atable set adate=DATE_ADD(str_to_date('xxx','%m/%d/%Y'), INTERVAL 0 DAY) where anum='1'Message: Error Code: 1411. Incorrect datetime value: '' for function str_to_date`这种情况发生在是否启用了my.ini中的ALLOW_INVALID_DATES模式。 - ItBHarsH

所以你的代码和Workbench报告警告作为错误。不确定您想要什么答案 - 您可以放松SQL模式以避免基础警告，或更改查询。那基本上就是这样。 - Álvaro González

如上所述，我尝试了ALLOW_INVALID_DATES模式，但没有帮助。生产服务器没有定义任何模式。我一直在查看SQL模式文档，不确定还有什么其他选项可尝试。有什么建议吗？顺便说一句，感谢您的所有输入！ - ItBHarsH

@ItBHarsH - 有没有可能是您的语法不正确或者在不同的会话中运行了查询？ - Álvaro González

我在代码中设置了断点，然后进行复制/粘贴以保持语法一致。 - ItBHarsH

Answer 2

我在使用MySQL Server版本8.0.28-0ubuntu0.20.04.3（Ubuntu）时遇到了同样的问题，代码如下：

insert into documents (data) values (IF((@d := STR_TO_DATE("", "%Y-%m-%d")) IS NULL, null, @d));

但是使用SELECT时没有出现问题：

select IF((@d := STR_TO_DATE("", "%Y-%m-%d")) IS NULL, null, @d);

解决方法是从MySQL更改为MariaDb 10.3.31。错误代码1411不再发生。

我猜测MySQL 8.0.28存在一些bug，因为我在两个版本中完全使用相同的脚本。

- user24302

Answer 3

当我尝试加载一些日期值为空的数据时，我遇到了相同的1411错误：

CLM_FROM_DT is a DATE

LOAD DATA INFILE 'Sample_1.csv'
INTO TABLE INPATIENT
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(DESYNPUF_ID,
@CLM_FROM_DT)
SET CLM_FROM_DT = STR_TO_DATE(@CLM_FROM_DT, '%Y%m%d')

前段时间我曾尝试在MySQL v.5或者更高版本中使用ALLOW_INVALID_DATES，但是好像并没有起作用。现在我升级到了MySQL 8.0，在mysql终端会话中设置ALLOW_INVALID_DATES后，它可以正常工作。所以看来ALLOW_INVALID_DATES比较敏感。我正在运行Mac OS 10.11.6。

- Keith