您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL UPDATE 與 DELETE 實戰指南：條件構建、表達式更新、影響行數、事務安全與大批量刪除優化

編輯：MySQL 資料庫相關知識

前言

UPDATE 與 DELETE 是資料修改的核心 DML，看似簡單但暗藏「無 WHERE 全表更新」、「表達式求值順序」、「大批量刪除鎖升級」、「主從延遲」等生產事故隱患。本文系統講解語法、安全模式、表達式陷阱、批量優化策略與事務包裹最佳實踐。

一、UPDATE

1.1 基本語法

UPDATE `table`
SET col1 = val1, col2 = val2, ...
WHERE condition;

WHERE 缺失 → **全表更新**，極度危險。
多欄位賦值用逗號分隔，**按寫入順序左到右求值** (見 1.3)。

1.2 安全模式

-- 會話級開啟 (預設 my.cnf 可能已開啟)
SET SQL_SAFE_UPDATES = 1;
-- 此模式下：UPDATE/DELETE 必須帶 WHERE 且 WHERE 條件需使用索引鍵，否則報錯

**生產環境強制開啟**，防止手滑全表操作。

1.3 表達式求值順序陷阱

UPDATE t SET a = a + 1, b = a + 1;
-- MySQL 按左到右求值：
-- 1. 計算 a+1 → 新 a
-- 2. 計算 a+1 (此時 a 已是新值) → 新 b
-- 結果：a 增 1，b = 新 a (非原 a+1)

**修正**：若需同時基於原值計算，用用戶變數或子查詢：

UPDATE t SET a = @new_a := a + 1, b = @new_a;

1.4 實戰：原子計數器

-- 文章閱讀數 +1 (無競爭條件)
UPDATE article SET views = views + 1 WHERE aid = 123;

-- 庫存扣減 (防超賣)
UPDATE product SET stock = stock - 1 WHERE pid = 100 AND stock > 0;
-- 影響行數 = 1 表示成功，0 表示庫存不足

1.5 多表 UPDATE

-- 同步用戶最新文章數到 user 表
UPDATE user u
INNER JOIN (
    SELECT uid, COUNT(*) AS cnt FROM article GROUP BY uid
) a ON u.uid = a.uid
SET u.article_count = a.cnt;

二、DELETE

2.1 基本語法

DELETE FROM `table` WHERE condition;

無 WHERE → **全表刪除** (不可回滾若無事務)。
回傳影響行數 (ROW_COUNT() / mysql_affected_rows())。

2.2 大批量刪除優化 (核心痛點)

**問題**：DELETE FROM huge_table WHERE created_at < '2023-01-01' 一次刪百萬行 →
- 鎖升級為表鎖，阻塞所有讀寫。
- Undo Log 暴增，History List Length 飆升，影響 MVCC 讀取。
- Binlog 巨大，主從延遲嚴重。
- 磁碟空間不釋放 (需 OPTIMIZE TABLE)。

2.3 分批刪除模板

#!/bin/bash
# batch_delete.sh
# 用法: ./batch_delete.sh 'DELETE FROM orders WHERE created_at < "2023-01-01"' 1000 1

SQL="$1"
BATCH_SIZE=${2:-1000}
SLEEP_SEC=${3:-1}

while true; do
    # 使用 LIMIT 限制每批行數 (MySQL 支援 DELETE ... LIMIT)
    ROWS=$(mysql -N -e "${SQL} LIMIT ${BATCH_SIZE}; SELECT ROW_COUNT();" | tail -1)
    echo "$(date) Deleted $ROWS rows"
    if [[ $ROWS -eq 0 ]]; then break; fi
    sleep $SLEEP_SEC
done

-- 也可用存儲過程
DELIMITER //
CREATE PROCEDURE batch_delete()
BEGIN
    DECLARE done INT DEFAULT 0;
    REPEAT
        DELETE FROM orders WHERE created_at < '2023-01-01' LIMIT 1000;
        SELECT ROW_COUNT() INTO done;
        DO SLEEP(1);
    UNTIL done = 0 END REPEAT;
END//
DELIMITER ;

2.4 軟刪除模式 (推薦)

ALTER TABLE user ADD COLUMN deleted_at DATETIME NULL DEFAULT NULL;
-- 刪除 → 更新標記
UPDATE user SET deleted_at = NOW() WHERE uid = 123;
-- 查詢自動排除
SELECT * FROM user WHERE deleted_at IS NULL;
-- 定期真刪 (分批)
DELETE FROM user WHERE deleted_at < NOW() - INTERVAL 90 DAY LIMIT 1000;

2.5 多表 DELETE

-- 刪除用戶及其所有文章
DELETE u, a FROM user u
LEFT JOIN article a ON u.uid = a.uid
WHERE u.uid = 123;

三、事務包裹與主從一致性

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE user_id = 1 AND balance >= 100;
IF ROW_COUNT() = 0 THEN ROLLBACK; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '餘額不足'; END IF;
INSERT INTO transaction (user_id, amount, type) VALUES (1, -100, 'debit');
COMMIT;

**關鍵業務務必事務包裹**，確保原子性。
主從環境下，大事務會導致從庫 SQL Thread 長時間阻塞 → 延遲。拆分為小事務或用 binlog_format=ROW 減少鎖爭用。

四、PHP PDO 實戰封裝

function safeUpdate(PDO $pdo, string $table, array $data, array $where): int {
    $set = implode(', ', array_map(fn($k) => "`$k` = ?", array_keys($data)));
    $whereSql = implode(' AND ', array_map(fn($k) => "`$k` = ?", array_keys($where)));
    $sql = "UPDATE `$table` SET $set WHERE $whereSql";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array_merge(array_values($data), array_values($where)));
    return $stmt->rowCount();
}

function safeDelete(PDO $pdo, string $table, array $where, int $limit = 0): int {
    $whereSql = implode(' AND ', array_map(fn($k) => "`$k` = ?", array_keys($where)));
    $sql = "DELETE FROM `$table` WHERE $whereSql" . ($limit ? " LIMIT $limit" : '');
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array_values($where));
    return $stmt->rowCount();
}

結語： UPDATE 注意表達式求值順序與安全模式；DELETE 大批量**必須分批、加 LIMIT、睡眠間隔**，或改用軟刪除。所有寫操作**納入事務**，配合 ROW_COUNT() 驗證影響行數，構建可靠的資料修改防線。