MySQL错误代码:1411.错误的日期时间值:"表示函数str_to_date [英] MySQL Error Code: 1411. Incorrect datetime value: '' for function str_to_date

问题描述

已要求我对现有应用程序进行更改，该应用程序目前仅部署到Linux Production服务器.我已经前进，大部分应用程序都可以在本地Windows PC上运行.我在本地PC上安装了Production的MySQL DB的完整副本.在Linux上，生产数据库是MySQL v5.0.95，在Windows上，我的本地数据库是MySQL v5.5.两者都处于InnoDB模式.

I have been asked to make changes to an existing application that at this time is only deployed to a Linux Production server. I have gone ahead and have the app for the most part working on my local Windows PC. I have a full copy of the MySQL DB from Production installed on my local PC. The Production DB is MySQL v5.0.95 on Linux and my local DB is MySQL v5.5 on Windows. Both are in InnoDB mode.

我的问题是这样的声明.成为通用名称，以方便其他想要帮助的人使用.

My issue is with a statement such as the following. Made generic for ease of use by others wanting to help.

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

在某些情况下，将传递一个空字符串，其中在Production中不会引起任何问题，并且允许保存/更新记录，但是在本地它会引发SQLException.因此，我直接针对本地数据库尝试了SQL语句，在两种情况下都收到以下错误消息.

In some cases an empty string is passed in which in Production does not cause any issues and allows the record to be saved/updated but locally it throws a SQLException. So I tried the SQL statement directly against my local DB and I get the following error message in both cases.

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

Error Code: 1411. Incorrect datetime value: '' for function str_to_date

我已经查看了生产版my.cnf和本地的my.ini来寻找任何主要差异，并且我还尝试了在本地使用sql-mode"ALLOW_INVALID_DATES"，但是它并没有改变最终结果.

I've looked at the Production my.cnf and my local my.ini looking for any major differences and I have also tried to use the sql-mode "ALLOW_INVALID_DATES" locally but it did not change the end result.

我知道我可以更改代码以不传入这些空字符串，但是有很多类似这样的语句，目前我不希望对所有这些进行更改.该客户的预算和时间有限，我想关注他们的新要求.我正在寻找有关如何尽可能使本地环境像在生产环境中那样工作的信息.

I know that I could change the code to not pass in these empty strings in but there are many statements like this and at this time I do not wish to make changes to all of these if possible. This customer has a limited budget and timeframe and I want to focus on their new requirements. I'm looking for input as to how I can get my local environment working as it does in Production if possible.

感谢您的时间.

推荐答案

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

The SQLException does not come directly from MySQL, it's probably triggered by your client language. MySQL will just generate a warning you can normally ignore. Whatever, the ALLOW_INVALID_DATES SQL mode should actually do the trick:

警告:

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)

编辑:如果您正在寻找一种重写查询的方法，则可以尝试执行以下操作:

If you are looking for a way to rewrite the query, you could try something like this:

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

当然，这要求adate可以为空.

Of course, this requires that adate is nullable.

这篇关于MySQL错误代码:1411.错误的日期时间值:"表示函数str_to_date的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！