MySQL MySQL中键、主键、唯一键和索引的区别

Difference between Key, Primary Key, Unique Key and Index in MySQL

提问by HELP

When should I use KEY, PRIMARY KEY, UNIQUE KEYand INDEX?

我什么时候应该使用KEY, PRIMARY KEY,UNIQUE KEY和INDEX？

回答by Daniel Vassallo

KEYand INDEXare synonyms in MySQL. They mean the same thing. In databases you would use indexesto improve the speed of data retrieval. An index is typically created on columns used in JOIN, WHERE, and ORDER BYclauses.

KEY和INDEX是 MySQL 中的同义词。他们的意思是一样的。在数据库中，您将使用索引来提高数据检索的速度。的索引在使用柱通常创建JOIN，WHERE和ORDER BY条款。

Imagine you have a table called usersand you want to search for all the users which have the last name 'Smith'. Without an index, the database would have to go through all the records of the table: this is slow, because the more records you have in your database, the more work it has to do to find the result. On the other hand, an index will help the database skip quickly to the relevant pages where the 'Smith' records are held. This is very similar to how we, humans, go through a phone book directory to find someone by the last name: We don't start searching through the directory from cover to cover, as long we inserted the information in some order that we can use to skip quickly to the 'S' pages.

假设您有一个名为users“Smith”的表，并且您想要搜索所有姓氏为“Smith”的用户。如果没有索引，数据库将不得不遍历表的所有记录：这很慢，因为数据库中的记录越多，查找结果所需的工作就越多。另一方面，索引将帮助数据库快速跳转到保存“史密斯”记录的相关页面。这与我们人类通过电话簿目录查找姓氏的方式非常相似：我们不会从一个封面到另一个封面开始搜索目录，只要我们按某种顺序插入信息，我们就可以用于快速跳转到“S”页。

Primary keys and unique keys are similar. A primary key is a column, or a combination of columns, that can uniquely identify a row. It is a special case of unique key. A table can have at most one primary key, but more than one unique key. When you specify a unique key on a column, no two distinct rows in a table can have the same value.

主键和唯一键类似。主键是可以唯一标识行的一列或多列组合。它是唯一键的特例。一张表最多可以有一个主键，但可以有多个唯一键。当您在列上指定唯一键时，表中任何两个不同的行都不能具有相同的值。

Also note that columns defined as primary keys or unique keys are automatically indexed in MySQL.

另请注意，定义为主键或唯一键的列会在 MySQL 中自动编入索引。

回答by Mark Byers

KEY and INDEX are synonyms.

KEY 和 INDEX 是同义词。

You should add an index when performance measurements and EXPLAIN shows you that the query is inefficient because of a missing index. Adding an index can improve the performance of queries (but it can slow down modifications to the table).

当性能测量和 EXPLAIN 显示查询由于缺少索引而效率低下时，您应该添加索引。添加索引可以提高查询的性能（但会减慢对表的修改速度）。

You should use UNIQUE when you want to contrain the values in that column (or columns) to be unique, so that attempts to insert duplicate values result in an error.

当您希望将该列（或多列）中的值限制为唯一时，您应该使用 UNIQUE，以便尝试插入重复值会导致错误。

A PRIMARY KEY is both a unique constraint and it also implies that the column is NOT NULL. It is used to give an identity to each row. This can be useful for joining with another table via a foreign key constraint. While it is not required for a table to have a PRIMARY KEY it is usually a good idea.

PRIMARY KEY 既是唯一约束，也暗示该列不是 NULL。它用于为每一行提供一个身份。这对于通过外键约束与另一个表连接很有用。虽然表不需要具有 PRIMARY KEY，但它通常是一个好主意。

回答by Er.Gaurav singh

Primary keydoes not allow NULLvalues, but unique keyallows NULLvalues.

主键不允许NULL值，但唯一键允许NULL值。

We can declare only oneprimary keyin a table, but a table can have multipleunique keys(column assign).

我们只能在一张表中声明一个主键，但一张表可以有多个唯一键（列分配）。

回答by Buhake Sindi

PRIMARY KEYAND UNIQUE KEYare similar except it has different functions. Primary key makes the table row unique (i.e, there cannot be 2 row with the exact same key). You can only have 1 primary key in a database table.

PRIMARY KEYANDUNIQUE KEY是相似的，只是它有不同的功能。主键使表行唯一（即，不能有 2 行具有完全相同的键）。一个数据库表中只能有 1 个主键。

Unique key makes the table column in a table row unique (i.e., no 2 table row may have the same exact value). You can have more than 1 unique key table column (unlike primary key which means only 1 table column in the table is unique).

唯一键使表行中的表列唯一（即，没有 2 个表行可能具有完全相同的值）。您可以拥有 1 个以上的唯一键表列（与主键不同，这意味着表中只有 1 个表列是唯一的）。

INDEXalso creates uniqueness. MySQL (example) will create a indexing table for the column that is indexed. This way, it's easier to retrieve the table row value when the query is queried on that indexed table column. The disadvantage is that if you do many updating/deleting/create, MySQL has to manage the indexing tables (and that can be a performance bottleneck).

INDEX也创造了独特性。MySQL（示例）将为被索引的列创建一个索引表。这样，在该索引表列上查询查询时，可以更轻松地检索表行值。缺点是如果你做很多更新/删除/创建，MySQL 必须管理索引表（这可能是一个性能瓶颈）。

Hope this helps.

希望这可以帮助。

回答by Ashwini Dhekane

Unique Keys: The columns in which no two rows are similar

唯一键：没有两行相似的列

Primary Key: Collection of minimum number of columns which can uniquely identify every row in a table (i.e. no two rows are similar in all the columns constituting primary key). There can be more than one primary key in a table. If there exists a unique-key then it is primary key (not "the" primary key) in the table. If there does not exist a unique key then more than one column values will be required to identify a row like (first_name, last_name, father_name, mother_name) can in some tables constitute primary key.

主键：可以唯一标识表中每一行的最小列数的集合（即构成主键的所有列中没有两行相似）。一张表中可以有多个主键。如果存在唯一键，则它是表中的主键（不是“主键”）。如果不存在唯一键，那么将需要多个列值来标识行，例如 (first_name, last_name,father_name,mother_name) 可以在某些表中构成主键。

Index: used to optimize the queries. If you are going to search or sort the results on basis of some column many times (eg. mostly people are going to search the students by name and not by their roll no.) then it can be optimized if the column values are all "indexed" for example with a binary tree algorithm.

索引：用于优化查询。如果您要根据某个列多次搜索或排序结果（例如，大多数人将按姓名而不是他们的卷号搜索学生），那么如果列值全部为“索引”，例如使用二叉树算法。

回答by VIKASH

Primary key- we can put only one primary key on a table into a table and we can not left that column blank when we are entering the values into the table.

主键- 我们只能将表上的一个主键放入表中，并且在将值输入表中时不能将该列留空。

Unique Key- we can put more than one unique key on a table and we may left that column blank when we are entering the values into the table. column take unique values (not same) when we applied primary & unique key.

唯一键- 我们可以在一张表上放置多个唯一键，当我们将值输入到表中时，我们可以将该列留空。当我们应用主键和唯一键时，列采用唯一值（不相同）。

回答by irudyak

The primary key is used to work with different tables. This is the foundation of relational databases. If you have a book database it's better to create 2 tables - 1) books and 2) authors with INT primary key "id". Then you use id in books instead of authors name.

主键用于处理不同的表。这是关系数据库的基础。如果您有图书数据库，最好创建 2 个表 - 1）图书和 2）具有 INT 主键“id”的作者。然后你在书中使用 id 而不是作者姓名。

The unique key is used if you don't want to have repeated entries. For example you may have title in your book table and want to be sure there is only one entry for each title.

如果您不想重复输入，则使用唯一键。例如，您的图书表中可能有标题，并希望确保每个标题只有一个条目。

回答by Pritam Banerjee

Unique Key :

唯一键：

1. More than one value can be null.
2. No two tuples can have same values in unique key.
3. One or more unique keys can be combined to form a primary key, but not vice versa.

1. 多个值可以为空。
2. 任何两个元组都不能在唯一键中具有相同的值。
3. 一个或多个唯一键可以组合成一个主键，但反之则不然。

Primary Key

首要的关键

1. Can contain more than one unique keys.
2. Uniquely represents a tuple.

1. 可以包含多个唯一键。
2. 唯一地代表一个元组。