MySQL 刪除操作全解析：DELETE 與 TRUNCATE 差異、優化與陷阱

DELETE 語法與特性

-- 單表
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

-- 多表
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*] ...]
    FROM table_references
    [WHERE where_definition]

-- 或
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*] ...]
    USING table_references
    [WHERE where_definition]

修飾符詳解

• LOW_PRIORITY：延遲執行，直到無其他客戶端讀取該表
• QUICK（MyISAM）：刪除時不合併索引葉節點，加速特定場景刪除；適用「刪除值會被相近新值替換」的情況，否則產生索引碎片
• IGNORE：忽略刪除過程中的錯誤（轉為警告）

LIMIT 子句應用

• 限制單次刪除行數，避免長鎖表、大事務阻塞
• 搭配 ORDER BY 實現「刪除最舊 N 筆」等邏輯：DELETE FROM log WHERE user='x' ORDER BY ts LIMIT 100
• 分批刪除大量資料模式：

  REPEAT
    DELETE FROM big_table WHERE condition LIMIT 1000;
  UNTIL ROW_COUNT() = 0 END REPEAT;
  

多表刪除注意事項

• 支援 INNER JOIN、LEFT JOIN 等連接類型
• 引用表別名時必須使用別名：DELETE t1 FROM test AS t1, test2 WHERE ...
• 跨資料庫刪除不可使用別名：DELETE db1.t1, db2.t2 FROM db1.t1, db2.t2 WHERE ...
• 不支援 ORDER BY 與 LIMIT
• 不可在子查詢中引用同表進行刪除

TRUNCATE TABLE

TRUNCATE [TABLE] tbl_name

與 DELETE 差異對照

InnoDB 特殊行為

• 存在外鍵引用 → 自動轉為 DELETE（逐行檢查約束）
• 無外鍵 → DROP TABLE + CREATE TABLE（瞬間完成）
• AUTO_INCREMENT 計數器永遠重置，無論是否有外鍵

效能優化建議

• 大量刪除：優先 TRUNCATE（無外鍵、無觸發器需求）
• 需保留部分資料：CREATE TABLE new AS SELECT ... FROM old WHERE keep_condition; RENAME TABLE old TO old_bak, new TO old;
• 分批 DELETE ... LIMIT 避免長事務、鎖升級、Replication 延遲
• MyISAM 大量刪除後執行 OPTIMIZE TABLE 回收空間、整理索引
• DELETE QUICK + OPTIMIZE TABLE 組合加速索引重建

小結

DELETE 靈活、事務安全、支援條件與限制；TRUNCATE 極速、重置自增、但不可回滾、不觸發 Trigger、受外鍵限制。根據業務場景選擇：清空全表用 TRUNCATE，條件刪除用 DELETE，大量條件刪除用分批 DELETE 或 重建表法。