萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 批次 INSERT 多筆資料:單條 SQL 插入千筆,效能提升 90%+ 的正確姿勢

逐筆 INSERT 走網路往返、解析、鎖定、提交,效能極差。MySQL 支援單條 INSERT 多值列表語法,一次插入成百上千筆,效能可提升 10~100 倍。本文詳解正確語法、常見錯誤、PHP 批次組裝範例與大量資料分批策略。

正確語法:多值列表

-- ✅ 正確:每組值用括號包裹,組之間用逗號分隔
INSERT INTO `table` (`name`) VALUES ('A'), ('B'), ('C'), ('D');

-- ❌ 錯誤:VALUES 後直接列出所有值,MySQL 會誤判為單列多欄
INSERT INTO table1 (i) VALUES (1,2,3,4,5);
-- ERROR 1136: Column count doesn't match value count at row 1

語法變體

-- 省略欄位名(值順序與數量必須與表定義完全一致)
INSERT INTO table1 VALUES (1), (2), (3);

-- 指定部分欄位,其餘走預設值
INSERT INTO users (username, email) VALUES 
  ('alice', 'alice@example.com'), 
  ('bob', 'bob@example.com');

PHP 批次組裝最佳實踐

function batchInsert(PDO $pdo, string $table, array $columns, array $rows, int $batchSize = 500): int {
    $colList = '`' . implode('`, `', $columns) . '`';
    $placeholders = '(' . implode(',', array_fill(0, count($columns), '?')) . ')';
    $total = 0;

    foreach (array_chunk($rows, $batchSize) as $chunk) {
        $valuePlaceholders = implode(',', array_fill(0, count($chunk), $placeholders));
        $sql = "INSERT INTO `$table` ($colList) VALUES $valuePlaceholders";
        
        $stmt = $pdo->prepare($sql);
        $params = [];
        foreach ($chunk as $row) {
            foreach ($columns as $col) {
                $params[] = $row[$col] ?? null;
            }
        }
        $stmt->execute($params);
        $total += $stmt->rowCount();
    }
    return $total;
}

// 使用範例
$data = [
    ['username' => 'u1', 'email' => 'u1@test.com'],
    ['username' => 'u2', 'email' => 'u2@test.com'],
    // ... 10000 筆
];
$inserted = batchInsert($pdo, 'users', ['username', 'email'], $data, 1000);

大量資料分批策略

資料量建議批次max_allowed_packet事務策略
< 1 萬500~100016M (預設)單事務
10 萬~100 萬1000~2000調大至 64M~256M分批提交事務
> 100 萬2000~5000256M+分批 + LOAD DATA INFILE

避坑清單

  • max_allowed_packet:單條 SQL 超過此限制會報錯,大批次前必須調大(SET GLOBAL max_allowed_packet=268435456;)
  • 自增 ID 衝突:批次插入會連續分配自增值,LAST_INSERT_ID() 僅回傳第一筆 ID
  • 唯一鍵衝突:使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 處理重複
  • 觸發器:每筆仍會觸發 BEFORE/AFTER INSERT,大批次需評估觸發器開銷

極致效能:LOAD DATA INFILE

超過百萬筆時,LOAD DATA LOCAL INFILE 直接讀檔導入,繞過 SQL 解析層,速度再快 5~10 倍:

LOAD DATA LOCAL INFILE '/path/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(username, email);
批次 INSERT 是提升寫入吞吐最划算的優化:零程式碼重構、零架構變更、立竿見影。記住「括號包值、逗號分組、分批提交、調大封包」八字訣。
copyright © 萬盛學電腦網 all rights reserved