mysql设置唯一键1062错误,＃1062-尝试添加唯一键（MySQL）时，键'unique_id'的条目重复...

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:

#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:

Here is the MySQL query that's generate by phpMyAdmin:

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!

解决方案

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.