MySQL 跨表查詢、更新、刪除實戰範例

跨表插入 (INSERT ... SELECT)

-- 全欄位複製
INSERT INTO table_A SELECT * FROM table_B;

-- 指定欄位複製 (推薦，避免結構變更報錯)
INSERT INTO table_A (id, name, email)
SELECT id, username, email FROM table_B WHERE status = 1;

跨表更新 (多表 UPDATE)

-- 將 table_B 的 name 同步到 table_A (依 id 關聯)
UPDATE table_A a
JOIN table_B b ON a.id = b.id
SET a.name = b.name, a.updated_at = NOW();

-- 別名簡寫版
UPDATE table_A, table_B
SET table_A.name = table_B.name
WHERE table_A.id = table_B.id;

跨表刪除 (多表 DELETE)

-- 刪除 table_A 中在 table_B 不存在的資料 (反向同步)
DELETE a FROM table_A a
LEFT JOIN table_B b ON a.id = b.id
WHERE b.id IS NULL;

-- 刪除兩表都存在的資料
DELETE a, b FROM table_A a
JOIN table_B b ON a.id = b.id
WHERE a.status = 0;

實戰場景：資料同步腳本範例

-- 1. 新增/更新 (UPSERT)
INSERT INTO target_table (id, name, data, version)
SELECT id, name, data, version FROM source_table
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    data = VALUES(data),
    version = version + 1;

-- 2. 刪除目標端多餘資料
DELETE t FROM target_table t
LEFT JOIN source_table s ON t.id = s.id
WHERE s.id IS NULL;

效能提醒

• 確保關聯欄位有索引
• 大批次操作分塊 (每次 1000-5000 行)
• 避免長事務，設定 innodb_lock_wait_timeout
• 主從環境注意複製延遲，必要時 SET SESSION sql_log_bin=0