SQL INSERT INTO 完整語法與高效寫入實戰

INSERT 是資料寫入核心。掌握單行、多行、批次、選擇性插入等寫法，能大幅提升匯入效能。

基本語法

指定欄位插入 (推薦、防結構變更報錯)

INSERT INTO table_name (col1, col2, col3) VALUES (val1, val2, val3);

全欄位插入 (須按建表順序、不推薦生產用)

INSERT INTO table_name VALUES (val1, val2, val3);

多行批次插入 (效能關鍵！)

標準 SQL 多值列 (MySQL/PostgreSQL/SQLite/SQL Server 2008+)

INSERT INTO usertable (user_id, user_name) VALUES
(1, 'dsf'), (2, 'fgy'), (3, 'faad'), (4, 'test'), (5, 'demo');
-- 一條語句插入 5 筆，網路往返 1 次

效能對比：迴圈單筆 vs 批次

方式	10 萬筆耗時	網路往返	交易開銷
迴圈單筆 INSERT	~30 秒	10 萬次	極大
批次 1000 筆/條	~0.8 秒	100 次	小
單條 10 萬值列	~0.5 秒	1 次	最小 (但超過 max_allowed_packet 風險)

程式碼層面批次組裝範例

PHP + MySQLi

$batchSize = 1000;
$values = [];
foreach ($data as $row) {
    $values[] = "('" . $mysqli->real_escape_string($row['name']) . "', " . (int)$row['age'] . ")";
    if (count($values) >= $batchSize) {
        $sql = "INSERT INTO users (name, age) VALUES " . implode(',', $values);
        $mysqli->query($sql);
        $values = [];
    }
}
if ($values) { $mysqli->query("INSERT INTO users (name, age) VALUES " . implode(',', $values)); }

Python + SQLAlchemy 批次

session.bulk_insert_mappings(User, [
    {'name': 'A', 'age': 20}, {'name': 'B', 'age': 21}, ...
])
session.commit()

INSERT ... SELECT 跨表複製

-- 複製表結構+資料
CREATE TABLE new_table AS SELECT * FROM old_table;

-- 指定欄位複製
INSERT INTO target_table (col1, col2)
SELECT source_col1, source_col2 FROM source_table WHERE condition;

-- 分表歸檔
INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < '2023-01-01';

進階語法

INSERT IGNORE (忽略重複錯誤)

INSERT IGNORE INTO users (email, name) VALUES ('a@b.com', 'Alice');
-- 唯一鍵衝突時靜默忽略，不報錯、不插入

REPLACE INTO (衝突時刪舊插新)

REPLACE INTO users (id, email, name) VALUES (1, 'a@b.com', 'Alice New');
-- 主鍵/唯一鍵衝突：DELETE 舊行 + INSERT 新行
-- ⚠️ 觸發 DELETE+INSERT 觸發器、外鍵級聯、自增 ID 變更

ON DUPLICATE KEY UPDATE (Upsert 推薦)

INSERT INTO users (email, name, login_count) VALUES ('a@b.com', 'Alice', 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1, name = VALUES(name);
-- 衝突時更新，保留主鍵 ID，單條語句原子性

大量匯入優化清單

• 關閉自動提交：SET autocommit=0; 批次結束 COMMIT;
• 關閉唯一檢查：SET unique_checks=0; (匯入後開啟)
• 關閉外鍵檢查：SET foreign_key_checks=0;
• 關閉 binlog：SET sql_log_bin=0; (需 SUPER 權限)
• 調大：max_allowed_packet=100M, innodb_log_file_size=1G
• 使用 LOAD DATA INFILE 替代 INSERT (最快，需檔案在 Server 端)

常見錯誤

錯誤	原因	解決
Column count doesn't match	欄位數/順序不匹配	顯式指定欄位列表
Data too long	VARCHAR 長度不足	調整表結構或截斷資料
Duplicate entry	主鍵/唯一鍵衝突	用 IGNORE/REPLACE/IODKU
Packet too large	批次太大超過 max_allowed_packet	減小批次或調大參數