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

2021-11-20 00:00:00 mysql

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

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

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

Among other things, I need to change the default character set from latin1 to 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

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

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.

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

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

我收到此错误:

  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

但我得到:

  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?

推荐答案

我的建议，如果是空表或不是很大的情况，将create语句导出为.sql文件，按照你的方式重写希望.如果您有任何现有数据，也请执行相同操作，即导出插入语句(我建议在单独的文件中执行此操作作为创建语句).最后删除表，先执行create语句再插入.

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.

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

You can use for that either mysqldump command, 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.

相关文章