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.
-
If we are creating unique index on only a single column, all the rows in that column must be unique.
-
We cannot create a unique index where NULL values are present in multiple rows in a single column.
-
If we are creating unique index on multiple columns, the combination of rows in those columns must be unique.
-
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);
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 表并使用以下查询在名为 NAME 和 AGE 的列上创建唯一索引 -
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