PHP数据库批量插入数据：高效大量数据导入方案
在实际开发中，我们经常需要将大量数据导入到数据库中。如果使用普通的单条插入方式，会频繁地与数据库建立连接和执行SQL语句，导致性能低下。本文将介绍几种PHP数据库批量插入数据的方案，帮助你提高数据导入的效率。
一、普通单条插入的问题
假设我们有一个用户表users，需要插入1000条用户数据。如果使用普通的单条插入方式，代码如下：
// 连接数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'database');
// 准备插入语句
$sql = "INSERT INTO users (name, email) VALUES (?, ?)";
$stmt = mysqli_prepare($conn, $sql);
// 循环插入数据
for ($i = 0; $i < 1000; $i++) {
$name = 'user' . $i;
$email = 'user' . $i . '@ippipp.com';
mysqli_stmt_bind_param($stmt, 'ss', $name, $email);
mysqli_stmt_execute($stmt);
}
// 关闭连接
mysqli_close($conn);这种方式虽然简单，但每次插入都需要执行一次SQL语句，会产生大量的网络开销和数据库操作，效率非常低。
二、使用批量插入语句
大多数数据库都支持批量插入语句，即在一个INSERT语句中插入多条记录。以MySQL为例，可以使用以下语法：
INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), ... (valueN, valueM);
在PHP中，我们可以构建这样的批量插入语句来提高效率。示例代码如下：
// 连接数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'database');
// 准备要插入的数据
$data = [];
for ($i = 0; $i < 1000; $i++) {
$data[] = "('user" . $i . "', 'user" . $i . "@ippipp.com')";
}
// 构建批量插入语句
$values = implode(',', $data);
$sql = "INSERT INTO users (name, email) VALUES $values";
// 执行插入
if (mysqli_query($conn, $sql)) {
echo "批量插入成功";
} else {
echo "批量插入失败: " . mysqli_error($conn);
}
// 关闭连接
mysqli_close($conn);这种方式将多条插入语句合并为一条，减少了网络开销和数据库操作次数，大大提高了插入效率。但需要注意的是，不同的数据库对批量插入的记录数可能有限制，需要根据实际情况进行调整。
三、使用事务提高性能
事务可以确保一组SQL语句要么全部执行成功，要么全部回滚。在批量插入数据时，使用事务可以避免部分数据插入成功而部分失败的情况，同时还能提高插入性能。示例代码如下：
// 连接数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'database');
// 开始事务
mysqli_begin_transaction($conn);
try {
// 准备插入语句
$sql = "INSERT INTO users (name, email) VALUES (?, ?)";
$stmt = mysqli_prepare($conn, $sql);
// 循环插入数据
for ($i = 0; $i < 1000; $i++) {
$name = 'user' . $i;
$email = 'user' . $i . '@ippipp.com';
mysqli_stmt_bind_param($stmt, 'ss', $name, $email);
mysqli_stmt_execute($stmt);
}
// 提交事务
mysqli_commit($conn);
echo "批量插入成功";
} catch (Exception $e) {
// 回滚事务
mysqli_rollback($conn);
echo "批量插入失败: " . $e->getMessage();
}
// 关闭连接
mysqli_close($conn);在这个示例中，我们使用mysqli_begin_transaction()开始一个事务，然后在循环中执行插入操作。如果所有插入都成功，就使用mysqli_commit()提交事务；如果出现异常，就使用mysqli_rollback()回滚事务。这样可以保证数据的一致性，同时也能提高插入性能。
四、分批次插入大数据量
当需要插入的数据量非常大时，一次性构建所有的插入语句可能会导致内存溢出。此时，我们可以将数据分成多个小批次进行插入。示例代码如下：
// 连接数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'database');
// 每批次插入的记录数
$batchSize = 100;
// 总记录数
$totalRecords = 10000;
// 循环插入数据
for ($i = 0; $i < $totalRecords; $i += $batchSize) {
// 准备要插入的数据
$data = [];
for ($j = $i; $j < $i + $batchSize && $j < $totalRecords; $j++) {
$data[] = "('user" . $j . "', 'user" . $j . "@ippipp.com')";
}
// 构建批量插入语句
$values = implode(',', $data);
$sql = "INSERT INTO users (name, email) VALUES $values";
// 执行插入
if (!mysqli_query($conn, $sql)) {
echo "第 " . ($i / $batchSize + 1) . " 批次插入失败: " . mysqli_error($conn);
break;
}
}
// 关闭连接
mysqli_close($conn);在这个示例中，我们将10000条记录分成100条一批，每次插入一批数据。这样可以避免一次性加载过多数据到内存中，减少内存占用。同时，我们还可以根据实际情况调整批次大小，以达到最佳的性能和内存使用平衡。
五、使用LOAD DATA INFILE语句
对于非常大的数据量，使用LOAD DATA INFILE语句是一种更高效的方式。该语句可以直接从文件中读取数据并插入到数据库中，避免了PHP与数据库之间的多次交互。示例代码如下：
// 连接数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'database');
// 创建临时文件
$tempFile = tempnam(sys_get_temp_dir(), 'data');
$fp = fopen($tempFile, 'w');
// 写入数据到临时文件
for ($i = 0; $i < 10000; $i++) {
fwrite($fp, "user" . $i . "\tuser" . $i . "@ippipp.com\n");
}
fclose($fp);
// 构建LOAD DATA INFILE语句
$sql = "LOAD DATA INFILE '$tempFile' INTO TABLE users FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (name, email)";
// 执行插入
if (mysqli_query($conn, $sql)) {
echo "数据导入成功";
} else {
echo "数据导入失败: " . mysqli_error($conn);
}
// 删除临时文件
unlink($tempFile);
// 关闭连接
mysqli_close($conn);在这个示例中，我们首先创建一个临时文件，然后将数据写入该文件。接着，使用LOAD DATA INFILE语句将数据从文件中导入到数据库中。最后，删除临时文件。需要注意的是，使用LOAD DATA INFILE语句可能需要数据库用户具有相应的权限，并且文件路径需要正确配置。
六、总结
本文介绍了几种PHP数据库批量插入数据的方案，包括使用批量插入语句、事务、分批次插入和使用LOAD DATA INFILE语句。在实际应用中，我们可以根据数据量的大小和具体需求选择合适的方案。对于小数据量，可以使用批量插入语句或事务；对于大数据量，可以考虑分批次插入或使用LOAD DATA INFILE语句。同时，我们还需要注意数据库的性能和安全性，避免因大量数据插入导致数据库崩溃或出现安全问题。