INSERT 是寫入操作核心。本文系統講解單條/批次插入、IGNORE 忽略錯誤、ON DUPLICATE KEY UPDATE 衝突轉更新、大數據導入優化、自增 ID 獲取等生產級用法。
-- 1. 標準顯式列名 (推薦，防結構變更)
INSERT INTO `tbl` (`col1`, `col2`, `col3`) VALUES ('v1', 'v2', 'v3');
-- 2. 批次插入 (高效，單事務)
INSERT INTO `tbl` (`col1`, `col2`) VALUES
('a1', 'b1'), ('a2', 'b2'), ('a3', 'b3');
-- 3. INSERT ... SELECT (表間複製/轉換)
INSERT INTO `target_tbl` (`id`, `name`, `created_at`)
SELECT `user_id`, `username`, NOW() FROM `source_users` WHERE `status`=1;
-- 4. INSERT IGNORE (忽略主鍵/唯一鍵衝突，不報錯)
INSERT IGNORE INTO `tbl` (`uk_col`, `data`) VALUES ('duplicate_key', 'new_data');
-- 衝突行不插入，不報錯，警告計數 +1 (SHOW WARNINGS)
-- 5. REPLACE INTO (衝突時刪除舊行再插新行，觸發 DELETE+INSERT 觸發器)
REPLACE INTO `tbl` (`pk`, `data`) VALUES (1, 'new_value');
-- 等價於：DELETE + INSERT，自增 ID 會改變
-- 6. ON DUPLICATE KEY UPDATE (Upsert 核心，衝突轉更新)
INSERT INTO `tbl` (`uk_col`, `counter`, `updated_at`)
VALUES ('key1', 1, NOW())
ON DUPLICATE KEY UPDATE
`counter` = `counter` + 1,
`updated_at` = NOW();
-- VALUES() 函數引用插入值
INSERT INTO `tbl` (`uk_col`, `val`) VALUES ('k1', 100)
ON DUPLICATE KEY UPDATE `val` = VALUES(`val`) + 10;
VALUES 行數建議 100-1000 行，超大批次拆分，避免 max_allowed_packet 超限、Binlog 過大、鎖持有過久。autocommit=0 手動控制，大幅減少 fsync 開銷。ALTER TABLE tbl DISABLE KEYS (MyISAM) / 導入後重建索引 (InnoDB 5.7+ ALTER TABLE ... ALGORITHM=INPLACE 不支援 DISABLE KEYS，建議先導入再建索引)。
LOAD DATA INFILE '/path/data.csv'
INTO TABLE `tbl`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES
(`col1`, `col2`, `col3`)
SET `created_at` = NOW();
-- 比 INSERT 快 10-20 倍，直接寫頁面、最小化日誌
-- 會話級安全
INSERT INTO `tbl` (`name`) VALUES ('test');
SELECT LAST_INSERT_ID(); -- 返回當前會話最後生成的自增值，併發隔離
-- 批次插入獲取首個 ID
INSERT INTO `tbl` (`name`) VALUES ('a'), ('b'), ('c');
SELECT LAST_INSERT_ID(); -- 返回第一行生成的 ID (連續分配)
-- 存儲過程/觸發器中
SELECT LAST_INSERT_ID() INTO @new_id;
| 陷阱 | 後果 | 解決 |
|---|---|---|
省略列名 INSERT INTO tbl VALUES(...) | 表結構變更 (增/減/調序列) 導致數據錯位或報錯 | 永遠顯式指定列名 |
| 單條循環 INSERT (應用層 for 循環) | 網路往返、事務提交、Binlog 寫入開銷巨大，效能極低 | 批次 VALUES / LOAD DATA / 事務包裹 |
ON DUPLICATE KEY UPDATE 未覆蓋所有列 | 衝突時未更新列保留舊值，可能非預期 | 顯式列出所有需更新列 |
REPLACE INTO 導致自增 ID 跳變、觸發器雙重觸發 | 主鍵引用關係斷裂、審計日誌異常 | 改用 INSERT ... ON DUPLICATE KEY UPDATE |
大批次未分片導致 max_allowed_packet 報錯 | 插入失敗、事務回滾 | 應用層分片 (每批 1MB 內) 或調大參數 |
LOAD DATA INFILE 分批導入 (每批 10-50 萬行)。CREATE INDEX ...)。ANALYZE TABLE 更新統計信息。結語：INSERT 看似簡單，但批次策略、衝突處理、主鍵順序、大數據導入流程直接決定寫入吞吐與數據質量。掌握 ON DUPLICATE KEY UPDATE、LOAD DATA、批次事務三大核心技巧，應對 99% 寫入場景。
