PHP 批量刪除數據：IN 條件優化與安全實踐

後台列表頁勾選多條記錄點擊「批量刪除」，前端提交 ID 陣列，後端構造 DELETE ... WHERE id IN (...) 單條 SQL 執行，比循環刪除效率高數量級。本文涵蓋前端傳參、後端驗證、預處理綁定、事務保護、軟刪除替代方案。

前端：表單提交 ID 陣列

<!-- list.php -->
<form action="delete_batch.php" method="post" id="batchForm">
  <table>
    <thead><tr><th><input type="checkbox" id="checkAll"></th><th>標題</th><th>操作</th></tr></thead>
    <tbody>
      <?php foreach ($rows as $row): ?>
      <tr>
        <td><input type="checkbox" name="ids[]" value="<?= $row['id'] ?>"></td>
        <td><?= htmlspecialchars($row['title']) ?></td>
        <td><a href="edit.php?id=<?= $row['id'] ?>">編輯</a></td>
      </tr>
      <?php endforeach; ?>
    </tbody>
  </table>
  <button type="submit" name="action" value="delete" onclick="return confirm('確定刪除選中項？')">批量刪除</button>
</form>

<script>
document.getElementById('checkAll').addEventListener('change', function() {
  document.querySelectorAll('input[name="ids[]"]').forEach(cb => cb.checked = this.checked);
});
</script>

後端：安全高效刪除

<?php
// delete_batch.php
require 'db.php'; // $mysqli

$ids = $_POST['ids'] ?? [];
$action = $_POST['action'] ?? '';

if ($action !== 'delete' || empty($ids)) {
    header('Location: list.php?error=invalid_request');
    exit;
}

// 1. 輸入驗證：只保留正整數 ID
$validIds = array_filter($ids, fn($id) => ctype_digit((string)$id) && (int)$id > 0);
$validIds = array_map('intval', $validIds); // 強制轉 int
$validIds = array_unique($validIds);        // 去重

if (empty($validIds)) {
    header('Location: list.php?error=no_valid_ids');
    exit;
}

// 2. 構造 IN 佔位符
$placeholders = implode(',', array_fill(0, count($validIds), '?'));
$types = str_repeat('i', count($validIds)); // 全部整數

// 3. 軟刪除（推薦）：更新 deleted_at 欄位，保留數據可恢復
$sql = "UPDATE `articles` SET `deleted_at` = NOW() WHERE `id` IN ($placeholders)";

// 硬刪除（謹慎）：
// $sql = "DELETE FROM `articles` WHERE `id` IN ($placeholders)";

$stmt = $mysqli->prepare($sql);
if (!$stmt) {
    error_log("Prepare failed: " . $mysqli->error);
    header('Location: list.php?error=db_error');
    exit;
}

// 4. 動態綁定參數
$stmt->bind_param($types, ...$validIds);

// 5. 事務執行（若涉及多表關聯刪除）
$mysqli->begin_transaction();
try {
    $stmt->execute();
    $affected = $stmt->affected_rows;
    $stmt->close();
    
    // 關聯表同步軟刪除（示例）
    $stmt2 = $mysqli->prepare("UPDATE `comments` SET `deleted_at` = NOW() WHERE `article_id` IN ($placeholders)");
    $stmt2->bind_param($types, ...$validIds);
    $stmt2->execute();
    $stmt2->close();
    
    $mysqli->commit();
    
    header('Location: list.php?success=1&deleted=' . $affected);
} catch (Exception $e) {
    $mysqli->rollback();
    error_log("Batch delete failed: " . $e->getMessage());
    header('Location: list.php?error=execute_failed');
}
?>

關鍵優化點解析

大批量刪除（> 1000 條）分塊策略

<?php
function chunkedDelete(mysqli $mysqli, array $ids, int $chunkSize = 500): int {
    $total = 0;
    foreach (array_chunk($ids, $chunkSize) as $chunk) {
        $placeholders = implode(',', array_fill(0, count($chunk), '?'));
        $types = str_repeat('i', count($chunk));
        $stmt = $mysqli->prepare("UPDATE `articles` SET `deleted_at` = NOW() WHERE `id` IN ($placeholders)");
        $stmt->bind_param($types, ...$chunk);
        $stmt->execute();
        $total += $stmt->affected_rows;
        $stmt->close();
    }
    return $total;
}
// 避免單條 SQL 過長超過 max_allowed_packet、鎖表時間過長影響併發
?>

前端增強：AJAX 異步批量刪除

<script>
async function batchDelete() {
  const checked = [...document.querySelectorAll('input[name="ids[]"]:checked')].map(cb => cb.value);
  if (!checked.length) return alert('請選擇項目');
  if (!confirm(`確定刪除 ${checked.length} 條記錄？`)) return;
  
  const fd = new FormData();
  checked.forEach(id => fd.append('ids[]', id));
  fd.append('action', 'delete');
  
  try {
    const res = await fetch('delete_batch_ajax.php', {method: 'POST', body: fd});
    const data = await res.json();
    if (data.success) {
      alert(`刪除成功 ${data.deleted} 條`);
      checked.forEach(id => document.getElementById('row-' + id)?.remove());
    } else {
      alert('失敗: ' + data.message);
    }
  } catch (e) {
    alert('請求異常: ' + e.message);
  }
}
</script>