MySQL日期时间格式错误：'0000-00-00 00:00:00'。

Question

MySQL日期时间格式错误：'0000-00-00 00:00:00'。

257

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

除其他事项外，我需要将默认字符集从latin1更改为utf8。

例如，我有这样的表：

  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

我在我的 Mac 上进行了设置以便开展工作。没有太多考虑，我运行了 "brew install mysql" 命令，安装了 MySQL 5.7。因此我有一些版本冲突。

我下载了该数据库的一个副本并进行了导入。

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

  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

我以为我可以用以下方法解决这个问题：

  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

但我得到的是：

  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

我必须更新每个值吗？

- lorm

被接受的答案已经过时，请查看 @TariqKhan 在下面针对 mysql 8.0 的回答。 - rubo77

谢谢你在我之前询问 :) - Tom

26个回答

215

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

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

该操作不会更改已存储的任何值。"默认"值适用于插入的行，且未为列提供值。

至于为什么会遇到错误，很可能是您的会话的 sql_mode 设置包括NO_ZERO_DATE。

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

当您进行 "import" 时，运行了 INSERT 命令将数据插入到该表中，而该会话允许使用零日期。

查看 sql_mode 设置的方法：

SHOW VARIABLES LIKE 'sql_mode' ;

-或-

SELECT @@sql_mode ;

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

几个选项：

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

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

-- 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) 更改 created 列允许为 NULL 值，并更新现有行将零日期更改为 NULL 值。

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

我们不需要运行单个语句来更新每一行。我们可以一次性更新所有的行（假设表的大小是合理的）。对于更大的表，为了避免巨大的回滚/撤销生成，我们可以分批次执行操作。

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

如果我们已经更改了 created 列以允许为 NULL 值，我们可以执行以下操作：

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

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

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

注意，1970年1月1日午夜的日期时间值（'1970-01-01 00:00:00'）实际上是一个“零日期”。这将被计算为'0000-00-00 00:00:00'。

- spencer7593

3

没问题，这对我有效。我在my.ini文件中搜索了sql-mode并删除了NO_ZERO_IN_DATE和NO_ZERO_DATE，然后重新启动了服务。谢谢Spencer7593！ - mili

设置NO_ZERO_DATE：https://dev59.com/questions/3W865IYBdhLWcg3wSMgw - user 1007017

1

@MikeWeir：假设“不让我”意味着在执行SQL语句时返回了一个错误。这很可能是由于sql_mode的设置。MySQL的较新版本具有更严格的默认sql_mode设置，与以前的版本不同。8.0版本的参考文献在此处：https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html，请查看`NO_ZERO_DATE`、`ALLOW_INVALID_DATE`等内容。请注意，某些内容包含在STRICT模式中，例如`STRICT_TRANS_TABLES`、`STRICT_ALL_TABLES`和其他组合模式。为了解决限制问题，可以暂时修改会话的`sql_mode`。 - spencer7593

我认为几乎没有情况需要将日期字段设置为 NOT NULL 并默认为 '1970-01-02'。真的有这样的情况吗？如果您还没有有效的日期值，则应将其设置为 NULL，直到您有了实际日期。这也使查询更加合理。例如，where occurence is null 要比 where occurence = '1970-01-02' 更明显。 - David Baucum

@DavidBaucum：这个答案中的第二个选项建议允许该列使用NULL值；我们可以暂时调整sql_mode以允许零日期，然后进行UPDATE操作将0000-00-00替换为NULL。 - spencer7593

显示剩余7条评论

186

在查询之前，我通过执行以下操作来解决了它。

SET SQL_MODE='ALLOW_INVALID_DATES';

- Tariq Khan

1

这是唯一的答案。用作：--init-command='SET SESSION FOREIGN_KEY_CHECKS=0;SET SQL_MODE='ALLOW_INVALID_DATES' - Konchog

2

这在 MySQL 8.0 中是必需的，还需要从/etc/mysql/conf.d/内相应的文件中添加此内容并移除 NO_ZERO_IN_DATE和NO_ZERO_DATE的 SQL 模式。 - rubo77

我想再点击“上”10次！ - Harun Baris Bulut

3

是的！救命之策。我是这样使用的：

mysql -u 用户名 -p 数据库名 --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;SET SQL_MODE='ALLOW_INVALID_DATES';" < 文件名.sql

- John Skiles Skinner

1

不要执行 SET FOREIGN_KEY_CHECKS=0！你不需要这样做来解决日期问题，除非你知道自己在做什么，否则真的不应该这样做。禁用外键将导致数据完整性错误。 - Cfreak

给所有MySQL新手：这个命令会覆盖您当前的SQL_MODE设置，并可能给您带来一些严重的麻烦。 - Wolfgang Blessen

57

根据MySQL 5.7参考手册：

默认的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。

由于0000-00-00 00:00:00不是有效的DATETIME值，因此您的数据库出现了故障。这就是为什么MySQL 5.7默认启用了NO_ZERO_DATE模式，当您尝试执行写操作时会输出错误。

您可以通过将所有无效值更新为任何其他有效值（如NULL）来修复表格：

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

此外，为了避免这个问题，我建议您始终将当前时间设置为created等字段的默认值，以便在INSERT时自动填充。只需执行以下操作：

ALTER TABLE users
ALTER created SET DEFAULT CURRENT_TIMESTAMP

- Ivan Filho

33

相比之下

UPDATE your_table SET your_column = new_valid_value where your_column = '0000-00-00 00:00:00';

使用

UPDATE your_table SET your_column = new_valid_value where your_column = 0;

- Reynier

3

运作得非常好。在我看来，这个应该被标记为答案。谢谢！ - Michael

1

这个救了我的一天。谢谢！ - mixable

20

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

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

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

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

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

因此我这样做：

获取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'

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

这对于更改全局以使用和测试框架或在每个文件或一堆查询中指定sql_mode非常有帮助。

源问题来自：如何禁用MySQL的严格模式

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

编辑： 在PhpMyAdmin中，如果您控制服务器，可以直接在Plus > Variables > sql_mode中更改sql_mode（以及其他所有参数）。

- Shim-Sao

1

如果在重新启动Ubuntu机器时需要重置此内容，请将其添加到/etc/mysql/conf.d中。加1感谢。 - Bhavin Rana

14

我在https://support.plesk.com/hc/en-us/articles/115000666509-How-to-change-the-SQL-mode-in-MySQL找到了解决方案。我之前遇到了这个问题：

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | 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 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

注意上面结果中的NO_ZERO_IN_DATE,NO_ZERO_DATE。我通过以下方式去除了它：

注意上面结果中的NO_ZERO_IN_DATE,NO_ZERO_DATE。我通过以下方法将其移除：

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

接着我有了这个:

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                        |
+---------------+--------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

在这样做之后，我可以成功使用 ALTER TABLE 命令来修改我的数据表。

- Jaime Montoya

1

在程序运行时：SET sql_mode=(SELECT REPLACE(@@sql_mode, 'NO_ZERO_IN_DATE', '')); 并且第二个选项也是一样的。 - djdance

同时添加 ALLOW_INVALID_DATES。 - Frik

10

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'

- Andrew March

4

本回答需要讨论为什么这个解决方案能够解决问题。翻译：需要讨论此解决方案的原因。 - KevinO

这会影响日期时间的存储吗？或者会引起任何问题吗？ - Ask Xah

10

检查

SELECT @@sql_mode;

如果您看到其中的“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',''));

注销并重新登录您的客户端（这很奇怪），然后再试一遍。

- commonpike

9

如果表是空的或者不是非常非常大，我的建议是将创建语句导出为.sql文件并重新编写。如果有任何现有数据，请重复上述步骤，即导出插入语句（建议将其放在单独的文件中，而不是与创建语句混在一起）。最后，删除该表并执行首先创建语句，然后插入语句。

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

- Lucia Pasarin

Lucia Pasarin，我非常喜欢你的想法，但是数据会被截断吗？一些UTF8数据占用的字节数比latin1多吗？如果以前适合于varchar 255，现在可能就不行了吗？也许我应该将所有的varchar字段改为“text”字段？ - lorm

是的，你说得对。这种情况可能会发生，因为latin1每个字符使用1个字节，而utf8每个字符最多使用4个字节（取决于MySQL版本和utf8类型http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8.html）。因此，您不一定需要TEXT类型。我会假设您之前存在的大小的4倍可以正常工作。 - Lucia Pasarin

2

这就相当于想要删除一个文件时重新格式化硬盘的数据库等效操作。可以肯定地说，在生产环境中，这种解决方案是不可接受的。 - Brandon

1

UPDATE users SET created = NULL WHERE created=0; 将用户表中创建时间为0的记录的创建时间设置为NULL。 - Datbates

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- obe · Accepted Answer

我无法做到这一点：

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

（在MySQL 5.7.13上）。

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

奇怪的是，这个语句却可以正常工作：SELECT * FROM users WHERE created = '0000-00-00 00:00:00'。我不知道为什么前者会失败而后者可以正常工作......也许是MySQL的一个Bug？

无论如何，以下UPDATE语句可以正常运行：

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