跨表欄位複製 (UPDATE + JOIN)

場景：依主鍵/唯一鍵對應更新

-- 方式 1: INNER JOIN (最常用，有匹配才更新)
UPDATE target t
INNER JOIN source s ON t.id = s.id
SET t.col = s.col;

-- 方式 2: LEFT JOIN + WHERE 過濾 (同步缺失資料)
UPDATE target t
LEFT JOIN source s ON t.id = s.id
SET t.col = s.col
WHERE s.id IS NOT NULL; -- 或其他條件

-- 方式 3: 子查詢 (直觀但大表效能較差)
UPDATE target t
SET t.col = (SELECT s.col FROM source s WHERE s.id = t.id)
WHERE EXISTS (SELECT 1 FROM source s WHERE s.id = t.id);

場景：非主鍵關聯 (如名稱對應)

UPDATE folder f1
LEFT JOIN folder f2 ON f1.name = f2.name AND f2.parent_id = 54
SET f1.parent_id = 54
WHERE f2.name IS NULL AND f1.id IN (1,2,3);

同表欄位複製

-- 全量複製
UPDATE article SET col_b = col_a;

-- 條件複製
UPDATE tb_1 SET content_target = content_source WHERE status = 1;

-- 交換兩欄值 (需臨時變數或多步)
UPDATE t SET a = b, b = a; -- MySQL 支援同步賦值，以舊值計算

插入新行並複製欄位 (INSERT ... SELECT)

-- 複製結構+資料
CREATE TABLE new_tbl LIKE old_tbl;
INSERT INTO new_tbl SELECT * FROM old_tbl;

-- 部分欄位複製並轉換
INSERT INTO target (id, name, created_at)
SELECT id, CONCAT('prefix_', name), NOW() FROM source;

大數據量分批處理模板

-- 避免長鎖表、大事務、Replication 延遲
SET @batch_size = 10000;
SET @last_id = 0;

WHILE TRUE DO
  UPDATE target t
  INNER JOIN (
    SELECT id, col FROM source
    WHERE id > @last_id
    ORDER BY id LIMIT @batch_size
  ) s ON t.id = s.id
  SET t.col = s.col;

  SET @last_id = (SELECT MAX(id) FROM source WHERE id > @last_id LIMIT 1);
  IF ROW_COUNT() = 0 THEN LEAVE; END IF;
  -- 可選 SLEEP(0.1) 降低主庫壓力
END WHILE;

效能與安全檢查清單

• ✅ 關聯欄位有索引 (主鍵/唯一/普通)
• ✅ 子查詢確保單行返回 (LIMIT 1 或唯一鍵)
• ✅ WHERE EXISTS 避免 NULL 覆蓋
• ✅ 先 SELECT 驗證影響行數
• ✅ 生產環境開啟 binlog_format=ROW 方便回滾
• ✅ 大表分批、低峰期執行、監控複製延遲