萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 多表關聯 UPDATE 與 DELETE 實戰語法大全

MySQL 支援在 UPDATE 與 DELETE 中使用 JOIN,實現多表關聯寫入。本文整理標準語法、別名用法、子查詢陷阱與效能優化技巧。

一、多表 DELETE 語法

1. 基本語法(支援別名)

DELETE a
FROM `table_a` a
INNER JOIN `table_b` b ON a.`id` = b.`id`
WHERE b.`status` = 0;

關鍵點:DELETE 後指定要刪除的表別名(a),FROM 列出所有參與關聯的表。

2. 同時刪除多表資料

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 語法

1. INNER JOIN 更新(推薦,效能最佳)

UPDATE `table_1` t1
INNER JOIN `table_2` t2 ON t1.`uid` = t2.`uid`
SET t1.`score` = t1.`score` + 5
WHERE t2.`sid` = 10;

2. LEFT JOIN 更新(保留左表所有行)

UPDATE `table_1` t1
LEFT JOIN `table_2` t2 ON t1.`id` = t2.`ref_id`
SET t1.`status` = IFNULL(t2.`new_status`, t1.`status`);

3. 多欄位同時更新

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 或應用層迴圈的邏輯,壓縮為單一高效率語句。

copyright © 萬盛學電腦網 all rights reserved