批量插入数据实现SQL语句

terry 2年前 (2023-09-30) 阅读数 42 #PHP

文章标签 PHP

1.什么是批量插入数据？

批量插入数据是指一次性向数据库中插入多条数据记录。相比于一条条记录的插入，批量插入可以提高插入效率，减轻数据库的压力。

以常见的业务场景为例，比如批量为一个用户添加多个收货地址。如果一一插入的话，无疑会占用大量的系统资源。然而，使用批量插入技术可以显着减轻数据库的负担。。

2。如何批量插入数据？

批量插入数据常用的方式有两种：使用多条insert语句插入、使用一条insert into语句插入。

1。更多插入语句插入

在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语句进行插入可以进一步提高插入效率。示例如下：

// 声明数据数组
$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语句插入多条数据记录。

3。如何优化数据的批量插入？

虽然使用批量数据插入可以提高数据插入效率，但是当数据量较大或者需要批量插入时，批量插入还需要进一步优化。

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。设置批量大小

当插入数据量比较大时，默认的batch size可能会导致插入失败或缓慢。这种情况下，可以根据当前情况在程序中设置合适的batch size。

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

在上面的代码中，将$batchSize变量设置为batch size，使用loop和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()方法禁用自动提交功能，手动开启事务并执行插入操作，最后手动提交事务并恢复自动提交状态。