MySQL不正确的日期时间值:"0000-00-00 00:00:00" [英] MySQL Incorrect datetime value: '0000-00-00 00:00:00'
问题描述
我最近接手了一个十年前创建的旧项目.它使用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来进行这项工作.不用考虑太多,我运行了"brew install mysql",它安装了MySQL 5.7.所以我有一些版本冲突.
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语句放在一个单独的文件中),则也要执行相同的操作.最后,删除表并首先执行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.
您可以使用MySQL安装中包含的mysqldump
命令,也可以安装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.
这篇关于MySQL不正确的日期时间值:"0000-00-00 00:00:00"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!