MySQL -使现有字段唯一。

I have an already existing table with a field that should be unique but is not. I only know this because an entry was made into the table that had the same value as another, already existing, entry and this caused problems.

我有一个已经存在的表，其中的字段应该是唯一的，但不是。我之所以知道这一点，是因为在表中创建了一个条目，该条目的值与另一个已经存在的条目相同，这导致了问题。

How do I make this field only accept unique values?

如何使该字段只接受唯一值?

7 个解决方案

#1

ALTER IGNORE TABLE mytbl ADD UNIQUE (columnName);

---

For MySQL 5.7.4 or later:

MySQL 5.7.4或更高版本:

ALTER TABLE mytbl ADD UNIQUE (columnName);

As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error.

在MySQL 5.7.4中，删除了ALTER TABLE的忽略子句，它的使用产生了一个错误。

So, make sure to remove duplicate entries first as IGNORE keyword is no longer supported.

因此，确保在不再支持IGNORE关键字时首先删除重复项。

Reference

参考

#2

Just write this query in your db phpmyadmin.

只需在您的db phpadmin中编写此查询。

ALTER TABLE TableName ADD UNIQUE (FieldName)

Example.

的例子。

ALTER TABLE user ADD UNIQUE (email)

Thanks

谢谢

#3

If you want to also name the constraint, use this:

如果您还想命名约束，请使用以下命令:

ALTER TABLE myTable
  ADD CONSTRAINT constraintName 
    UNIQUE (columnName);

#4

CREATE UNIQUE INDEX foo ON table_name (field_name)

在table_name (field_name)上创建惟一的索引foo

(You have to remove duplicate values before that.)

(在此之前必须删除重复的值。)

#5

ALTER IGNORE TABLE mytbl ADD UNIQUE (columnName);

is the right answer

是正确的答案

the insert part

插入的部分

INSERT IGNORE INTO mytable ....

#6

The easiest and fastest way would be with phpmyadmin structure table.

最简单、最快的方法是使用phpadmin mystructure表。

There it's in Russian language but in English Version should be the same. Just click Unique button. Also from there you can make your columns PRIMARY or DELETE.

那里有俄语，但英文版本应该是一样的。点击的按钮。此外，您还可以使您的列成为主或删除。

#7

This code is to solve our problem to set unique key for existing table

这段代码是为了解决我们为现有表设置唯一键的问题

alter ignore table ioni_groups add unique (group_name);