批量插入数据 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 < count($array); $i += $batchSize) {
    $values = array_slice($array, $i, $batchSize);
    $sql = "INSERT INTO `fruit` (`name`, `amount`) VALUES ";
    foreach ($values as $value) {
        $sql .= "('" . $value[0] . "', '" . $value[1] . "'),";
    }
    $sql = rtrim($sql, ',');
    $stmt = $pdo->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 语句进行批量插入数据的方法，并从使用事务、设置批量大小和关闭自动提交三个方面进行了扩展和优化。