MySQL 多表關聯刪除完全指南：語法陷阱與最佳實踐

MySQL 多表關聯刪除完全指南：語法陷阱與最佳實踐

DELETE 是高風險操作，生產環境務必謹慎執行。MySQL 支持多表刪除，但語法靈活性較大，易寫錯。

1. 基礎語法變體

DELETE FROM t1 WHERE 條件;
DELETE t1 FROM t1 WHERE 條件;
DELETE t1 FROM t1, t2 WHERE 條件;
DELETE t1, t2 FROM t1, t2 WHERE 條件;

2. 實戰案例解析

案例 1：刪除主表中有匹配關聯表記錄的數據

-- 寫法 A
DELETE MV FROM MV, Track WHERE Track.trkid = MV.mvid;

-- 寫法 B（USING 語法，推薦可讀性更好）
DELETE FROM MV USING MV, Track WHERE Track.trkid = MV.mvid;

案例 2：刪除主表中無匹配關聯表記錄的數據（反向匹配）

DELETE MV FROM MV
LEFT JOIN Track ON MV.mvid = Track.trkid
WHERE Track.trkid IS NULL;

-- 等價 USING 寫法
DELETE FROM MV USING MV
LEFT JOIN Track ON MV.mvid = Track.trkid
WHERE Track.trkid IS NULL;

案例 3：同時刪除兩表中匹配的記錄

DELETE MV, Track FROM MV
LEFT JOIN Track ON MV.mvid = Track.trkid
WHERE MV.mvid = 1;

-- 使用別名（注意：別名必須前後一致）
DELETE t1, t2 FROM MV t1
LEFT JOIN Track t2 ON t1.mvid = t2.trkid
WHERE t1.mvid = 2;

3. 常見報錯陷阱

錯誤 1：混用表名與別名

DELETE MV, Track FROM MV AS t1 LEFT JOIN Track AS t2 ... -- 報錯

錯誤 2：WHERE 條件未使用別名

DELETE t1, t2 FROM MV t1 LEFT JOIN Track t2 ... WHERE MV.mvid = 2 -- 報錯

核心原則：若使用別名，整條語句必須統一使用別名（包括 SELECT 列表、JOIN 條件、WHERE 條件）。

4. 替代方案：外鍵級聯刪除

定義表關係時指定 ON DELETE CASCADE，刪除主表記錄自動刪除關聯從表數據，避免手寫複雜 DELETE 語句。