php MySQL 错误 - 无效的日期时间格式：1292 不正确的日期时间值：'' 列

MySQL error - Invalid datetime format: 1292 Incorrect datetime value: '' for column

提问by daveloper

I am using mysql in Os X 10.9.2 and when I try to run my php website in local I have this error:

我在 Os X 10.9.2 中使用 mysql，当我尝试在本地运行我的 php 网站时出现此错误：

Invalid datetime format: 1292 Incorrect datetime value: '' for column nameOfTheColumn

无效的日期时间格式：1292 不正确的日期时间值：'' 列 nameOfTheColumn

The Error happens when I tried to insert an empty value in a datetime field. The site works fine in production and in some other locals computers, so the problem is in database and I have to be solve in my local database. Reading about the problem I read that mysql could be running in Strict mode, so I edit /usr/local/mysql/my.cnf and I comment out this line:

当我尝试在日期时间字段中插入空值时发生错误。该站点在生产环境和其他一些本地计算机上运行良好，因此问题出在数据库中，我必须在本地数据库中解决。阅读有关 mysql 可以在严格模式下运行的问题的阅读，所以我编辑 /usr/local/mysql/my.cnf 并注释掉这一行：

 #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

I restart computer and still is not working, but according to the documentation online and others questions, this is the way, but I would like to know What is it the exactly change I have to do to make my application works. Repeat, the code works in production, windows with xampp, etc... so can not be change because is to long and just for work in local I need the right change in MYSQL DATABASE.

我重新启动计算机，但仍然无法正常工作，但根据在线文档和其他问题，这就是方法，但我想知道要使我的应用程序正常工作，我必须做哪些更改。重复一遍，代码在生产环境中工作，带有 xampp 的 windows 等......所以不能改变，因为太长而且只是为了在本地工作我需要在 MYSQL DATABASE 中进行正确的更改。

Thanks in advance!

提前致谢！

回答by O. Jones

It looks like the default sql_mode on your osx instance of MySQL is set to refuse invalid dates.

看起来您的 osx MySQL 实例上的默认 sql_mode 设置为拒绝无效日期。

You could try issuing this command:

您可以尝试发出以下命令：

SET SESSION sql_mode='ALLOW_INVALID_DATES' 

as you initialize your session.

当您初始化会话时。

回答by user3470953

you can change your column to allow null

您可以更改列以允许空值

ALTER TABLE table_name MODIFY datetime_column_name datetime;

Columns are nullable by default as long as a column is not declared as NOT NULL.

只要未将列声明为 NOT NULL，列默认情况下可以为空。

回答by daveloper

At last I found the solution, in fact I was on the way, the solution is to comment out sql_mode, but the file located in /etc/my.cnf instead of /usr/local/mysql/my.cnf. If you do not comment this line, and you try to insert an empty value an error is raised, but if you comment it out, empty values becomes '0000-00-00 00:00:00'

终于找到解决办法了，其实我在路上，解决办法是把sql_mode注释掉，但是文件在/etc/my.cnf而不是/usr/local/mysql/my.cnf。如果您不注释此行，并且您尝试插入一个空值，则会引发错误，但如果您将其注释掉，空值将变为 '0000-00-00 00:00:00'

回答by Andron

There was a similar issue on our MySQL 5.6 server.
When I changed sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESto sql_mode=NO_ENGINE_SUBSTITUTION- the issue was fixed.

我们的 MySQL 5.6 服务器上也有类似的问题。
当我更改sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES为sql_mode=NO_ENGINE_SUBSTITUTION- 问题已解决。

Of course, it is a good idea to be sure that the value is in the correct format.
For example, if the column type is "DATETIME", but in php we try to insert the value generated by date('c')- we'll get that error (if strict mode is turned on). But when we'll change it to: date('Y-m-d H:i:s')- the issue is gone.

当然，最好确保该值的格式正确。
例如，如果列类型是“DATETIME”，但在 php 中我们尝试插入由生成的值date('c')- 我们会得到该错误（如果打开了严格模式）。但是当我们将其更改为：date('Y-m-d H:i:s')- 问题消失了。