UPDATE 是修改現有數據的核心 DML 語句。本文系統講解單表/多表更新語法、WHERE 條件最佳實踐、ORDER BY/LIMIT 控制、返回值判斷、事務配合、效能優化與常見陷阱。
基本語法
UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET col1 = expr1, col2 = expr2, ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
單表更新實戰範例
1. 更新所有行（慎用）
UPDATE links SET url = 'https://www.example.com';
-- 影響所有行，無 WHERE 保護
2. 條件更新（標準業務）
-- 修改指定用戶密碼
UPDATE users SET password_hash = SHA2('newpass', 256) WHERE username = 'admin';
-- 批量狀態變更
UPDATE orders SET status = 2, updated_at = NOW() WHERE status = 1 AND created_at < '2023-01-01';
3. 運算式更新（基於原值計算）
-- 庫存扣減
UPDATE products SET stock = stock - 1 WHERE id = 100 AND stock > 0;
-- 計數器增量
UPDATE stats SET pv = pv + 1 WHERE date = CURDATE() AND page = '/home';
-- 字串拼接
UPDATE users SET nickname = CONCAT(nickname, '_vip') WHERE level >= 10;
4. ORDER BY + LIMIT 控制更新範圍
-- 只更新前 100 條符合條件的記錄（按 id 升序）
UPDATE logs SET processed = 1 WHERE processed = 0 ORDER BY id ASC LIMIT 100;
-- 避免大事務長鎖，分批處理大量更新
多表更新（MySQL 專有語法）
-- 內連接更新：僅更新雙方匹配的行
UPDATE orders o
INNER JOIN users u ON o.user_id = u.id
SET o.user_name = u.username, o.user_email = u.email
WHERE o.user_name IS NULL;
-- 左連接更新：更新左表所有行，右表匹配則帶入值，否則 NULL
UPDATE products p
LEFT JOIN categories c ON p.category_id = c.id
SET p.category_name = COALESCE(c.name, '未分類');
返回值與影響行數判斷
| 語言/介面 | 獲取影響行數 | 說明 |
|---|
| MySQL 客戶端 | Query OK, 3 rows affected | 直接顯示 |
| PHP PDO | $stmt->rowCount() | 返回影響行數 |
| PHP mysqli | $mysqli->affected_rows | 屬性 |
| Java JDBC | stmt.executeUpdate() | 返回 int |
| Python PyMySQL | cursor.rowcount | 屬性 |
| Go database/sql | result.RowsAffected() | 返回 (int64, error) |
事務與鎖控制
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 或 ROLLBACK;
- 行鎖：InnoDB 對 WHERE 條件匹配的**索引記錄**加排他鎖（X 鎖）。
- 間隙鎖/Next-Key Lock：RR 隔離級下，範圍條件會鎖定間隙防止幻讀。
- 無索引 WHERE：全表掃描 → **鎖全表所有行**（極其危險）。**永遠確保 WHERE 條件走索引**。
效能優化清單
- WHERE 必須命中索引：
EXPLAIN UPDATE ... (8.0+) 驗證 type=ref/range。
- 避免更新主鍵/唯一鍵欄位：引發 Delete+Insert、外鍵級聯、索引重建。
- 大批量更新分批：每批 500-1000 行，配合
LIMIT、id > last_id 游標，避免長事務、Replication 延遲、Undo Log 膨脹。
- 只更新變更的欄位：
SET col = new_val 而非全欄位 SET col1=val1, col2=val2...，減少 Redo/Undo 體積。
- 使用
IGNORE 忽略重複鍵錯誤：UPDATE IGNORE t SET uk_col = 'x' ... 遇到唯一鍵衝突跳過該行不報錯。
常見陷阱
| 陷阱 | 後果 | 修正 |
|---|
| 忘記 WHERE | 全表更新，業務災難 | 養成「先寫 SELECT 驗證 WHERE」習慣 |
| WHERE 欄位無索引 | 全表掃描 + 全表鎖定 | 建立索引、或分批用主鍵範圍 |
| 子查詢更新同表 | ERROR 1093: You can't specify target table for update in FROM clause | 改用 JOIN 或多步驟 |
| 更新大字段 (TEXT/BLOB) | 產生大量 Undo/Redo、碎片 | 拆表垂直分離、或對象存儲 |
| 隱式類型轉換 | 索引失效、數據截斷 | 參數綁定、CAST 顯式轉換 |
PHP 完整範例
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
]);
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE users SET balance = balance - ? WHERE id = ? AND balance >= ?");
$affected = $stmt->execute([100.00, 123, 100.00]);
if ($stmt->rowCount() === 0) {
throw new Exception("Insufficient balance or user not found");
}
$pdo->commit();
echo "Update success, rows: " . $stmt->rowCount();
} catch (Exception $e) {
$pdo->rollBack();
echo "Update failed: " . $e->getMessage();
}
?>