萬盛學電腦網

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

MySQL 防止重複插入的 5 種方案:從約束到應用層的完整防禦

方案對比總覽

方案層級性能適用場景備註
唯一索引 + INSERT IGNORE數據庫極高幂等寫入、不關心是否真插入靜默忽略,無錯誤
唯一索引 + ON DUPLICATE KEY UPDATE數據庫高Upsert、需更新時間戳/計數器返回 affected_rows=2
INSERT ... SELECT ... WHERE NOT EXISTS數據庫中複雜條件、跨表去重單語句原子
應用層先查後插應用低(2次往返)邏輯複雜、需用戶友好提示併發下有競態,需加鎖
分佈式鎖(Redis/DB)應用中跨服務、高併發強一致增加架構複雜度

方案一:唯一索引 + INSERT IGNORE(最簡)

-- 表結構
CREATE TABLE `users` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(100) NOT NULL,
    `name` VARCHAR(50),
    UNIQUE KEY `uk_email` (`email`)
) ENGINE=InnoDB;

-- 插入(重複郵箱自動忽略,不報錯)
INSERT IGNORE INTO `users` (`email`, `name`) VALUES ('a@b.com', 'Alice');

-- 判斷是否插入成功
SELECT ROW_COUNT();  -- 1=插入, 0=忽略

方案二:ON DUPLICATE KEY UPDATE(推薦業務主流)

INSERT INTO `users` (`email`, `name`, `login_count`, `last_login`) 
VALUES ('a@b.com', 'Alice', 1, NOW())
ON DUPLICATE KEY UPDATE 
    `login_count` = `login_count` + 1,
    `last_login` = NOW(),
    `name` = VALUES(`name`);  -- 可選:同步更新名稱

-- 獲取受影響行 ID(新插或更新)
SELECT LAST_INSERT_ID();

方案三:INSERT ... SELECT ... WHERE NOT EXISTS(條件去重)

INSERT INTO `orders` (`user_id`, `item_id`, `qty`, `created_at`)
SELECT 1001, 5001, 1, NOW()
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM `orders` 
    WHERE `user_id` = 1001 AND `item_id` = 5001 AND `status` = 'pending'
);
  • DUAL 虛表,MySQL/Oracle 通用
  • 子查詢可寫任意複雜條件
  • 單語句原子,無競態

方案四:應用層「查後插」(需加鎖)

beginTransaction();
    $stmt = $pdo->prepare("SELECT id FROM users WHERE email = ? FOR UPDATE");
    $stmt->execute([$email]);
    if ($stmt->fetchColumn()) {
        $pdo->rollBack();
        return false;  // 已存在
    }
    
    // 2. 插入
    $stmt = $pdo->prepare("INSERT INTO users (email, name) VALUES (?, ?)");
    $stmt->execute([$email, $name]);
    $pdo->commit();
    return true;
}

⚠️ 必須 FOR UPDATE 加鎖,否則併發下仍會重複插入。

方案五:分佈式鎖(高併發強一致)


// Redis SETNX 鎖
$lockKey = "lock:user_email:{$email}";
$lockVal = uniqid();
$acquired = $redis->set($lockKey, $lockVal, ['nx', 'ex' => 10]);  // 10s 過期

if ($acquired) {
    try {
        // 雙重檢查 + 插入
        $stmt = $pdo->prepare("SELECT id FROM users WHERE email = ?");
        $stmt->execute([$email]);
        if (!$stmt->fetchColumn()) {
            $pdo->prepare("INSERT INTO users (email, name) VALUES (?, ?)")->execute([$email, $name]);
        }
    } finally {
        // 釋放鎖(Lua 腳本保證原子性)
        $redis->eval("if redis.call('get', KEYS[1]) == ARGV[1] then return redis.call('del', KEYS[1]) else return 0 end", 1, $lockKey, $lockVal);
    }
}

實戰選型建議

  1. **90% 場景**:表加唯一索引 + ON DUPLICATE KEY UPDATE,簡單高效原子
  2. **只寫不更新、允許靜默失敗**:INSERT IGNORE
  3. **條件複雜、無唯一索引**:INSERT ... WHERE NOT EXISTS
  4. **需用戶感知「已存在」提示**:應用層查詢(緩存加速)+ 插入捕獲異常
  5. **跨服務/分庫分表**:分佈式鎖或 Saga 模式

反模式警示

-- ❌ 絕對不要這樣做(競態條件)
$exists = $pdo->query("SELECT 1 FROM users WHERE email='$email'")->fetchColumn();
if (!$exists) {
    $pdo->exec("INSERT INTO users (email) VALUES ('$email')");
}
// 兩條語句間隙極易產生重複
copyright © 萬盛學電腦網 all rights reserved