唯一索引

导学

业务中经常需要确保某列的值不重复——如员工姓名、工号等。唯一索引不仅提供了这种约束，还能像普通索引一样加速查询。

定义

唯一索引（Unique Index）：一种特殊的索引，它在加速查询的同时，强制保证索引列的值在全表中唯一。NULL 值在唯一索引中比较特殊：MySQL 允许多个 NULL 值存在（因为 NULL <> NULL）。

创建方式

方式一：CREATE UNIQUE INDEX

当前数据状态：创建员工表

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

SELECT * FROM employees;

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

操作语句：

-- 为 emp_name 列创建唯一索引
CREATE UNIQUE INDEX uk_emp_name ON employees(emp_name);

SHOW INDEX FROM employees;

操作后数据状态（节选）：

Table	Non_unique	Key_name	Seq_in_index	Column_name
employees	0	PRIMARY	1	emp_id
employees	0	uk_emp_name	1	emp_name

结果解读：Non_unique=0 表示这是唯一索引。现在 emp_name 列不能有重复值。

方式二：建表时声明

CREATE TABLE members (
    member_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    UNIQUE KEY uk_name (emp_name),
    UNIQUE KEY uk_dept (dept)
);

SHOW INDEX FROM members;

Table	Key_name	Seq_in_index	Column_name
members	PRIMARY	1	member_id
members	uk_name	1	emp_name
members	uk_dept	1	dept

结果解读：建表时通过 UNIQUE KEY 可以同时创建多个唯一索引。member_id 是主键（特殊的唯一索引），emp_name 和 dept 也分别被唯一索引约束。

方式三：ALTER TABLE ADD CONSTRAINT UNIQUE

当前数据状态：employees 表已有 uk_emp_name。

操作语句：

ALTER TABLE employees ADD CONSTRAINT uk_dept UNIQUE (dept);

SHOW INDEX FROM employees;

操作后数据状态：employees 表现在有 uk_emp_name 和 uk_dept 两个唯一索引。

在 MySQL 中，UNIQUE 约束和 UNIQUE INDEX 在底层实现上是完全相同的——都是唯一索引。

SQL 示例

场景一：唯一索引阻止重复插入

当前数据状态：employees 表已有数据

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

操作语句：

-- 尝试插入重复的 emp_name
INSERT INTO employees (emp_name, dept, score) VALUES ('大翔', '产品部', 90);

操作后数据状态：

ERROR 1062 (23000): Duplicate entry '大翔' for key 'uk_emp_name'

SELECT * FROM employees;

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

结果解读：由于 emp_name 列有唯一索引，'大翔' 已存在，插入被拒绝。表中数据没有变化。

场景二：处理重复插入（INSERT IGNORE）

当前数据状态：同上。

操作语句：

-- 使用 INSERT IGNORE 忽略重复错误
INSERT IGNORE INTO employees (emp_name, dept, score) VALUES ('大翔', '产品部', 90);

SELECT * FROM employees;

操作后数据状态：

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

结果解读：INSERT IGNORE 遇到唯一键冲突时静默跳过，不报错，也不插入数据。返回 0 rows affected。适用于"插入一条记录，如果已存在则忽略"的场景。

场景三：重复时更新（ON DUPLICATE KEY UPDATE）

当前数据状态：employees 表同上。

操作语句：

-- 重复时更新 dept 和 score
INSERT INTO employees (emp_name, dept, score)
VALUES ('大翔', '产品部', 90)
ON DUPLICATE KEY UPDATE dept = '产品部', score = 90;

SELECT * FROM employees;

操作后数据状态：

emp_id	emp_name	dept	score
1	大翔	产品部	90
2	白歌	技术部	NULL

结果解读：ON DUPLICATE KEY UPDATE 遇到唯一键冲突时执行 UPDATE 部分。大翔 的 dept 从 技术部 更新为 产品部，score 从 100 更新为 90。这实现了"存在则更新、不存在则插入"的 Upsert 语义。

场景四：NULL 值的特殊性

当前数据状态：创建新表测试 NULL

CREATE TABLE null_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(20) UNIQUE
);

操作语句：

-- 插入多个 NULL
INSERT INTO null_test (code) VALUES (NULL);
INSERT INTO null_test (code) VALUES (NULL);
INSERT INTO null_test (code) VALUES (NULL);

SELECT * FROM null_test;

操作后数据状态：

id	code
1	NULL
2	NULL
3	NULL

结果解读：三条记录都插入成功！在 SQL 标准中，NULL 表示未知，任何值与 NULL 的比较结果都是 UNKNOWN。因此 NULL <> NULL 的结果是 UNKNOWN 而非 TRUE，多个 NULL 不违反唯一性约束。

场景五：唯一索引加速查询

当前数据状态：employees 表有唯一索引 uk_emp_name。

操作语句：

-- 按 emp_name 查询
SELECT * FROM employees WHERE emp_name = '白歌';

操作后数据状态：

emp_id	emp_name	dept	score
2	白歌	技术部	NULL

结果解读：唯一索引和普通索引一样，能加速查询。由于唯一索引保证列值不重复，优化器知道匹配到一条记录后就可以停止搜索，因此效率比普通索引更高。

主键 vs 唯一索引

特性	主键（PRIMARY KEY）	唯一索引（UNIQUE）
唯一性	✅	✅
允许 NULL	❌	✅（允许多个）
每张表数量	只能有 1 个	可以有多个
聚簇索引	是	否
外键引用	可以被引用	可以被引用（5.7+）

对比示例：

CREATE TABLE pk_vs_uk (
    id INT PRIMARY KEY,           -- 主键：不允许 NULL，聚簇索引
    passport VARCHAR(20) UNIQUE,  -- 唯一索引：允许 NULL，二级索引
    id_card VARCHAR(18) UNIQUE    -- 唯一索引：允许 NULL，二级索引
);

-- 主键不能为 NULL
INSERT INTO pk_vs_uk (id, passport, id_card) VALUES (NULL, 'P123', 'I456');
-- ERROR 1048: Column 'id' cannot be null

-- 唯一索引可以为 NULL
INSERT INTO pk_vs_uk (id, passport, id_card) VALUES (1, NULL, 'I456');
INSERT INTO pk_vs_uk (id, passport, id_card) VALUES (2, NULL, 'I789');
-- 两条都成功！

SELECT * FROM pk_vs_uk;

id	passport	id_card
1	NULL	I456
2	NULL	I789

常见误区

误区	正解
唯一索引就是主键	不是。主键更严格（不允许 NULL），且只有一个。
唯一索引不能有 NULL	可以，且允许多个 NULL。
`UNIQUE` 约束和 `UNIQUE INDEX` 不同	在 MySQL 中完全相同，底层都是唯一索引。

面试考点

Q：唯一索引和主键的区别？

主键不允许 NULL 且每张表只能有一个，同时是聚簇索引；唯一索引允许 NULL（多个），每张表可有多个，是二级索引。

Q：唯一索引中为什么可以有多个 NULL？

因为 SQL 的三值逻辑规定 NULL <> NULL 的结果是 UNKNOWN 而非 TRUE，所以多个 NULL 不违反唯一性约束。

Q：INSERT IGNORE 和 ON DUPLICATE KEY UPDATE 的区别？

INSERT IGNORE 遇到唯一键冲突时忽略错误，不插入也不更新；ON DUPLICATE KEY UPDATE 遇到冲突时执行指定的 UPDATE 操作，实现"存在则更新、不存在则插入"。

小结

唯一索引保证列值唯一，同时加速查询
允许多个 NULL 值存在
INSERT IGNORE 和 ON DUPLICATE KEY UPDATE 是处理唯一冲突的利器
主键是特殊的唯一索引，更严格（不允许 NULL，只能有一个）

下一章引子：单列索引不够用，多列组合查询需要多列索引——复合索引。