MySQL INSERT 插入操作三大實用技巧：跨表複製、忽略重複、衝突更新

INSERT 不僅是「插入新行」，配合 SELECT、IGNORE、ON DUPLICATE KEY UPDATE 能解決跨表同步、幂等導入、計數器維護等高頻場景。本文通過實戰範例講解三大技巧的語法、適用條件、效能差異與避坑點。

技巧一：INSERT ... SELECT 跨表複製/轉換

-- 基本語法：將來源表查詢結果直接寫入目標表
INSERT INTO target_table (col1, col2, col3)
SELECT src_col1, src_col2, src_col3
FROM source_table
WHERE condition;

-- 實戰：同步訂單歸檔表（僅同步已完成訂單）
INSERT INTO orders_archive (order_id, cust_id, amount, status, created_at)
SELECT id, cust_id, amount, status, created_at
FROM orders
WHERE status = 'COMPLETED' AND created_at < '2024-01-01';

-- 實戰：異構表結構轉換（字段映射、計算欄位、常數填充）
INSERT INTO dw_fact_sales (date_key, product_key, store_key, sales_amt, qty)
SELECT DATE_FORMAT(o.created_at, '%Y%m%d'), p.dw_key, s.dw_key, oi.amount, oi.qty
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN dim_product p ON oi.product_id = p.id
JOIN dim_store s ON o.store_id = s.id
WHERE o.status = 'COMPLETED';

效能建議：大數據量分批次（LIMIT 10000 循環）、來源表加適當索引、目標表暫時移除非唯一索引加速寫入、事務批量提交。

技巧二：INSERT IGNORE 靜默忽略重複鍵衝突

-- 唯一鍵衝突時不報錯，跳過該行，繼續執行後續行
INSERT IGNORE INTO users (user_id, email, name)
SELECT user_id, email, name FROM staging_users;

-- 對比：普通 INSERT 遇到重複鍵直接報錯中斷，整批失敗
-- INSERT IGNORE 返回警告而非錯誤，可用 SHOW WARNINGS 查看跳過詳情

適用場景：幂等導入、日誌去重、增量同步不關心重複行。

⚠️ 副作用：除重複鍵外的其他錯誤（如外鍵失敗、欄位過長、NOT NULL 違規）也會被忽略並轉為警告，可能掩蓋數據質量問題。生產環境建議搭配 ON DUPLICATE KEY UPDATE 顯式處理。

技巧三：ON DUPLICATE KEY UPDATE 衝突轉更新

-- 唯一鍵衝突時，執行 UPDATE 邏輯（原子性，無競態）
INSERT INTO counters (counter_key, cnt, updated_at)
VALUES ('page_view:/home', 1, NOW())
ON DUPLICATE KEY UPDATE cnt = cnt + 1, updated_at = NOW();

-- 多行批量衝突更新
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (1001, 1, NOW()), (1002, 1, NOW())
ON DUPLICATE KEY UPDATE login_count = login_count + VALUES(login_count), last_login = VALUES(last_login);

-- 實戰：從緩存同步計數器到資料庫（定時任務）
INSERT INTO article_stats (article_id, view_count, like_count, updated_at)
SELECT article_id, view_count, like_count, NOW() FROM redis_sync_buffer
ON DUPLICATE KEY UPDATE view_count = view_count + VALUES(view_count), like_count = like_count + VALUES(like_count), updated_at = VALUES(updated_at);

三大技巧對比總結

避坑指南

1. 自增主鍵燒盡：INSERT IGNORE 和 ON DUPLICATE KEY UPDATE 遇到重複鍵仍會消耗自增 ID（InnoDB 預分鎖機制），高併發長期運行導致 ID 耗盡。解決：用 BIGINT 或業務主鍵替代自增。
2. 觸發器雙重執行：ON DUPLICATE KEY UPDATE 會觸發 BEFORE INSERT、BEFORE UPDATE、AFTER INSERT、AFTER UPDATE 四個觸發器，邏輯需冪等。
3. VALUES() 函數誤用： ON DUPLICATE KEY UPDATE col = VALUES(col) 取的是 VALUES 列表中該列的值，而非舊行值。需區分 col = col + 1（舊值+1）與 col = VALUES(col) + 1（新值+1）。
4. 唯一索引缺失：三大技巧均依賴 PRIMARY KEY 或 UNIQUE INDEX 判定衝突，無唯一約束則視為不衝突直接插入新行。

掌握這三大 INSERT 變體，能用純 SQL 解決 80% 的數據同步、導入、計數維護需求，減少應用層邏輯複雜度，提升系統穩定性。