本文 AI 產出，尚未審核

MySQL 基本資料 CRUD – DELETE 刪除資料

簡介

在資料庫的生命周期中，刪除（DELETE） 是不可或缺的操作之一。
隨著系統使用者不斷新增、修改資料，舊資料或錯誤資料若不及時清除，會導致資料表膨脹、查詢效能下降，甚至影響商業決策的正確性。
因此，掌握安全、有效的 DELETE 語法與最佳實踐，是每位 MySQL 開發者必備的基本功。

本篇文章將從概念說明、實作範例、常見陷阱與最佳實踐，帶你一步步了解 如何在 MySQL 中正確刪除資料，並提供實務上常見的應用情境，讓初學者也能快速上手，同時給予中級開發者進一步的優化技巧。

核心概念

1. 基本 DELETE 語法

DELETE FROM `table_name` WHERE 條件;
  • DELETE FROM：指定要刪除資料的表格。
  • WHERE：條件子句，只有符合條件的列會被刪除。若省略 WHERE，整表資料將全部被清除（相當於 TRUNCATE，但會逐筆記錄日誌）。

⚠️ 在正式環境執行前，務必先確認 WHERE 條件，或先在測試環境跑 SELECT 確認要刪除的資料。

2. 多條件刪除與邏輯運算子

DELETE FROM `orders`
WHERE `order_date` < '2023-01-01'      -- 日期較舊
  AND `status` = 'cancelled'          -- 已取消的訂單
  OR `total_amount` = 0;              -- 或金額為 0 的訂單
  • AND 與 OR 的優先順序同程式語言，建議使用 括號 明確分組，避免邏輯錯誤。
DELETE FROM `orders`
WHERE (`order_date` < '2023-01-01' AND `status` = 'cancelled')
   OR `total_amount` = 0;

3. LIMIT 與 ORDER BY（限定刪除筆數）

在需要分批刪除大量資料時，可結合 LIMIT 與 ORDER BY：

DELETE FROM `log_events`
WHERE `created_at` < DATE_SUB(NOW(), INTERVAL 90 DAY)
ORDER BY `created_at` ASC
LIMIT 5000;
  • ORDER BY：先刪除最舊的日誌，避免一次掃描整表。
  • LIMIT：一次最多刪除 5000 筆，配合排程腳本分批執行，減少對系統的衝擊。

4. 刪除與外鍵約束（ON DELETE）

若表格之間存在外鍵關聯，刪除資料時會受到 參照完整性 的限制。
外鍵可設定以下動作：

動作 說明
CASCADE 刪除父表資料時，同時刪除子表相對應的資料。
SET NULL 刪除父表資料時，將子表外鍵欄位設為 NULL。
RESTRICT 預設行為，若子表仍有參照，則阻止刪除。
NO ACTION 與 RESTRICT 同效，僅在某些儲存引擎或版本中有差異。 
CREATE TABLE `customers` (
  `customer_id` INT PRIMARY KEY,
  `name` VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE `orders` (
  `order_id` INT PRIMARY KEY,
  `customer_id` INT,
  `order_date` DATE,
  FOREIGN KEY (`customer_id`) REFERENCES `customers`(`customer_id`)
    ON DELETE CASCADE
) ENGINE=InnoDB;

刪除 customers 時，相關的 orders 會自動被刪除。

5. 使用子查詢（Subquery）刪除

有時候刪除條件需要先從另一張表取得資料，這時可使用子查詢：

DELETE FROM `product_inventory`
WHERE `product_id` IN (
    SELECT `id` FROM `products`
    WHERE `discontinued` = 1
);

注意：在 MySQL 8.0 之前，子查詢會先產生臨時表，若資料量龐大可能造成效能問題。可改用 JOIN 方式。

DELETE pi
FROM `product_inventory` AS pi
JOIN `products` AS p ON pi.`product_id` = p.`id`
WHERE p.`discontinued` = 1;

程式碼範例

以下提供 5 個實務常見的 DELETE 範例，每段程式碼均附上說明與注意事項。

範例 1：刪除單筆資料（最基本）

-- 刪除會員編號為 102 的使用者
DELETE FROM `users`
WHERE `user_id` = 102;

小技巧：執行前先跑同條件的 SELECT 確認唯一性，避免一次刪除多筆。

範例 2：批次刪除過期的優惠券

DELETE FROM `coupons`
WHERE `expiry_date` < CURDATE()
  AND `status` = 'unused';
  • 只刪除已過期且尚未使用的券，保留已使用或尚未過期的紀錄。
  • 若優惠券表格非常大，建議加上 INDEX (expiry_date, status) 提升效能。

範例 3：分批刪除舊的系統日誌（配合排程）

-- 每次刪除 10,000 筆最舊的 log
DELETE FROM `system_log`
WHERE `log_time` < DATE_SUB(NOW(), INTERVAL 180 DAY)
ORDER BY `log_time` ASC
LIMIT 10000;

實務建議：將上述語句寫入 cron 或 Windows Task Scheduler，每日或每小時執行一次，避免一次性刪除大量資料造成鎖表。

範例 4：使用外鍵 CASCADE 刪除客戶與其訂單

-- 刪除客戶 2001，相關的 orders 會自動被刪除
DELETE FROM `customers`
WHERE `customer_id` = 2001;
  • 前提：orders.customer_id 已設定 ON DELETE CASCADE。
  • 若未設定，執行此語句會因參照完整性錯誤而失敗。

範例 5：以 JOIN 刪除已下架商品的庫存

DELETE pi
FROM `product_inventory` AS pi
JOIN `products` AS p ON pi.`product_id` = p.`id`
WHERE p.`discontinued` = 1;
  • 這裡使用 DELETE … FROM 形式，允許在同一語句中使用 JOIN。
  • 效能遠優於子查詢，特別是 products 與 product_inventory 各自都有千萬筆資料時。

常見陷阱與最佳實踐

陷阱 可能的後果 解決方案或最佳實踐
忘記加 WHERE 整表資料全部被刪除，資料無法復原。 先執行 SELECT 確認條件，或在開發環境加 --safe-updates 參數。
外鍵 RESTRICT 刪除父表時因子表仍有參照而失敗。 設計時考慮 ON DELETE CASCADE 或先刪除子表資料。
大量刪除未加 LIMIT 大量 IO、鎖表、事務日誌膨脹，導致系統卡頓。 使用 分批刪除（LIMIT + ORDER BY）配合排程。
缺乏索引 條件掃描全表，效能極差。 為常用的 WHERE 欄位建立 複合索引（如 expiry_date, status）。
不當使用子查詢 產生臨時表，導致記憶體耗盡。 改用 JOIN 或 EXISTS 檢查存在性。
事務未提交 刪除在事務內，但未 COMMIT，其他連線看不到變更。 使用 明確的事務控制（START TRANSACTION → COMMIT / ROLLBACK）。

最佳實踐總結：

  1. 先測試，再執行：在測試環境先跑 SELECT，確認條件正確。
  2. 加索引：對常用的刪除條件欄位加索引，減少全表掃描。
  3. 分批處理：大量資料刪除時，配合 LIMIT、ORDER BY 與排程。
  4. 備份策略：在關鍵資料表執行刪除前，做好備份或快照，以防失誤。
  5. 日誌與審計：使用 binlog、audit 或自行紀錄刪除操作，提升可追溯性。

實際應用場景

1. 電子商務平台 – 清除過期促銷碼

  • 需求：每日清除已過期且未被使用的促銷碼，避免表格膨脹影響搜尋速度。
  • 解法：利用排程執行範例 2 的語句，搭配 LIMIT 5000 分批刪除。

2. SaaS 系統 – 软删除（Soft Delete）與硬刪除（Hard Delete）

  • 软删除：在資料表加入 deleted_at 欄位，使用 UPDATE 方式標記刪除，保留歷史。
  • 硬刪除：定期（如每 90 天）將已软删除且 deleted_at 超過期限的資料真正刪除，降低儲存成本。
  • 實作：
-- 軟刪除：標記
UPDATE `invoices`
SET `deleted_at` = NOW()
WHERE `status` = 'canceled' AND `deleted_at` IS NULL;

-- 硬刪除：真正移除
DELETE FROM `invoices`
WHERE `deleted_at` < DATE_SUB(NOW(), INTERVAL 90 DAY);

3. 企業內部系統 – 清理暫存表

  • 情境：系統每次匯入大量暫存資料，匯入完成後即刪除暫存表內容。
  • 技巧：使用 TRUNCATE TABLE 效率最高，但若需保留事務日誌，改為 DELETE FROM 搭配 WHERE 1=1.

4. 多租戶 SaaS – 租戶下線時刪除其所有資料

  • 挑戰：確保不會誤刪除其他租戶資料。
  • 做法：所有表格均有 tenant_id 欄位，刪除時必須加上 tenant_id = ? 條件，並在事務中一次完成多表刪除。
START TRANSACTION;

DELETE FROM `orders` WHERE `tenant_id` = 42;
DELETE FROM `customers` WHERE `tenant_id` = 42;
DELETE FROM `subscriptions` WHERE `tenant_id` = 42;

COMMIT;

總結

  • DELETE 是資料庫 CRUD 中不可或缺的「刪除」操作，掌握正確語法與條件設定是避免資料災難的第一步。
  • 透過 索引、分批、事務 以及 外鍵設定，可以在確保資料完整性的同時，維持系統效能。
  • 常見的陷阱（忘記 WHERE、外鍵限制、缺乏備份）只要養成 先 SELECT、先測試、先備份 的好習慣，就能大幅降低風險。
  • 在實務上，從 促銷碼清除、軟硬刪除策略、暫存表清理 到 多租戶資料隔離，DELETE 都扮演著維護資料品質與儲存成本的關鍵角色。

掌握以上概念與技巧後，你就能在 MySQL 中自信地使用 DELETE，讓資料庫保持乾淨、效能持續優化，為應用系統的穩定運行奠定堅實基礎。祝你寫出安全、快速、易維護的資料刪除程式碼！