错误的日期时间值:'0000-00-00 00:00:00'

我最近接手了一个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

我必须更新每个值吗?

当前回答

而不是

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

Use

UPDATE your_table SET your_column = new_valid_value where your_column = 0;

2020-06-18 07:36:37

其他回答

您可以将创建的字段的类型从datetime更改为varchar(255)，然后您可以将(更新)所有值为“0000-00-00 00:00:00”的记录设置为NULL。

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

2018-01-31 09:49:09

我的建议是，如果表是空的或者不是很大，那就把create语句导出为一个.sql文件，然后按照你的意愿重写它们。如果你有任何现有的数据，也做同样的事情，即导出插入语句(我建议在一个单独的文件中作为创建语句)。最后，删除表并执行第一个create语句，然后执行insert语句。

你可以使用mysqldump命令，包括在你的MySQL安装中，或者你也可以安装MySQL工作台，这是一个免费的图形工具，它也包括这个选项，以一种非常自定义的方式，而不必寻找特定的命令选项。

2016-02-22 22:36:41

这就是我解决问题的方法。我在本地MySQL 5.7 ubuntu 18.04中进行了测试。

set global sql_mode="NO_ENGINE_SUBSTITUTION";

在全局运行这个查询之前，我在/etc/mysql/conf中添加了一个cnf文件。d目录。cnf文件的名称是mysql.cnf和代码

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

然后重启mysql

sudo service mysql restart

希望这能帮助到一些人。

2019-07-16 05:08:41

对于Symfony用户:添加一个默认的orm列，如下所示:

/**
 * @var DateTime $updatedAt
 *
 * @ORM\Column(name="updated_at", type="datetime", nullable=false, options={"default" : "CURRENT_TIMESTAMP"})
 */

2021-02-03 10:08:00

我在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。我通过这样做去掉了它:

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并更改我的表。

2020-04-17 02:05:32

错误的日期时间值:'0000-00-00 00:00:00'

推荐文章

最新文章

标签