萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL UNIQUE 索引完整使用指南與實戰範例

在 MySQL 資料庫設計中,UNIQUE 索引 是確保資料唯一性的關鍵工具。它不僅能防止重複資料寫入,還能在多欄位組合唯一性驗證、替代易產生斷層的自增主鍵等場景中發揮重要作用。本文將從語法、適用場景、實戰範例到常見操作,全方位解析 UNIQUE 索引的使用方法。

一、UNIQUE 索引基本語法

建立 UNIQUE 索引的標準語法如下:

UNIQUE KEY `索引名稱` (`欄位1`, `欄位2`, ...)

說明:

  • 索引名稱可自訂(如 `test`、`xref_key`),若省略則 MySQL 自動以第一個欄位名命名。
  • 支援單一欄位或多欄位組合(複合唯一索引),檢查指定欄位組合的值是否唯一。
  • 一張表可建立多個 UNIQUE 索引,但每個索引名稱在表中必須唯一。

二、核心適用場景

1. 多欄位相互依賴的唯一性約束

例如:訂單系統中「用戶 ID + 商品 ID」組合必須唯一,防止重複下單;或是「部門 ID + 職位代碼」組合唯一。

2. 替代易產生斷層的 AUTO_INCREMENT 主鍵

在頻繁增刪資料的表中,自增主鍵會產生大量 ID 斷層(如 1, 2, 5, 10...),導致主鍵值快速耗盡或語義不清。此時可改用業務唯一欄位(如身分證字號、商品編號)建立 UNIQUE 索引作為邏輯主鍵,或搭配複合唯一索引替代。

三、實戰範例詳解

範例 1:單一欄位 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。

範例 2:動態新增/移除 UNIQUE 索引

以 `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 索引前,必須確保現有資料滿足唯一性,否則會直接報錯。

範例 3:複合唯一索引(多欄位組合唯一)

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` 組合也被強制唯一。適用於「同一訂單下同一型號只能出現一次」等業務。

範例 4:刪除 UNIQUE 索引

ALTER TABLE `表名` DROP INDEX `索引名`;

例如:ALTER TABLE fuinfo DROP INDEX email;

四、進階技巧與注意事項

  1. 命名規範: 建議採用 `uk_表名_欄位1_欄位2` 格式,便於維護識別。
  2. NULL 值處理: MySQL 允許 UNIQUE 索引欄位存放多個 NULL(視為不同值),若需禁止 NULL,請加上 NOT NULL 約束。
  3. 效能考量: 唯一索引會增加寫入開銷(插入/更新時需檢查唯一性),讀取查詢則可大幅加速。高頻寫入表需權衡。
  4. 與主鍵差異: 表僅能有一個 PRIMARY KEY(隱含 NOT NULL + UNIQUE),但可有多個 UNIQUE KEY;PRIMARY KEY 通常作為叢集索引,UNIQUE KEY 為非叢集索引(InnoDB 中包含主鍵值)。

五、總結

UNIQUE 索引是資料完整性的守門員。善用複合唯一索引可解決多欄位業務唯一性需求;在高頻增刪表中替代自增主鍵可避免 ID 斷層問題。實務上,建立前務必驗證現有資料,並配合 EXPLAIN 分析查詢計畫,確保索引真正被優化器採用。

copyright © 萬盛學電腦網 all rights reserved