INSERT 是最基礎也最高頻的寫入操作。MySQL 提供多種語法變體,適用不同場景。本文系統梳理語法細節與效能優化要點。
-- 1. 明確指定欄位 (推薦,防結構變更出錯)
INSERT INTO tbl_name (col1, col2, ...) VALUES (val1, val2, ...);
-- 2. 全欄位插入 (須按建表順序,不推薦生產用)
INSERT INTO tbl_name VALUES (val1, val2, ...);
-- 3. 多列單語句批次插入 (高效,單次網路往返)
INSERT INTO tbl_name (col1, col2) VALUES
(1, 'a'), (2, 'b'), (3, 'c'), ...;
-- 4. INSERT ... SELECT (表間複製/轉換)
INSERT INTO target_tbl (col1, col2)
SELECT colA, colB FROM source_tbl WHERE condition;
-- 5. INSERT ... ON DUPLICATE KEY UPDATE (Upsert 核心)
INSERT INTO tbl (id, val) VALUES (1, 'new')
ON DUPLICATE KEY UPDATE val = VALUES(val), updated_at = NOW();
SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;
-- 大量 INSERT
SET FOREIGN_KEY_CHECKS=1; SET UNIQUE_CHECKS=1;
| 現象 | 原因 | 解法 |
|---|---|---|
| Duplicate entry '0' for key 'PRIMARY' | 自增主鍵耗盡或顯式插入 0 | 檢查主鍵類型 (BIGINT)、重置自增值、避免插入 0 |
| Data truncated for column | 數值超出範圍/字串過長 | 修正數據或調整欄位定義 |
| Lock wait timeout exceeded | 行鎖衝突、長事務 | 縮短事務、優化索引、調大 `innodb_lock_wait_timeout` |
| Packet too large | 批次 SQL 超過 `max_allowed_packet` | 拆分批次或調大參數 (需重啟) |
超過 10 萬行時,`LOAD DATA INFILE` 比批次 INSERT **快 10~20 倍**:
LOAD DATA INFILE '/path/data.csv'
INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES (col1, col2, ...);
需 `FILE` 權限且檔案在伺服器端 (或 `LOCAL` 關鍵字從客戶端讀取)。