MySQL 唯一索引

我们在设计数据库和表的时候，表中的主键通常为一个自增的数字列。主键列肯定是唯一的。有时候，我们的表中除主键之外的其他列也需要具有唯一值，比如订单表中的订单编号，用户表中的电子邮件和手机号等。我们可以将这些列定义为唯一索引，以保证这些列中不能出现重复值。

MySQL 唯一索引是一种特殊的索引，它不但可以加快从表中检索数据的速度，还能防止在指定的一个或多个列中出现重复值。

MySQL 允许您使用 CREATE UNIQUE INDEX 语句来创建唯一索引。

MySQL 唯一索引语法

要创建唯一索引，请使用 CREATE UNIQUE INDEX 如下语句：

CREATE UNIQUE INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_list)
[algorithm_option | lock_option];

这里：

UNIQUE 关键字表明此索引为唯一索引。
index_name 是索引的名字。一个表中不应该出现两个相同名字的索引。
table_name 是表的名字。
column_list 是表中的列名。多个列名使用逗号分隔。

除了使用上面的语法，您可以通过下面的方式在建表的时候将一个列或多个列指定为唯一键：

定义一列为唯一键

CREATE TABLE table_name(
    ...,
    column_name data_type UNIQUE,
    ...
);

如果唯一键包含多个列，请使用如下语法：

CREATE TABLE table_name(
column_name1 column_definition,
column_name2 column_definition,
...,
[CONSTRAINT constraint_name]
    UNIQUE(column_name1,column_name2)
);

要获得更详细的信息，请跳转到 MySQL 唯一键页面。

注意：在 MySQL 中，唯一键和唯一索引的意思相同。因为关键字 INDEX 和 KEY 具有相同的含义。

MySQL 唯一索引实例

以下示例设计了一个面向企业租户的 SAAS 系统中的用户系统的解决方案。我们将设计两个表：

租户表 tenant。此表具有三列：主键(id), 租户名称(name) 和租户电子邮箱(email)。
租户用户表 tenant_user。此表具有四列：主键(id), 租户 ID(tenant_id), 用户登录名(username) 和用户邮箱(email)。

其中，租户表中的租户电子邮件列要具有唯一值，租户用户表中用户登录名要在一个企业内唯一。

注意，这里只是简单的设计，实际的用户方案要复杂的多。

请按照如下的步骤执行此实例：

使用 mysql 客户端工具连接到 MySQL 服务器：
```
mysql -u root -p
```
输入 root 帐户的密码并按 Enter ：
```
Enter password: ********
```

创建测试数据库并选择此数据库

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

创建租户表

CREATE TABLE IF NOT EXISTS tenant (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
);

在租户表的 email 列创建唯一索引

CREATE UNIQUE INDEX idx_tenant_email ON tenant(email);

让我们使用 SHOW INDEXES 语句查看表 tenant 上的索引：

SHOW INDEXES FROM tenant;

+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tenant |          0 | PRIMARY          |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tenant |          0 | idx_tenant_email |            1 | email       | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

创建租户用户表

CREATE TABLE IF NOT EXISTS tenant_user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    nickname VARCHAR(50) NOT NULL
);

在租户用户表的 tenant_id 列和 username 列上创建唯一索引

CREATE UNIQUE INDEX idx_tenant_username
ON tenant_user(tenant_id, username);

这里，我们创建了一个组合唯一索引，它保证了 tenant_id 列和 username 列组合值唯一。

让我们使用 SHOW INDEXES 语句查看表 tenant_user 上的索引：

SHOW INDEXES FROM tenant_user;

+-------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tenant_user |          0 | PRIMARY             |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tenant_user |          0 | idx_tenant_username |            1 | tenant_id   | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tenant_user |          0 | idx_tenant_username |            2 | username    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

验证 tenant 表上的唯一索引。

让我们使用以下语句向 tenant 表中插入一行：
```
INSERT INTO `tenant` (name, email)
VALUES ('A', '[email protected]'), ('B', '[email protected]');
```
让我们再尝试插入一个具有相同电子邮件的行：
```
INSERT INTO `tenant` (name, email)
VALUES ('A', '[email protected]');
```
MySQL 会报错： ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'tenant.idx_tenant_email'。
验证 tenant_user 表上的唯一复合索引。

让我们为 A 租户添加两个用户名分别为 a 和 b 的用户：
```
INSERT INTO `tenant_user` (tenant_id, username, nickname)
VALUES (1, 'a', 'A user1'), (1, 'b', 'A user2');
```
让我们为 B 租户添加一个用户名为 a 的用户：
```
INSERT INTO `tenant_user` (tenant_id, username, nickname)
VALUES (2, 'a', 'B user1');
```
这里，虽然这个用户的用户名已经存在，但是由于 tenant_id 列的值不同，因此可以正常插入。

让我们再尝试为 B 租户添加一个用户名为 a 的用户：
```
INSERT INTO `tenant_user` (tenant_id, username, nickname)
VALUES (2, 'a', 'B user2');
```
这里，MySQL 会返回一个错误： ERROR 1062 (23000): Duplicate entry ‘2-a’ for key ’tenant_user.idx_tenant_username’。

结论

MySQL 唯一索引是一种特殊的索引，它不但可以加快从表中检索数据的速度，还能防止在指定的一个或多个列中出现重复值。

MySQL 唯一索引语法

MySQL 唯一索引实例

结论

本页目录