#1062 - 尝试添加 UNIQUE KEY (MySQL) 时，键 'unique_id' 的重复条目 ''
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17823322/
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
#1062 - Duplicate entry '' for key 'unique_id' When Trying to add UNIQUE KEY (MySQL)
提问by MillerMedia
I've got an error on MySQL while trying to add a UNIQUE KEY. Here's what I'm trying to do. I've got a column called 'unique_id' which is VARCHAR(100). There are no indexes defined on the table. I'm getting this error:
在尝试添加唯一键时，我在 MySQL 上遇到错误。这就是我想要做的。我有一个名为“unique_id”的列，它是 VARCHAR(100)。表上没有定义索引。我收到此错误：
#1062 - Duplicate entry '' for key 'unique_id'
When I try to add a UNIQUE key. Here is a screenshot of how I'm setting it up in phpMyAdmin:
当我尝试添加 UNIQUE 键时。这是我如何在 phpMyAdmin 中设置它的屏幕截图：
Here is the MySQL query that's generate by phpMyAdmin:
这是由 phpMyAdmin 生成的 MySQL 查询：
ALTER TABLE `wind_archive` ADD `unique_id` VARCHAR( 100 ) NOT NULL FIRST ,
ADD UNIQUE (
`unique_id`
)
I've had this problem in the past and never resolved it so I just rebuilt the table from scratch. Unfortunately in this case I cannot do that as there are many entries in the table already. Thanks for your help!
我过去遇到过这个问题，但从未解决过，所以我只是从头开始重建了桌子。不幸的是，在这种情况下我不能这样做，因为表中已经有很多条目。谢谢你的帮助！
回答by Namphibian
The error says it all:
错误说明了一切：
Duplicate entry ''
So run the following query:
所以运行以下查询：
SELECT unique_id,COUNT(unique_id)
FROM yourtblname
GROUP BY unique_id
HAVING COUNT(unique_id) >1
This query will also show you the problem
此查询还将向您显示问题
SELECT *
FROM yourtblname
WHERE unique_id=''
This will show you where there are values that have duplicates. You are trying to create a unique index on a field with duplicates. You will need to resolve the duplicate data first then add the index.
这将显示哪里有重复的值。您正在尝试在具有重复项的字段上创建唯一索引。您需要先解决重复数据，然后添加索引。
回答by RQube
This is 3rd time i am looking for solution to this problem so for the reference I am posting the answer here.
这是我第三次寻找此问题的解决方案，因此我在此处发布答案以供参考。
Depending on the data we may use IGNORE keyword with Alter command. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key, The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.
根据数据，我们可能会在 Alter 命令中使用 IGNORE 关键字。如果指定了 IGNORE，则仅使用具有唯一键重复项的行的第一行，删除其他冲突行。不正确的值被截断为最接近匹配的可接受值。
The IGNOREkeyword extension to MySQL seems to have a bug in the InnoDB versionon some version of MySQL.
IGNOREMySQL的关键字扩展在某些版本的 MySQL 上的 InnoDB 版本中似乎有一个错误。
You could always, convert to MyISAM, IGNORE-ADD the index and then convert back to InnoDB
您总是可以转换为 MyISAM，忽略添加索引，然后转换回 InnoDB
ALTER TABLE table ENGINE MyISAM;
ALTER IGNORE TABLE table ADD UNIQUE INDEX dupidx (field);
ALTER TABLE table ENGINE InnoDB;
Note, if you have Foreign Key constraints this will not work, you will have to remove those first, and add them back later.
请注意，如果您有外键约束，这将不起作用，您必须先删除它们，然后再重新添加它们。
回答by Sudheer Naga Doguparthi
Make unique_idNULLfrom NOT NULLand it will solve your problem
Make unique_idNULLfromNOT NULL它将解决您的问题
回答by Ning Yu Fisher
select ID from wind_archive
where ID not in (select max(ID) from wind_archive group by unique_id)
and this is what you should remove from the table before you succesfully add the unique key. this also works for adding unique key with 2 or more columns. such as -
这是您在成功添加唯一键之前应该从表中删除的内容。这也适用于添加具有 2 列或更多列的唯一键。如 -
delete from wind_archive
where ID in (
select * from (select ID from wind_archive where ID not in (
select max(ID) from wind_archive group by lastName, firstName
) ORDER BY ID
) AS p
);
回答by Abolfazl Miadian
because of you write in your query, unique_id be NOT NULL and previous rows all of them are null and you want this column be unique, then after run this query, you have several rows with the same value it means this column is not unique, then you have to change unique_id NOT NULL to unique_id NULL in your query.
因为你在你的查询中写了，unique_id 是 NOT NULL 并且以前的行都是空的并且你希望这个列是唯一的，然后在运行这个查询之后，你有几行具有相同的值，这意味着这个列不是唯一的，那么您必须在查询中将 unique_id NOT NULL 更改为 unique_id NULL 。
回答by T. Bulford
I was getting the same error (Duplicate entry '' for key 'unique_id') when trying to add a new column as unique "after" I had already created a table containing just names of museums. I wanted to go back and add a unique code for each museum name, with the intention of inserting the code values one at a time. Poor table planning on my part. My solution was to add the new column without making it unique; then entered the data for each code one row at a time; and then changing the column structure to make it unique for future entries. Lucky there were only 10 rows.
当我尝试添加一个新列作为唯一的“之后”时，我遇到了同样的错误（重复条目 '' 键 'unique_id'） 我已经创建了一个只包含博物馆名称的表。我想回过头来为每个博物馆名称添加一个唯一的代码，目的是一次插入一个代码值。我的餐桌规划不佳。我的解决方案是添加新列而不使其唯一；然后一次一行输入每个代码的数据；然后更改列结构以使其在将来的条目中独一无二。幸运的是只有 10 行。
