Mysql 简明教程

MySQL - Unique Index

MySQL 索引用于从数据库快速返回数据。用户看不到执行的索引,相反,它们仅用于加速查询。

MySQL Indexes are used to return the data from the database real quick. The users cannot see the indexes performing, instead they are just used to speed up the queries.

但是,唯一索引除了加速数据检索查询外,还用于维护表中的数据完整性。当在表列上定义唯一索引时,我们不能向该列中添加任何重复值。

However a unique index, in addition to speeding up data retrieval queries, is also used to maintain data integrity in a table. When a unique index is defined on a table column, we cannot add any duplicate values into that column.

MySQL Unique Index

可以使用 MySQL 中的 CREATE UNIQUE INDEX 语句在表的一个或多个列上创建一个唯一索引。

A unique index can be created on one or more columns of a table using the CREATE UNIQUE INDEX statement in MySQL.

  1. If we are creating unique index on only a single column, all the rows in that column must be unique.

  2. We cannot create a unique index where NULL values are present in multiple rows in a single column.

  3. If we are creating unique index on multiple columns, the combination of rows in those columns must be unique.

  4. We cannot create a unique index on multiple columns if the combination of columns contains NULL values in more than one row.

Syntax

以下是 MySQL 中创建唯一索引的语法 -

Following is the syntax for creating a unique index in MySQL −

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ..., columnN);

Example

让我们首先使用以下查询创建一个名为 CUSTOMERS 的表 -

Let us first create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 2),
   PRIMARY KEY(ID)
);

在以下查询中,我们使用 INSERT 语句向上面创建的表中插入一些值 -

In the following query, we are inserting some values in to the above created table using the INSERT statement −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', '32', 'Ahmedabad', 2000),
(2, 'Khilan', '25', 'Delhi', 1500),
(3, 'Kaushik', '23', 'Kota', 2500),
(4, 'Chaitali', '26', 'Mumbai', 6500),
(5, 'Hardik','27', 'Bhopal', 8500),
(6, 'Komal', '22', 'MP', 9000),
(7, 'Muffy', '24', 'Indore', 5500);

该表将按如下方式创建:

The table will be created as follows −

现在,使用以下查询为 CUSTOMERS 表中名为 SALARY 的列创建唯一索引 -

Now, create a unique index for the column named SALARY in the CUSTOMERS table using the following query −

CREATE UNIQUE INDEX unique_ind ON CUSTOMERS (SALARY);

Inserting Duplicate Values

现在,让我们尝试使用以下查询更新 SALARY 列中的值为重复值(已存在的数据) -

Now, let us try to update the value in the SALARY column with a duplicate (already existing data) value using the following query −

UPDATE CUSTOMERS SET SALARY = 2000 WHERE ID = 2;

Error

上面的查询导致错误,因为具有唯一索引的列不能包含重复值。

The above query results in an error because a column that has unique index cannot contain duplicate values in it.

ERROR 1062 (23000): Duplicate entry '2000.00' for key 'customers.unique_ind'

Creating Unique Index on Multiple Columns

在 MySQL 中,我们还可以使用 CREATE UNIQUE INDEX 语句在表的多个列上创建唯一索引。要这样做,您只需将列的名称(您需要为其创建索引)传递给查询。

In MySQL, we can also create a unique index on multiple columns of a table using the CREATE UNIQUE INDEX statement. To do so, you just need to pass the name of the columns (you need to create the index on) to the query.

Example

假设先前创建的 CUSTOMERS 表并使用以下查询在名为 NAMEAGE 的列上创建唯一索引 -

Assume the previously created CUSTOMERS table and create a unique index on the columns named NAME and AGE using the following query −

CREATE UNIQUE INDEX mul_unique_index ON CUSTOMERS(NAME, AGE);

Verification

使用以下查询,我们可以列出在 CUSTOMERS 表上创建的所有索引 -

Using the following query, we can list all the indexes that are created on the CUSTOMERS table −

SHOW INDEX FROM CUSTOMERS\G

索引信息表显示为−

The table of index information is displayed as −

*************************** 1. row ***********************
        Table: customers
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***********************
        Table: customers
   Non_unique: 0
     Key_name: mul_unique_index
 Seq_in_index: 1
  Column_name: NAME
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 3. row ***********************
        Table: customers
   Non_unique: 0
     Key_name: mul_unique_index
 Seq_in_index: 2
  Column_name: AGE
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 4. row ***********************
        Table: customers
   Non_unique: 0
     Key_name: unique_ind
 Seq_in_index: 1
  Column_name: SALARY
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

Creating Unique Index Using a Client Program

除了使用 MySQL 查询创建索引之外,还可以使用客户端程序创建唯一索引。

In addition to creating an index using a MySQL query, we can also create the unique index using a client program.

Syntax

Example

以下是这些程序 −

Following are the programs −