萬盛學電腦網

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

MySQL UNIQUE vs PRIMARY KEY 深度對比:約束差異、索引特性、空值處理、外鍵關聯與實戰選型

核心定義對比

特性PRIMARY KEYUNIQUE KEY
空值允許❌ NOT NULL 強制✅ 允許 NULL(多行 NULL 不視為重複)
表中數量最多 1 個可多個
索引類型聚簇索引非聚簇索引
外鍵引用✅ 可被外鍵引用✅ 可被外鍵引用
隱含約束UNIQUE + NOT NULLUNIQUE

語法與操作範例

建表時定義

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

技術細節差異

  1. 聚簇 vs 非聚簇:InnoDB 主鍵為聚簇索引,資料與主鍵同存;UNIQUE 為輔助索引,葉子節點存主鍵值,回表查詢
  2. 索引創建預設:UNIQUE 預設建非聚簇索引,可指定 USING BTREE,不支援指定聚簇
  3. 主鍵目的:實體標識,支撐外鍵引用、複制、叢集
  4. 複合主鍵:PRIMARY KEY (col1, col2) 允許,順序影響查詢效能

選型決策樹

  1. 業務主鍵(自然鍵)穩定、不為空、唯一 → PRIMARY KEY
  2. 2. 業務鍵可能為空、或需多組唯一約束 → UNIQUE KEY 3. 需被外鍵引用 → PRIMARY KEY 或 UNIQUE NOT NULL 4. 高頻範圍查詢、排序 → 考慮設為主鍵利用聚簇索引 5. 分散式系統 ID → BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY + 業務鍵 UNIQUE
copyright © 萬盛學電腦網 all rights reserved