跨表插入
全量複製（結構相同）
INSERT INTO `table_A` SELECT * FROM `table_B`;
指定欄位插入（結構不同或僅需部分欄位）
INSERT INTO `table_A` (`id`, `name`, `email`)
SELECT `user_id`, `username`, `mail` FROM `table_B`;
避免重複：INSERT IGNORE / ON DUPLICATE KEY UPDATE
-- 忽略重複主鍵/唯一鍵錯誤
INSERT IGNORE INTO `table_A` SELECT * FROM `table_B`;
-- 衝突時更新指定欄位
INSERT INTO `table_A` (`id`, `name`, `cnt`)
SELECT `id`, `name`, 1 FROM `table_B`
ON DUPLICATE KEY UPDATE `cnt` = `cnt` + 1;
跨表更新
單表關聯更新（內連接語法，效能優於 WHERE 笛卡兒積）
UPDATE `table_A` a
INNER JOIN `table_B` b ON a.`id` = b.`id`
SET a.`name` = b.`name`, a.`email` = b.`mail`;
多表關聯更新
UPDATE `table_A` a
INNER JOIN `table_B` b ON a.`id` = b.`user_id`
INNER JOIN `table_C` c ON b.`dept_id` = c.`id`
SET a.`dept_name` = c.`name`;
LEFT JOIN 更新（保留左表所有行，無匹配設為 NULL/預設值）
UPDATE `table_A` a
LEFT JOIN `table_B` b ON a.`id` = b.`id`
SET a.`ext_info` = COALESCE(b.`info`, 'default');
跨表刪除
依關聯條件刪除目標表資料
DELETE a FROM `table_A` a
INNER JOIN `table_B` b ON a.`id` = b.`ref_id`
WHERE b.`status` = 'deleted';
多表聯刪（同時刪除兩表匹配行）
DELETE a, b FROM `table_A` a
INNER JOIN `table_B` b ON a.`id` = b.`id`
WHERE a.`create_time` < '2020-01-01';
進階技巧與避坑指南
| 場景 | 建議寫法 | 注意事項 |
|---|
| 大量資料跨表操作 | 分批處理（LIMIT 1000/5000）+ 迴圈 | 避免長事務鎖表、Undo Log 膨脹、Replication 延遲 |
| 更新/刪除無索引欄位關聯 | 先建立臨時索引或改用 EXISTS 子查詢 | 全表掃描鎖定大量行，嚴重阻塞併發 |
| 需保證原子性 | 啟動事務 `START TRANSACTION` ... `COMMIT` | 跨表操作默認自動提交，若需回滾必須顯式事務 |
| 主從同步環境 | 避免非確定性函數（NOW(), RAND()） | 確保主從資料一致 |
| 外鍵約束存在 | 按父→子順序刪除，或暫時 `SET FOREIGN_KEY_CHECKS=0` | 謹慎關閉檢查，務必在事務內完成並重新開啟 |
實戰範例：同步兩張表的用戶狀態
-- 1. 建立臨時表暫存目標狀態（避免重複掃描）
CREATE TEMPORARY TABLE tmp_user_status AS
SELECT u.`id`, u.`status` FROM `users` u WHERE u.`last_login` > DATE_SUB(NOW(), INTERVAL 30 DAY);
-- 2. 批量更新目標表
UPDATE `user_profiles` p
INNER JOIN tmp_user_status t ON p.`user_id` = t.`id`
SET p.`status` = t.`status`, p.`updated_at` = NOW();
-- 3. 清理
DROP TEMPORARY TABLE tmp_user_status;
總結
- 跨表 DML 核心語法：`INSERT ... SELECT`、`UPDATE ... JOIN ... SET`、`DELETE ... JOIN ... WHERE`
- 優先使用 `INNER JOIN` / `LEFT JOIN` 明確關聯關係，避免舊式 `WHERE` 笛卡兒積語法。
- 大數據量務必分批、加索引、控制事務大小，防止鎖衝突與主從延遲。
- 操作前 `EXPLAIN` 驗證執行計畫，確認走索引而非全表掃描。
