批量插入數據 SQL 語句實現

一、什麼是批量插入數據？

批量插入數據是指一次性向資料庫中插入多條數據記錄，相比於單條記錄一個一個插入，批量插入可以提高插入效率，減少資料庫的壓力。

以常見的業務場景為例，例如向某個用戶批量添加多條收貨地址，如果一條一條地插入，無疑會佔用很大的系統資源，而使用批量插入技術則能夠大大減輕資料庫負擔。

二、如何實現批量插入數據？

常見實現批量插入數據的方式有兩種：使用多個 insert 語句進行插入，使用一條 insert into 語句進行插入。

1. 多個 insert 語句插入

在 PHP 中，可以使用 for 循環或 foreach 循環一條一條地向資料庫插入數據，如下所示：

$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$array = [
    ['apple', 10],
    ['orange', 20],
    ['banana', 30],
];
foreach ($array as $value) {
    $stmt = $pdo->prepare("INSERT INTO `fruit` (`name`, `amount`) VALUES (?, ?)");
    $stmt->execute($value);
}

上述代碼實現了一條一條地向資料庫中插入數據，其中 $array 為待插入的數據數組。

2. 一條 insert into 語句插入

使用一條 insert into 語句進行插入可以進一步提高插入效率，示例如下：

// 聲明數據數組
$array = [
    ['apple', 10],
    ['orange', 20],
    ['banana', 30],
];
// 拼接 SQL 語句
$sql = "INSERT INTO `fruit` (`name`, `amount`) VALUES ";
foreach ($array as $value) {
    $sql .= "('" . $value[0] . "', '" . $value[1] . "'),";
}
$sql = rtrim($sql, ',');
// 執行 SQL 語句
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$stmt = $pdo->prepare($sql);
$stmt->execute();

上述代碼通過將多個數據記錄拼接成一個 SQL 語句，實現了一條 SQL 語句插入多條數據記錄。

三、如何優化批量插入數據？

雖然使用批量插入數據可以提高數據插入效率，但是在數據量較大或者需要頻繁進行批量插入的時候，還需要進一步對批量插入進行優化。

1. 使用事務

在執行批量插入的時候，很可能會出現其中某個數據插入失敗的情況，此時需要將插入操作放在一個事務中，確保數據的完整性和一致性。

try {
    $pdo->beginTransaction();
    foreach ($array as $value) {
        $stmt = $pdo->prepare("INSERT INTO `fruit` (`name`, `amount`) VALUES (?, ?)");
        $stmt->execute($value);
    }
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
}

上述代碼中，使用 beginTransaction() 和 commit() 方法開啟和提交一個事務，並在插入過程中捕獲異常並使用 rollBack() 方法進行回滾。

2. 設置批量大小

當插入數據量比較大時，默認的批量大小可能會導致插入失敗或者插入比較慢，此時可以在程序中根據實際情況設置合適的批量大小。

// 聲明批量大小
$batchSize = 500;
for ($i = 0; $i prepare($sql);
    $stmt->execute();
}

上述代碼中，設置 $batchSize 變數為批量大小，使用 for 循環和 array_slice() 方法將數據分塊處理，最終將分塊後的數據插入資料庫。

3. 關閉自動提交

如果資料庫在每次插入操作時都進行自動提交，可能會導致插入操作比較緩慢，此時可以關閉自動提交功能，手動控制事務的提交與回滾。

// 關閉自動提交
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);
$pdo->beginTransaction();
// 執行插入
$stmt = $pdo->prepare($sql);
$stmt->execute();
// 提交事務
$pdo->commit();
// 恢復自動提交
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 1);

上述代碼中，首先使用 setAttribute() 方法關閉自動提交功能，手動開啟一個事務並進行插入操作，最後手動提交事務並恢復自動提交狀態。

四、總結

批量插入數據是一種提高數據插入效率的重要策略，在開發過程中需要靈活應用，並結合實際情況進行多種優化。

本文介紹了使用多個 insert 語句和一條 insert into 語句進行批量插入數據的方法，並從使用事務、設置批量大小和關閉自動提交三個方面進行了擴展和優化。