PHP MySQL 插入多条数据

在实际应用中，我们经常需要批量插入多条数据到MySQL数据库中。本教程将介绍如何使用PHP高效地向MySQL数据表中插入多条数据，包括MySQLi扩展和PDO（PHP数据对象）两种方式。

为什么需要批量插入？

批量插入多条数据而不是单条插入的主要原因：

性能提升：减少数据库连接和查询次数，显著提高性能
减少网络开销：降低客户端和服务器之间的通信次数
事务处理：可以在单一事务中处理多条记录，确保数据一致性
简化代码：减少重复代码，提高代码可读性

使用MySQLi批量插入数据

MySQLi扩展提供了多种批量插入数据的方法。

1. 使用INSERT INTO ... VALUES (...), (...), (...)语法

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 准备要插入的数据
$data = [
    ["John", "Doe", "john@example.com"],
    ["Mary", "Moe", "mary@example.com"],
    ["Julie", "Dooley", "julie@example.com"]
];

// 构建批量插入的SQL语句
$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ";
$values = [];

foreach ($data as $row) {
    $firstname = $conn->real_escape_string($row[0]);
    $lastname = $conn->real_escape_string($row[1]);
    $email = $conn->real_escape_string($row[2]);
    $values[] = "('$firstname', '$lastname', '$email')";
}

$sql .= implode(', ', $values);

if ($conn->query($sql) === true) {
    $affected_rows = $conn->affected_rows;
    echo "成功插入 " . $affected_rows . " 条记录";
} else {
    echo "错误: " . $sql . "
" . $conn->error;
}

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

2. 使用预处理语句循环执行

对于大量数据，预处理语句是一种更安全、更高效的方法。

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 开始事务
$conn->begin_transaction();

// 准备预处理语句
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");

// 绑定参数
$stmt->bind_param("sss", $firstname, $lastname, $email);

// 准备要插入的数据
$data = [
    ["John", "Doe", "john@example.com"],
    ["Mary", "Moe", "mary@example.com"],
    ["Julie", "Dooley", "julie@example.com"]
];

// 循环执行预处理语句
$success = true;
foreach ($data as $row) {
    $firstname = $row[0];
    $lastname = $row[1];
    $email = $row[2];
    
    if (!$stmt->execute()) {
        $success = false;
        echo "执行错误: " . $stmt->error . "
";
        break;
    }
}

// 根据执行结果提交或回滚事务
if ($success) {
    $conn->commit();
    echo "所有记录已成功插入";
} else {
    $conn->rollback();
    echo "事务回滚，没有记录被插入";
}

// 关闭语句和连接
$stmt->close();
$conn->close();
?>

使用PDO批量插入数据

PDO（PHP数据对象）也提供了多种批量插入数据的方法。

1. 使用INSERT INTO ... VALUES (...), (...), (...)语法

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 尝试连接数据库
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);
    
    // 设置PDO错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 准备要插入的数据
    $data = [
        ["John", "Doe", "john@example.com"],
        ["Mary", "Moe", "mary@example.com"],
        ["Julie", "Dooley", "julie@example.com"]
    ];
    
    // 构建批量插入的SQL语句
    $placeholders = [];
    $params = [];
    $index = 0;
    
    foreach ($data as $row) {
        $rowPlaceholders = [];
        
        foreach ($row as $value) {
            $paramName = ":param_" . $index;
            $rowPlaceholders[] = $paramName;
            $params[$paramName] = $value;
            $index++;
        }
        
        $placeholders[] = "(" . implode(', ', $rowPlaceholders) . ")";
    }
    
    $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES " . implode(', ', $placeholders);
    
    // 准备和执行语句
    $stmt = $conn->prepare($sql);
    $stmt->execute($params);
    
    $affected_rows = $stmt->rowCount();
    echo "成功插入 " . $affected_rows . " 条记录";
} catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}

// 关闭连接
$conn = null;
?>

2. 使用预处理语句和事务

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 尝试连接数据库
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);
    
    // 设置PDO错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 开始事务
    $conn->beginTransaction();
    
    // 准备预处理语句
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (:firstname, :lastname, :email)");
    
    // 准备要插入的数据
    $data = [
        ["John", "Doe", "john@example.com"],
        ["Mary", "Moe", "mary@example.com"],
        ["Julie", "Dooley", "julie@example.com"]
    ];
    
    // 循环执行预处理语句
    foreach ($data as $row) {
        $stmt->execute([
            ':firstname' => $row[0],
            ':lastname' => $row[1],
            ':email' => $row[2]
        ]);
    }
    
    // 提交事务
    $conn->commit();
    $total_rows = count($data);
    echo "成功插入 " . $total_rows . " 条记录";
} catch(PDOException $e) {
    // 发生错误时回滚事务
    $conn->rollback();
    echo "错误: " . $e->getMessage();
}

// 关闭连接
$conn = null;
?>

使用事务处理大量数据

当处理大量数据时，使用事务可以确保数据的一致性，并提高性能。

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 开始事务
$conn->begin_transaction();

// 准备预处理语句
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// 模拟大量数据（例如1000条记录）
$batch_size = 1000;
$success = true;

for ($i = 0; $i < $batch_size; $i++) {
    $firstname = "User_" . $i;
    $lastname = "Lastname_" . $i;
    $email = "user_" . $i . "@example.com";
    
    if (!$stmt->execute()) {
        $success = false;
        echo "执行错误: " . $stmt->error . "
";
        break;
    }
}

// 根据执行结果提交或回滚事务
if ($success) {
    $conn->commit();
    echo "成功插入 " . $batch_size . " 条记录";
} else {
    $conn->rollback();
    echo "事务回滚，没有记录被插入";
}

// 关闭语句和连接
$stmt->close();
$conn->close();
?>

分批次插入大量数据

当需要插入非常大量的数据时（如10万条以上），最好分批次插入，以避免超时和内存问题。

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 设置超时时间（如果需要）
$conn->query("SET GLOBAL max_execution_time = 120;");

// 准备预处理语句
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// 模拟大量数据，分批次插入
$total_records = 100000;  // 总共要插入的记录数
$batch_size = 1000;     // 每批次插入的记录数
$total_batches = ceil($total_records / $batch_size);

for ($batch = 0; $batch < $total_batches; $batch++) {
    // 开始事务
    $conn->begin_transaction();
    
    $success = true;
    $start_index = $batch * $batch_size;
    $end_index = min($start_index + $batch_size, $total_records);
    
    for ($i = $start_index; $i < $end_index; $i++) {
        $firstname = "User_" . $i;
        $lastname = "Lastname_" . $i;
        $email = "user_" . $i . "@example.com";
        
        if (!$stmt->execute()) {
            $success = false;
            echo "批次 " . ($batch + 1) . " 执行错误: " . $stmt->error . "
";
            break;
        }
    }
    
    // 根据执行结果提交或回滚事务
    if ($success) {
        $conn->commit();
        echo "批次 " . ($batch + 1) . " 成功插入，共 " . ($end_index - $start_index) . " 条记录
";
    } else {
        $conn->rollback();
        echo "批次 " . ($batch + 1) . " 回滚，没有记录被插入
";
        break; // 出错时停止后续批次
    }
    
    // 可选：每批次之间暂停一小段时间，减轻服务器压力
    usleep(100000); // 暂停0.1秒
}

// 关闭语句和连接
$stmt->close();
$conn->close();
?>

导入CSV文件批量插入

CSV文件是一种常见的数据交换格式，下面是如何从CSV文件导入数据并批量插入到数据库的示例：

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// CSV文件路径
$csv_file = "data.csv";

// 检查文件是否存在
if (!file_exists($csv_file)) {
    die("CSV文件不存在: " . $csv_file);
}

// 打开CSV文件
$file = fopen($csv_file, "r");

// 跳过表头行（如果有）
fgetcsv($file);

// 准备预处理语句
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// 开始事务
$conn->begin_transaction();

$success = true;
$record_count = 0;

// 读取CSV文件并插入数据
while (($row = fgetcsv($file)) !== false) {
    $firstname = $conn->real_escape_string($row[0]);
    $lastname = $conn->real_escape_string($row[1]);
    $email = $conn->real_escape_string($row[2]);
    
    if (!$stmt->execute()) {
        $success = false;
        echo "插入记录错误: " . $stmt->error . "
";
        break;
    }
    
    $record_count++;
}

// 关闭文件
fclose($file);

// 根据执行结果提交或回滚事务
if ($success) {
    $conn->commit();
    echo "成功从CSV文件导入 " . $record_count . " 条记录";
} else {
    $conn->rollback();
    echo "事务回滚，没有记录被导入";
}

// 关闭语句和连接
$stmt->close();
$conn->close();
?>

批量插入的性能优化

以下是一些提高批量插入性能的技巧：

使用事务：将多条插入语句包装在一个事务中，可以显著提高性能
使用预处理语句：预处理语句可以减少解析时间和防止SQL注入
禁用索引：对于大量数据插入，可以先禁用索引，插入完成后再重建索引
调整innodb_buffer_pool_size：增加MySQL的缓冲池大小可以提高性能
使用LOAD DATA INFILE：对于非常大的数据集，这是最快的导入方法
分批次插入：当插入大量数据时，分批次插入可以避免超时和内存问题
禁用自动提交：在插入数据前禁用自动提交，完成后再启用

<?php
// 禁用自动提交
$conn->autocommit(false);

// 插入数据代码...

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

// 重新启用自动提交
$conn->autocommit(true);
?>

最佳实践

始终使用预处理语句来防止SQL注入
对所有用户输入进行验证和过滤
使用事务来确保数据的一致性
对于大量数据，分批次插入
根据数据量选择合适的批量插入方法
在插入大量数据时，考虑临时禁用索引和外键约束
监控和记录插入过程，以便排查问题
插入完成后验证数据的完整性
在生产环境中测试批量插入的性能和稳定性
考虑使用LOAD DATA INFILE进行非常大的数据集导入