在 MySQL 資料庫設計中,UNIQUE 索引 是確保資料唯一性的關鍵工具。它不僅能防止重複資料寫入,還能在多欄位組合唯一性驗證、替代易產生斷層的自增主鍵等場景中發揮重要作用。本文將從語法、適用場景、實戰範例到常見操作,全方位解析 UNIQUE 索引的使用方法。
建立 UNIQUE 索引的標準語法如下:
UNIQUE KEY `索引名稱` (`欄位1`, `欄位2`, ...)
說明:
例如:訂單系統中「用戶 ID + 商品 ID」組合必須唯一,防止重複下單;或是「部門 ID + 職位代碼」組合唯一。
在頻繁增刪資料的表中,自增主鍵會產生大量 ID 斷層(如 1, 2, 5, 10...),導致主鍵值快速耗盡或語義不清。此時可改用業務唯一欄位(如身分證字號、商品編號)建立 UNIQUE 索引作為邏輯主鍵,或搭配複合唯一索引替代。
CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name` (`name`)
);
此表中 `name` 欄位不允許重複值,插入重複 `name` 會報錯 ERROR 1062 (23000): Duplicate entry。
以 `Employee` 表為例:
-- 建表(無唯一索引)
CREATE TABLE Employee (
id INT, first_name VARCHAR(15), last_name VARCHAR(15),
start_date DATE, end_date DATE, salary FLOAT(8,2),
city VARCHAR(10), description VARCHAR(15)
);
-- 嘗試在 city 加唯一索引 → 失敗(資料有重複 Vancouver)
ALTER TABLE Employee ADD UNIQUE address_index (city);
-- ERROR 1062: Duplicate entry 'Vancouver' for key 1
-- 在 last_name 加唯一索引 → 成功(資料無重複)
ALTER TABLE Employee ADD UNIQUE lastn_index (last_name);
-- Query OK, 8 rows affected
關鍵點: 新增 UNIQUE 索引前,必須確保現有資料滿足唯一性,否則會直接報錯。
CREATE TABLE myTable (
OrderID SMALLINT UNSIGNED NOT NULL,
ModelID SMALLINT UNSIGNED NOT NULL,
Description VARCHAR(40),
PRIMARY KEY (OrderID),
UNIQUE KEY `uk_order_model` (OrderID, ModelID)
);
此處 `OrderID` 是主鍵,但 `OrderID + ModelID` 組合也被強制唯一。適用於「同一訂單下同一型號只能出現一次」等業務。
ALTER TABLE `表名` DROP INDEX `索引名`;
例如:ALTER TABLE fuinfo DROP INDEX email;
NOT NULL 約束。UNIQUE 索引是資料完整性的守門員。善用複合唯一索引可解決多欄位業務唯一性需求;在高頻增刪表中替代自增主鍵可避免 ID 斷層問題。實務上,建立前務必驗證現有資料,並配合 EXPLAIN 分析查詢計畫,確保索引真正被優化器採用。