导入大文件时MySQL奇怪的问题：密钥'PRIMARY'的重复条目 [英] MySQL weird issue while importing a large file: Duplicate entry for key 'PRIMARY'

问题描述

我的托管公司希望我从MySQL 4服务器迁移到MySQL 5.5服务器。
我的数据库相对较大，因为我的网站托管了近200,000多注册用户的数据。

My hosting company wants me to move from a MySQL 4 server to a MySQL 5.5 one. My database is relatively big as my website hosts the data of nearly 200,000+ registered users.

我使用PHPMyAdmin导出了所有表格，而我是现在尝试将它们导入新服务器。一切顺利，直到我尝试导入'用户'表。有关信息，这是它的结构：

I made an export of all my tables using PHPMyAdmin and I am now trying to import them into the new server. Everything went fine until I tried to import the 'user' table. For information, here is its structure:

CREATE TABLE IF NOT EXISTS `user` (
`login` varchar(32) NOT NULL DEFAULT '',
`firstname` varchar(255) NOT NULL DEFAULT '',
`lastname` varchar(255) NOT NULL DEFAULT '',
`email` varchar(255) NOT NULL DEFAULT '',
...
PRIMARY KEY (`login`),
KEY `country_code` (`country_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

没什么了不起的，对吧？使用PHPMyAdmin导入选项卡，我上传了SQL文件。突然间，我收到了以下错误：

Nothing spectacular, right? Using the PHPMyAdmin import tab, I uploaded the SQL file. Suddenly, I got the following error:

#1062 - Duplicate entry 'Jobi' for key 'PRIMARY' 

我立即查询数据库并搜索登录为Jobi的用户名。不匹配！

I immediately queried the database and search for a username whom login was 'Jobi'. No match!

我查看了产生错误的查询，并尝试手动执行...

I had a look at the query which generated the error and tried to do it manually...

INSERT INTO `ft_user`
  (`login`, `firstname`, `lastname`, `email`, ...)
VALUES
  ('Jobi', 'Lorem-First', 'Ipsum-Last', '[e-mail]', ...);

它运作得很好！

我在我的开发机器上进行了测试并得到了同样的错误。
有人可以解释一下我在这里做错了吗？

I made the test on my development machine and got the same error. Could someone explain me what I am doing wrong here?

提前谢谢你。任何帮助都非常感谢。

Thank you in advance. Any help is really appreciated.

Hervé。

推荐答案

如果输入文件包含具有相同主键值的记录的两个副本，您可以获得此错误，但是当事务回滚时该键将不再存在。检查输入文件是否有重复项。

If the input file contains two copies of a record with the same primary key value you could get this error, but then when the transaction gets rolled back that key would no longer exist. Check your input file for duplicates.

这篇关于导入大文件时MySQL奇怪的问题：密钥'PRIMARY'的重复条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！