核心定義對比
| 特性 | PRIMARY KEY | UNIQUE KEY |
| 空值允許 | ❌ NOT NULL 強制 | ✅ 允許 NULL(多行 NULL 不視為重複) |
| 表中數量 | 最多 1 個 | 可多個 |
| 索引類型 | 聚簇索引 | 非聚簇索引 |
| 外鍵引用 | ✅ 可被外鍵引用 | ✅ 可被外鍵引用 |
| 隱含約束 | UNIQUE + NOT NULL | UNIQUE |
語法與操作範例
建表時定義
CREATE TABLE good_booked (
auto_id INT NOT NULL AUTO_INCREMENT,
good_id INT DEFAULT NULL,
chemist_id INT DEFAULT NULL,
PRIMARY KEY (auto_id),
UNIQUE KEY good_id (good_id, chemist_id),
KEY current_state (current_state),
KEY send_time (send_time)
);
刪除 UNIQUE 索引
ALTER TABLE good_booked DROP INDEX good_id;
UNIQUE 防重複插入的三種策略
1. INSERT IGNORE:靜默跳過
INSERT IGNORE INTO menus (id, label, url) VALUES (4, 'Contact', 'contact.html');
-- 衝突時不報錯,不插入,受影響行數 0
2. ON DUPLICATE KEY UPDATE:轉為更新
INSERT INTO menus (id, label, url) VALUES (4, 'Contact', 'contact.html')
ON DUPLICATE KEY UPDATE label='Contact', url='contact.html';
-- 衝突時更新,受影響行數 2
3. 應用層預檢(不推薦高併發)
SELECT 1 FROM menus WHERE id = 4;
-- 不存在才 INSERT
實戰演示:UNIQUE 約束行為
CREATE TABLE menus (
id TINYINT NOT NULL AUTO_INCREMENT,
label VARCHAR(10),
url VARCHAR(20),
UNIQUE KEY (id)
);
INSERT INTO menus (label, url) VALUES ('Home','home.html'), ('About','about.html');
-- 嘗試插入重複 id=4
INSERT INTO menus (id, label, url) VALUES (4, 'Contact', 'contact.html');
-- ERROR 1062: Duplicate entry '4' for key 'id'
-- IGNORE 跳過
INSERT IGNORE INTO menus (id, label, url) VALUES (4, 'Contact', 'contact.html');
-- Query OK, 0 rows affected
-- ON DUPLICATE KEY UPDATE 覆蓋
INSERT INTO menus (id, label, url) VALUES (4, 'Contact', 'contact.html')
ON DUPLICATE KEY UPDATE label='Contact', url='contact.html';
-- Query OK, 2 rows affected
技術細節差異
- 聚簇 vs 非聚簇:InnoDB 主鍵為聚簇索引,資料與主鍵同存;UNIQUE 為輔助索引,葉子節點存主鍵值,回表查詢
- 索引創建預設:UNIQUE 預設建非聚簇索引,可指定
USING BTREE,不支援指定聚簇
- 主鍵目的:實體標識,支撐外鍵引用、複制、叢集
- 複合主鍵:
PRIMARY KEY (col1, col2) 允許,順序影響查詢效能
選型決策樹
- 業務主鍵(自然鍵)穩定、不為空、唯一 → PRIMARY KEY
2. 業務鍵可能為空、或需多組唯一約束 → UNIQUE KEY
3. 需被外鍵引用 → PRIMARY KEY 或 UNIQUE NOT NULL
4. 高頻範圍查詢、排序 → 考慮設為主鍵利用聚簇索引
5. 分散式系統 ID → BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY + 業務鍵 UNIQUE