MySQL 支援在 UPDATE 與 DELETE 中使用 JOIN,實現多表關聯寫入。本文整理標準語法、別名用法、子查詢陷阱與效能優化技巧。
DELETE a
FROM `table_a` a
INNER JOIN `table_b` b ON a.`id` = b.`id`
WHERE b.`status` = 0;
關鍵點:DELETE 後指定要刪除的表別名(a),FROM 列出所有參與關聯的表。
DELETE a, b
FROM `table_a` a
LEFT JOIN `table_b` b ON a.`id` = b.`a_id`
WHERE a.`created_at` < '2023-01-01';
UPDATE `table_1` t1
INNER JOIN `table_2` t2 ON t1.`uid` = t2.`uid`
SET t1.`score` = t1.`score` + 5
WHERE t2.`sid` = 10;
UPDATE `table_1` t1
LEFT JOIN `table_2` t2 ON t1.`id` = t2.`ref_id`
SET t1.`status` = IFNULL(t2.`new_status`, t1.`status`);
UPDATE `categories` c
INNER JOIN `category_meta` m ON c.`id` = m.`cat_id`
SET c.`display_order` = CASE c.`id`
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
c.`title` = CASE c.`id`
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE c.`id` IN (1,2,3);
MySQL 禁止 在子查詢中直接引用正在更新/刪除的目標表:
-- 錯誤:ERROR 1093 (HY000)
DELETE FROM `tab1` WHERE `col1` = (SELECT MAX(`col1`) FROM `tab1`);
-- 正確:給子查詢表加別名,產生衍生表
DELETE FROM `tab1` WHERE `col1` = (SELECT MAX(`col1`) FROM `tab1` AS a);
-- 刪除分類及其下所有文章
DELETE `category`, `news`
FROM `category`
LEFT JOIN `news` ON `category`.`id` = `news`.`category_id`
WHERE `category`.`id` = 5;
PRIMARY KEY、FOREIGN KEY 或普通索引)INNER JOIN 而非子查詢,優化器執行計畫更穩定LIMIT 1000 迴圈),避免長鎖表EXPLAIN 檢查執行計畫,確認 type=ref/eq_ref、Using where掌握多表關聯寫法,能將原本需多條 SQL 或應用層迴圈的邏輯,壓縮為單一高效率語句。