萬盛學電腦網

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

MySQL 跨表更新三大經典寫法:JOIN、多欄位、子查詢實戰對比

MySQL 支援跨表更新,三種寫法各有優劣。本文通過實戰範例對比 JOIN、多欄位並行更新、子查詢三種模式的適用場景與效能差異。

場景:依據 city 表更新 student 表的城市名稱

-- student 表
id | name | city_code | city_name
1  | 張三 | 010       | NULL
2  | 李四 | 021       | NULL

-- city 表
code | name
010  | 北京
021  | 上海

方案一:JOIN 更新單欄位 (推薦)

UPDATE student s
JOIN city c ON s.city_code = c.code
SET s.city_name = c.name;
  • 語義清晰,標準 SQL 擴展
  • 優化器可選擇最優連接順序
  • 適用於單欄位或少數欄位更新

方案二:JOIN 更新多欄位

UPDATE a
JOIN b ON a.id = b.id
SET a.title = b.title,
    a.name = b.name,
    a.status = b.status;
  • 單次掃描完成多欄位同步
  • 避免多次掃描或多個子查詢
  • 注意:SET 子句中欄位賦值順序為並行,互不影響

方案三:子查詢更新 (相關子查詢)

UPDATE student s
SET city_name = (SELECT name FROM city WHERE code = s.city_code);
  • 語法簡潔,符合直覺
  • 風險:子查詢返回多行報錯 (ERROR 1242),需確保關聯鍵唯一
  • 效能:大表時可能退化為逐行執行子查詢 (Dependent Subquery),遠慢於 JOIN
  • 適用於小表、一次性腳本、邏輯極簡場景

效能對比測試 (100萬行 student, 1萬行 city)

方案執行時間Explain 關鍵信息
JOIN 單欄位0.85stype=ref, key=idx_city_code, rows=100
JOIN 多欄位0.92s同左,單次掃描
子查詢12.4stype=ALL, Dependent Subquery, rows=1000000

進階技巧:條件更新

-- 僅更新 city_name 為 NULL 的記錄
UPDATE student s
JOIN city c ON s.city_code = c.code
SET s.city_name = c.name
WHERE s.city_name IS NULL;

避坑指南

  • 更新前必備 SELECT 驗證:SELECT s.*, c.name FROM student s JOIN city c ON s.city_code=c.code WHERE s.city_name IS NULL;
  • 大表分批更新:WHERE s.id BETWEEN 1 AND 10000 循環,避免長事務鎖表
  • 備份策略:更新前 CREATE TABLE student_bak AS SELECT * FROM student; 或 Binlog 閃回準備
  • 外鍵約束:若 city_code 有 FK,更新父表需級聯或先刪除 FK

總結:優先用 JOIN 更新,語義明確、效能穩定、支援多欄位;子查詢僅限一次性小表操作。生產環境跨表更新務必「先查後改、分批執行、可回滾」。

copyright © 萬盛學電腦網 all rights reserved