INSERT ... SELECT：高效數據遷移與複製
語法
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
INTO tbl_name [ (col_name,...) ]
SELECT ... FROM ... WHERE ...
[ON DUPLICATE KEY UPDATE col=VALUES(col), ...];
核心優勢
- 單條 SQL 完成「查詢 + 插入」，避免應用層往返
- 优化器可產生流水線執行計畫，大數據量比應用層迴圈快數量級
- 支援
IGNORE 跳過主鍵/唯一鍵衝突、ON DUPLICATE KEY UPDATE 覆蓋更新
實戰範例
-- 1. 表間全量複製 (結構相同)
INSERT INTO user_archive SELECT * FROM users WHERE created_at < '2023-01-01';
-- 2. 列映射複製 (結構不同)
INSERT INTO rpt_daily_stats (stat_date, user_cnt, order_amt)
SELECT DATE(created_at), COUNT(*), SUM(amount)
FROM orders WHERE status = 'paid' GROUP BY DATE(created_at);
-- 3. 建表即複製 (CTAS 替代)
CREATE TABLE tmp_high_value_users AS
SELECT * FROM users WHERE total_amount > 10000;
-- 4. 衝突處理：同步維度表
INSERT INTO dim_product (id, name, price, updated_at)
SELECT id, name, price, NOW() FROM staging_product
ON DUPLICATE KEY UPDATE name=VALUES(name), price=VALUES(price), updated_at=VALUES(updated_at);
效能關鍵點
- 目標表**無索引**時插入最快 (批量載入後再建索引)
- 大批量 (＞10萬行) 分批提交，避免 Undo Log 膨脹、鎖升級
LOW_PRIORITY 延遲至無讀鎖、HIGH_PRIORITY 跳過寫鎖等待 (慎用)
多表 UPDATE：關聯更新的標準寫法
語法 (MySQL 專有擴展)
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col1=expr1, col2=expr2, ...
WHERE where_condition;
-- table_references 可為 JOIN 語法
UPDATE t1 INNER JOIN t2 ON t1.id = t2.t1_id
SET t1.status = t2.new_status, t1.updated_at = NOW()
WHERE t2.source = 'sync';
實戰範例
-- 1. 內連接更新：同步訂單狀態到用戶表
UPDATE users u
INNER JOIN (
SELECT user_id, COUNT(*) AS cnt FROM orders WHERE status='paid' GROUP BY user_id
) o ON u.id = o.user_id
SET u.order_count = o.cnt, u.last_order_at = (SELECT MAX(created_at) FROM orders WHERE user_id=u.id AND status='paid');
-- 2. 左連接更新：補齊缺失維度
UPDATE fact_sales f
LEFT JOIN dim_product p ON f.product_id = p.id
SET f.product_name = COALESCE(p.name, 'Unknown'), f.category_id = COALESCE(p.category_id, 0)
WHERE f.product_name IS NULL OR f.category_id IS NULL;
-- 3. 子查詢更新 (效能較差，慎用)
UPDATE users SET vip_level = (
SELECT CASE WHEN total_amount > 10000 THEN 3 WHEN total_amount > 1000 THEN 2 ELSE 1 END
FROM (SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE status='paid' GROUP BY user_id) o
WHERE o.user_id = users.id
);
避坑指南
- 別名在 SET 中不可用：
UPDATE t1 AS a JOIN t2 AS b ... SET a.col = b.col — MySQL 不支援 SET 子句用別名，需用完整表名/別名前綴
- WHERE 條件精確： 缺少 WHERE 將全表更新，生產必加
LIMIT 或先 SELECT 驗證影響行數
- 主從複制： 多表 UPDATE 基於語句複制 (STATEMENT) 可能在從庫執行順序不同導致不一致，**強制 ROW 格式**或拆分為單表更新
REPLACE INTO：主鍵衝突時「刪除舊 + 插入新」
語法與行為
REPLACE [INTO] tbl_name [ (col_list) ] VALUES (val_list) / SELECT ...
-- 等價於：
-- IF EXISTS (SELECT 1 FROM tbl WHERE pk = new_pk) THEN DELETE ...; INSERT ...;
與 INSERT ... ON DUPLICATE KEY UPDATE 對比
| 特性 | REPLACE INTO | INSERT ... ON DUPLICATE KEY UPDATE |
|---|
| 語義 | 刪除舊行 + 插入新行 | 更新現有行 |
| 自增 ID | **改變** (新行新 ID) | **保持** (原行 ID) |
| 外鍵引用 | **破壞** (舊行刪除觸發 CASCADE/RESTRICT) | **安全** (行不變) |
| 觸發器 | 觸發 DELETE + INSERT | 觸發 UPDATE |
| 效能 | 雙倍寫入開銷 | 單次更新開銷 |
| 適用場景 | 全行替換、不在乎 ID/外鍵 | 部分列更新、保持身份 |
實戰選型
-- 正確：維度表全量同步 (無外鍵引用、ID 無業務含義)
REPLACE INTO dim_date (date_key, full_date, year, month, day) VALUES ...;
-- 錯誤：用戶表/訂單表 (有外鍵、ID 有業務含義)
-- 必須用 INSERT ... ON DUPLICATE KEY UPDATE
綜合效能對比
| 操作 | 適用場景 | 大數據量策略 | 複制安全性 |
|---|
INSERT ... SELECT | 數據遷移、歸檔、報表生成、全量同步 | 分批 (1萬/批)、目標表暫無索引、LOAD DATA 更快 | ROW 格式安全、STATEMENT 需無確定性函數 |
多表 UPDATE | 關聯同步、維度補齊、狀態流轉 | 分批、確保 JOIN 列有索引、避免子查詢 | **強制 ROW 格式**、或拆單表 |
REPLACE INTO | 全行覆蓋、無外鍵依賴、ID 可變 | 慎用大批量、觸發 DELETE+INSERT 雙倍 Binlog | ROW 格式安全 |
INSERT ... ON DUPLICATE KEY UPDATE | 增量同步、計數器、狀態機、冪等寫入 | 單行/小批量極快、大批量分批 | ROW 格式安全 |
結語
MySQL 特殊語法是「寫少讀多、避免應用層迴圈」的利器。**INSERT...SELECT** 承擔大數據搬運、**多表 UPDATE** 解決關聯同步、**ON DUPLICATE KEY UPDATE** 守護冪等寫入、**REPLACE INTO** 僅限無外鍵全行替換。掌握各自效能特徵、複制安全性、觸發器行為、自增 ID 語義差異，並在代碼審查中強制要求「大批量分批、WHERE 條件驗證、ROW 複制格式」，即可安全高效落地生產。
