快速的往MySQL插入100万条数据，用PHP如何做

云游道人 2025-01-19 966 阅读 0评论

在 PHP 中快速向 MySQL 插入大量数据（例如 100 万条数据），如果每次执行单独的 INSERT 语句，性能会非常差。

为了解决这个问题，我们可以通过以下几种方式来优化插入操作：

1. 批量插入（Batch Insert）

批量插入是最常见的优化方法，能够大大提高插入效率。与其执行 100 万个单独的 INSERT 语句，不如将多条数据组合成一个 INSERT 语句一次性插入。

示例：批量插入（每次插入 1000 条）

<?php
// MySQL 连接设置
$mysqli = new mysqli("localhost", "username", "password", "database");

// 检查连接
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// 准备数据（这里我们生成100万条数据）
$data = [];
for ($i = 1; $i <= 1000000; $i++) {
    $data[] = "('Name $i', 'email$i@example.com')";
}

// 批量插入，每次插入1000条数据
$batchSize = 1000;
$totalRecords = count($data);
for ($i = 0; $i < $totalRecords; $i += $batchSize) {
    $batchData = array_slice($data, $i, $batchSize);
    $sql = "INSERT INTO users (name, email) VALUES " . implode(", ", $batchData);
    
    if ($mysqli->query($sql) === TRUE) {
        echo "Batch $i to " . ($i + $batchSize - 1) . " inserted successfully\n";
    } else {
        echo "Error: " . $sql . "\n" . $mysqli->error;
    }
}

$mysqli->close();
?>

解析：

生成了 100 万条数据，每 1000 条数据构建一次 INSERT 语句进行插入。
通过 implode(", ", $batchData) 将多条 INSERT 值连接成一个大 INSERT 语句，这样可以减少数据库的执行次数，从而提高性能。

2. 禁用 SQL 事务提交（关闭自动提交）

如果使用事务管理，可以通过关闭自动提交（autocommit），减少每个操作的提交次数，提高插入效率。

示例：禁用自动提交

<?php
// MySQL 连接设置
$mysqli = new mysqli("localhost", "username", "password", "database");

// 检查连接
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// 禁用自动提交
$mysqli->autocommit(FALSE);

$data = [];
for ($i = 1; $i <= 1000000; $i++) {
    $data[] = "('Name $i', 'email$i@example.com')";
}

$batchSize = 1000;
$totalRecords = count($data);
for ($i = 0; $i < $totalRecords; $i += $batchSize) {
    $batchData = array_slice($data, $i, $batchSize);
    $sql = "INSERT INTO users (name, email) VALUES " . implode(", ", $batchData);
    
    if ($mysqli->query($sql) === TRUE) {
        echo "Batch $i to " . ($i + $batchSize - 1) . " inserted successfully\n";
    } else {
        echo "Error: " . $sql . "\n" . $mysqli->error;
    }
}

// 提交事务
$mysqli->commit();

// 关闭连接
$mysqli->close();
?>

解析：

通过禁用自动提交并使用事务（$mysqli->autocommit(FALSE)），在插入大量数据时避免每次插入后都进行一次提交操作。
在所有数据插入完成后，调用 $mysqli->commit() 提交事务，这可以显著提高插入速度。

3. 使用 `LOAD DATA INFILE`

如果你可以先将数据导出到一个 CSV 文件中，使用 LOAD DATA INFILE 是最快的插入方式之一。通过这个方法，MySQL 可以直接从文件中读取数据并插入到表中，效率非常高。

示例：使用 `LOAD DATA INFILE`

<?php
// MySQL 连接设置
$mysqli = new mysqli("localhost", "username", "password", "database");

// 检查连接
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// 生成 CSV 文件
$filename = '/path/to/data.csv';
$file = fopen($filename, 'w');

// 写入列标题
fputcsv($file, ['name', 'email']);

for ($i = 1; $i <= 1000000; $i++) {
    fputcsv($file, ["Name $i", "email$i@example.com"]);
}

fclose($file);

// 使用 LOAD DATA INFILE 插入数据
$sql = "LOAD DATA INFILE '$filename' INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' (name, email)";
if ($mysqli->query($sql) === TRUE) {
    echo "Data inserted successfully!";
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();
?>