MySQL 错误 - #1062 - 键 2 的重复条目 ' '
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6153552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL error - #1062 - Duplicate entry ' ' for key 2
提问by nutman
I'm trying to insert a huge list of users to a MySQL database but everytime I try I get the error:
我正在尝试将大量用户插入 MySQL 数据库，但每次尝试时都会出现错误：
#1062 - Duplicate entry '' for key 2
It gives me this because the 2nd column is blank on quite a lot of the entries, so after it's inserted one blank entry in column 2, it won't let me add another. However, when I added most of the list yesterday I didn't get this error once even though a lot of the entries I added yesterday have a blank cell in column 2 as well. Whats going on?
它给了我这个，因为第二列在很多条目上都是空白的，所以在第二列中插入一个空白条目后，它不会让我添加另一个。但是，当我昨天添加列表的大部分内容时，即使我昨天添加的许多条目在第 2 列中也有一个空白单元格，我也没有收到此错误。这是怎么回事？
This is the sql code to insert 1 entry. The rest follow the same format:
这是插入 1 个条目的 sql 代码。其余的遵循相同的格式：
INSERT INTO users
(`id`,`title`,`firstname`,`lastname`,`company`,`address`,`city`,`county`
,`postcode`,`phone`,`mobile`,`category`,`email`,`password`,`userlevel`)
VALUES
('','','John','Doe','company','Streeet','city','county'
,'postcode','phone','','category','[email protected]','','');
采纳答案by Johan
In addition to Sabeen's answer:
除了 Sabeen 的回答：
The first column id is your primary key.
Don't insert ''into the primary key, but insert null instead.
第一列 id 是您的主键。
不要插入''主键，而是插入空值。
INSERT INTO users
(`id`,`title`,`firstname`,`lastname`,`company`,`address`,`city`,`county`
,`postcode`,`phone`,`mobile`,`category`,`email`,`password`,`userlevel`)
VALUES
(null,'','John','Doe','company','Streeet','city','county'
,'postcode','phone','','category','[email protected]','','');
If it's an autoincrement key this will fix your problem.
If not make idan autoincrement key, and always insert nullinto it to trigger an autoincrement.
如果它是自动增量键，这将解决您的问题。
如果没有创建id自动增量键，并始终插入null其中以触发自动增量。
MySQL has a setting to autoincrement keys only on nullinsert or on both inserts of 0and null. Don't count on this setting, because your code may break if you change server.
If you insert nullyour code will always work.
MySQL 设置为仅在null插入或同时插入0和 时自动递增键null。不要指望此设置，因为如果更改服务器，您的代码可能会中断。
如果您插入null您的代码将始终有效。
See: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
请参阅：http: //dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
回答by Sabeen Malik
Seems like the second column is set as a unique index. If you dont need that remove it and your errors will go away. Possibly you added the index by mistake and thats why you are seeing the errors today and werent seeing them yesterday
似乎第二列被设置为唯一索引。如果您不需要删除它，您的错误就会消失。可能是您错误地添加了索引，这就是您今天看到错误而昨天没有看到的原因
回答by ajon
I got this error when I tried to set a column as unique when there was already duplicate data in the column OR if you try to add a column and set it as unique when there is already data in the table.
当我尝试在列中已经存在重复数据的情况下将列设置为唯一时，或者如果您尝试添加一列并将其设置为唯一时（表中已有数据），则会出现此错误。
I had a table with 5 rows and I tried to add a unique column and it failed because all 5 of those rows would be empty and thus not unique.
我有一个有 5 行的表，我试图添加一个唯一的列，但它失败了，因为所有 5 行都是空的，因此不是唯一的。
I created the column without the unique index set, then populated the data then set it as unique and everything worked.
我创建了没有唯一索引集的列，然后填充数据，然后将其设置为唯一，一切正常。
回答by Ashish Karpe
Error 'Duplicate entry '338620-7' for key 2' on query. Default database
查询时出现错误 '重复条目 '338620-7' 键 2'。默认数据库
For this error :
对于这个错误：
set global sql_slave_skip_counter=1;
start slave;
show slave status\G
This worked for me
这对我有用
回答by Sankar Bordoloi
you can try adding
你可以尝试添加
$db['db_debug'] = FALSE;
in "your database file".php after that you can modify your database as you like.
在“您的数据库文件”.php 之后，您可以根据需要修改您的数据库。
回答by a1ex07
As it was said you have a unique index.
据说你有一个唯一的索引。
However, when I added most of the list yesterday I didn't get this error once even though a lot of the entries I added yesterday have a blank cell in column 2 as well. Whats going on?
但是，当我昨天添加列表的大部分内容时，即使我昨天添加的许多条目在第 2 列中也有一个空白单元格，我也没有收到此错误。这是怎么回事？
That means that all these entries contain value NULL, not empty string ''. Mysql lets you have multiple NULLvalues in unique fields.
这意味着所有这些条目都包含 value NULL，而不是空字符串''。Mysql 允许您NULL在唯一字段中有多个值。
回答by Jasmeen
Check out your table index. If that field got index (e.g. B+ tree index.) At that time update or insert query throws these kinds of error.
检查您的表索引。如果该字段获得索引（例如 B+ 树索引）。此时更新或插入查询会引发此类错误。
Remove indexing and then try to fire same query.
删除索引，然后尝试触发相同的查询。
回答by gomozor
Drop the primary key first: (The primary key is your responsibility)
ALTER TABLE Persons DROP PRIMARY KEY ;Then make all insertions:
Add new primary key just like before dropping:
ALTER TABLE Persons ADD PRIMARY KEY (P_Id);
先去掉主键：（主键是你的责任）
ALTER TABLE Persons DROP PRIMARY KEY ;然后进行所有插入：
像删除之前一样添加新的主键：
ALTER TABLE Persons ADD PRIMARY KEY (P_Id);
