MySQL 不正确的日期时间值：'0000-00-00 00:00:00'

MySQL Incorrect datetime value: '0000-00-00 00:00:00'

提问by lorm

I've recently taken over an old project that was created 10 years ago. It uses MySQL 5.1.

我最近接管了一个 10 年前创建的旧项目。它使用 MySQL 5.1。

Among other things, I need to change the default character set from latin1 to utf8.

除此之外，我需要将默认字符集从 latin1 更改为 utf8。

As an example, I have tables such as this:

例如，我有这样的表：

  CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `first_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `last_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `username` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `email` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `pass` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `active` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'Y',
    `created` datetime NOT NULL,
    `last_login` datetime DEFAULT NULL,
    `author` varchar(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'N',
    `locked_at` datetime DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `ripple_token` varchar(36) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `ripple_token_expires` datetime DEFAULT '2014-10-31 08:03:55',
    `authentication_token` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `index_users_on_reset_password_token` (`reset_password_token`),
    UNIQUE KEY `index_users_on_confirmation_token` (`confirmation_token`),
    UNIQUE KEY `index_users_on_unlock_token` (`unlock_token`),
    KEY `users_active` (`active`),
    KEY `users_username` (`username`),
    KEY `index_users_on_email` (`email`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1677 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

I set up my own Mac to work on this. Without thinking too much about it, I ran "brew install mysql" which installed MySQL 5.7. So I have some version conflicts.

我设置了自己的 Mac 来处理这个问题。没有考虑太多，我运行了安装 MySQL 5.7 的“brew install mysql”。所以我有一些版本冲突。

I downloaded a copy of this database and imported it.

我下载了该数据库的副本并将其导入。

If I try to run a query like this:

如果我尝试运行这样的查询：

  ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci    NOT NULL  

I get this error:

我收到此错误：

  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

I thought I could fix this with:

我以为我可以解决这个问题：

  ALTER TABLE users MODIFY created datetime  NULL DEFAULT '1970-01-01 00:00:00';
  Query OK, 0 rows affected (0.06 sec)
  Records: 0  Duplicates: 0  Warnings: 0

but I get:

但我得到：

  ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci    NOT NULL ;
  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

Do I have to update every value?

我是否必须更新每个值？

采纳答案by Lucia Pasarin

My suggestion if it is the case that the table is empty or not very very big is to export the create statements as a .sql file, rewrite them as you wish. Also do the same if you have any existing data, i.e. export insert statements (I recommend doing this in a separate file as the create statements). Finally, drop the table and execute first create statement and then inserts.

如果表是空的或不是很大，我的建议是将创建语句导出为 .sql 文件，根据需要重写它们。如果您有任何现有数据，也请执行相同的操作，即导出插入语句（我建议在单独的文件中执行此操作作为创建语句）。最后，删除表并执行先创建语句然后插入。

You can use for that either mysqldumpcommand, included in your MySQL installation or you can also install MySQL Workbench, which is a free graphical tool that includes also this option in a very customisable way without having to look for specific command options.

您可以使用mysqldump包含在 MySQL 安装中的任一命令，也可以安装 MySQL Workbench，这是一个免费的图形工具，它也以非常可定制的方式包含此选项，而无需查找特定的命令选项。

回答by obe

I wasn't able to do this:

我无法做到这一点：

UPDATE users SET created = NULL WHERE created = '0000-00-00 00:00:00'

(on MySQL 5.7.13).

（在 MySQL 5.7.13 上）。

I kept getting the Incorrect datetime value: '0000-00-00 00:00:00'error.

我一直收到Incorrect datetime value: '0000-00-00 00:00:00'错误。

Strangely, this worked: SELECT * FROM users WHERE created = '0000-00-00 00:00:00'. I have no idea why the former fails and the latter works... maybe a MySQL bug?

奇怪的是，这个工作：SELECT * FROM users WHERE created = '0000-00-00 00:00:00'。我不知道为什么前者失败而后者有效……也许是 MySQL 错误？

At any case, this UPDATE query worked:

无论如何，此 UPDATE 查询有效：

UPDATE users SET created = NULL WHERE CAST(created AS CHAR(20)) = '0000-00-00 00:00:00'

回答by spencer7593

Changing the default value for a column with an ALTER TABLEstatement, e.g.

使用ALTER TABLE语句更改列的默认值，例如

 ALTER TABLE users MODIFY created datetime  NULL DEFAULT '1970-01-02'

... doesn't change any values that are already stored. The "default" value applies to rows that are inserted, and for which a value is not supplied for the column.

... 不会更改任何已存储的值。“默认”值适用于插入的行，但未为列提供值。

---

---

As to why you are encountering the error, it's likely that the sql_modesetting for your session includes NO_ZERO_DATE.

至于您遇到错误的原因，很可能是sql_mode您的会话设置包括NO_ZERO_DATE.

Reference: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

参考：http: //dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

When you did the "import", the SQL statements that did the INSERT into that table were run in a session that allowed for zero dates.

当您执行“导入”时，执行 INSERT 到该表的 SQL 语句在允许零日期的会话中运行。

To see the sql_mode setting:

要查看 sql_mode 设置：

SHOW VARIABLES LIKE 'sql_mode' ;

-or-

-或者-

SELECT @@sql_mode ;

As far as how to "fix" the current problem, so that the error won't be thrown when you run the ALTER TABLEstatement.

至于如何“修复”当前问题，以便在运行ALTER TABLE语句时不会抛出错误。

Several options:

几个选项：

1) change the sql_modeto allow zero dates, by removing NO_ZERO_DATEand NO_ZERO_IN_DATE. The change can be applied in the my.cnf file, so after a restart of MySQL Server, sql_modevariable will be initialized to the setting in my.cnf.

1)sql_mode通过删除NO_ZERO_DATE和将更改为允许零日期NO_ZERO_IN_DATE。更改可以应用到 my.cnf 文件中，因此在重启 MySQL 服务器后，sql_mode变量将被初始化为 my.cnf 中的设置。

For a temporary change, we can modify the setting with a single session, without requiring a global change.

对于临时更改，我们可以通过单个会话修改设置，而无需全局更改。

-- save current setting of sql_mode
SET @old_sql_mode := @@sql_mode ;

-- derive a new value by removing NO_ZERO_DATE and NO_ZERO_IN_DATE
SET @new_sql_mode := @old_sql_mode ;
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_DATE,'  ,','));
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_IN_DATE,',','));
SET @@sql_mode := @new_sql_mode ;

-- perform the operation that errors due to "zero dates"

-- when we are done with required operations, we can revert back
-- to the original sql_mode setting, from the value we saved
SET @@sql_mode := @old_sql_mode ;

2) change the createdcolumn to allow NULL values, and update the existing rows to change the zero dates to null values

2) 更改created列以允许 NULL 值，并更新现有行以将零日期更改为空值

3) update the existing rows to change the zero dates to a valid date

3) 更新现有行以将零日期更改为有效日期

---

---

We don't need to run individual statements to update each row. We can update all of the rows in one fell swoop (assuming it's a reasonably sized table. For a larger table, to avoid humongous rollback/undo generation, we can perform the operation in reasonably sized chunks.)

我们不需要运行单独的语句来更新每一行。我们可以一举更新所有行（假设它是一个合理大小的表。对于一个更大的表，为了避免大量的回滚/撤消生成，我们可以在合理大小的块中执行操作。）

In the question, the AUTO_INCREMENTvalue shown for the table definition assures us that the number of rows is not excessive.

在问题中，AUTO_INCREMENT为表定义显示的值向我们保证行数不会过多。

If we've already changed the createdcolumn to allow for NULLvalues, we can do something like this:

如果我们已经更改了created列以允许NULL值，我们可以这样做：

UPDATE  `users` SET `created` = NULL WHERE `created` = '0000-00-00 00:00:00'

Or, we can set those to a valid date, e.g. January 2, 1970

或者，我们可以将它们设置为有效日期，例如 1970 年 1 月 2 日

UPDATE  `users` SET `created` = '1970-01-02' WHERE `created` = '0000-00-00 00:00:00'

(Note that a datetime value of midnight Jan 1, 1970 ('1970-01-01 00:00:00') isa "zero date". That will be evaluated to be '0000-00-00 00:00:00'

（请注意，1970 年 1 月 1 日午夜 ( '1970-01-01 00:00:00')的日期时间值是“零日期”。这将被评估为'0000-00-00 00:00:00'

回答by Tariq Khan

I got it fixed by doing this before the query

我在查询之前通过这样做修复了它

SET SQL_MODE='ALLOW_INVALID_DATES';

回答by Ivan Filho

According to MySQL 5.7 Reference Manual:

根据MySQL 5.7 参考手册：

The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

MySQL 5.7 中默认的 SQL 模式包括以下模式：ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER 和 NO_ENGINE_SUBSTITUTION。

Since 0000-00-00 00:00:00is not a valid DATETIMEvalue, your database is broken. That is why MySQL 5.7 – which comes with NO_ZERO_DATEmode enabled by default – outputs an error when you try to perform a write operation.

由于0000-00-00 00:00:00不是有效值DATETIME，您的数据库已损坏。这就是为什么 MySQL 5.7 NO_ZERO_DATE（默认启用模式）在您尝试执行写入操作时会输出错误的原因。

You can fix your table updating all invalid values to any other valid one, like NULL:

您可以修复您的表，将所有无效值更新为任何其他有效值，例如NULL：

UPDATE users SET created = NULL WHERE created < '0000-01-01 00:00:00'

Also, to avoid this problem, I recomend you always set current time as default value for your created-like fields, so they get automatically filled on INSERT. Just do:

此外，为避免此问题，我建议您始终将当前时间设置为类似created字段的默认值，以便它们自动填充INSERT. 做就是了：

ALTER TABLE users
ALTER created SET DEFAULT CURRENT_TIMESTAMP

回答by Shim-Sao

Here what my solution PhpMyAdmin / Fedora 29 / MySQL 8.0(for example):

这是我的解决方案PhpMyAdmin / Fedora 29 / MySQL 8.0（例如）：

set sql_mode='SOMETHING';doesn't work, command call successful but nothing was change.

set sql_mode='SOMETHING';不起作用，命令调用成功但没有任何变化。

set GLOBAL sql_mode='SOMETHING';change global configurationpermanent change.

set GLOBAL sql_mode='SOMETHING';更改全局配置永久更改。

set SESSION sql_mode='SOMETHING';change session configurationSESSION variable affects only the current client.

set SESSION sql_mode='SOMETHING';更改会话配置SESSION 变量仅影响当前客户端。

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

So I do this :

所以我这样做：

• Get SQL_MODE : SHOW VARIABLES LIKE 'sql_mode';
• Result : ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
• Remove on the result : NO_ZERO_IN_DATE,NO_ZERO_DATE
• Set new configuration : set GLOBAL SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

• 获取 SQL_MODE ： SHOW VARIABLES LIKE 'sql_mode';
• 结果 ： ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
• 删除结果： NO_ZERO_IN_DATE,NO_ZERO_DATE
• 设置新配置： set GLOBAL SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

You can remove or add other mode in the same way.

您可以以相同的方式删除或添加其他模式。

This is helpful to change global for using and testing frameworks or sql_mode must be specified in each file or bunch of queries.

这有助于更改全局使用和测试框架，或者必须在每个文件或一组查询中指定 sql_mode。

Adapted from a question ask here : how-can-i-disable-mysql-strict-mode

改编自一个问题在这里提问：how-can-i-disable-mysql-strict-mode

Example : install latest Joomla 4.0-alpha content.

示例：安装最新的 Joomla 4.0-alpha 内容。

Edit:In PhpMyadmin, if you have the control of the server, you can change the sql_mode(and all others parameters) directly in Plus > Variables > sql_mode

编辑：在 PhpMyadmin 中，如果您拥有服务器的控制权，则可以sql_mode直接在Plus > Variables > sql_mode

回答by Andrew March

SET sql_mode = 'NO_ZERO_DATE';
UPDATE `news` SET `d_stop`='2038-01-01 00:00:00' WHERE `d_stop`='0000-00-00 00:00:00'

回答by Abdallah Ajlouni

You can change the type of createdfield from datetimeto varchar(255), then you can set (update) all records that have the value "0000-00-00 00:00:00"to NULL.

您可以更改的类型创建从外地datetime来varchar(255)，那么你可以设置（更新）有值的所有记录"0000-00-00 00:00:00"到NULL。

Now, you can do your queries without error. After you finished, you can alter the type of the field createdto datetime.

现在，您可以毫无错误地进行查询。完成后，您可以将创建的字段类型更改为datetime。

回答by Don King

I have this error as well after upgrading MySQL from 5.6 to 5.7

将 MySQL 从 5.6 升级到 5.7 后，我也遇到此错误

I figured out that the best solution for me was to combine some of the solutions here and make something of it that worked with the minimum of input.

我发现对我来说最好的解决方案是结合这里的一些解决方案，并用最少的输入来制作一些东西。

I use MyPHPAdmin for the simplicity of sending the queries through the interface because then I can check the structure and all that easily. You might use ssh directly or some other interface. The method should be similar or same anyway.

我使用 MyPHPAdmin 是为了通过界面发送查询的简单性，因为这样我就可以轻松检查结构和所有这些。您可以直接使用 ssh 或其他一些接口。无论如何，该方法应该相似或相同。

...

...

1.

1.

First check out the actual error when trying to repair the db:

尝试修复数据库时首先检查实际错误：

joomla.jos_menu Note : TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.

Warning : Incorrect datetime value: '0000-00-00 00:00:00' for column 'checked_out_time' at row 1

Error : Invalid default value for 'checked_out_time'

status : Operation failed

joomla.jos_menu 注意：旧格式的 TIME/TIMESTAMP/DATETIME 列已升级为新格式。

警告：日期时间值不正确：第 1 行“checked_out_time”列的“0000-00-00 00:00:00”

错误：“checked_out_time”的默认值无效

状态：操作失败

This tells me the column checked_out_time in the table jos_menu needs to have all bad dates fixed as well as the "default" changed.

这告诉我 jos_menu 表中的 checked_out_time 列需要修复所有错误日期以及更改“默认”。

...

...

2.

2.

I run the SQL query based on the info in the error message:

我根据错误消息中的信息运行 SQL 查询：

UPDATE jos_menu SET checked_out_time = '1970-01-01 08:00:00' WHERE checked_out_time = 0

If you get an error you can use the below query instead that seems to always work:

如果出现错误，您可以使用以下查询，这似乎始终有效：

UPDATE jos_menu SET checked_out_time = '1970-01-01 08:00:00' WHERE CAST(checked_out_time AS CHAR(20)) = '0000-00-00 00:00:00'

...

...

3.

3.

Then once that is done I run the second SQL query:

完成后，我运行第二个 SQL 查询：

ALTER TABLE `jos_menu` CHANGE `checked_out_time` `checked_out_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP;

Or in the case it is a date that has to be NULL

或者在这种情况下，它是一个必须为 NULL 的日期

ALTER TABLE `jos_menu` CHANGE `checked_out_time` `checked_out_time` DATETIME NULL DEFAULT NULL;

...

...

If I run repair database now I get:

如果我现在运行修复数据库，我会得到：

joomla.jos_menu OK

joomla.jos_menu 确定

...

...

Works just fine :)

工作得很好:)

回答by commonpike

Check

查看

SELECT @@sql_mode;

if you see 'ZERO_DATE' stuff in there, try

如果你在那里看到“ZERO_DATE”的东西，试试

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

Log out and back in again to your client (this is strange) and try again

注销并重新登录到您的客户端（这很奇怪）并重试