MySQL 批量插入

PHP 将多条记录插入 MySQL

高效插入多条记录

当您需要使用 PHP 向 MySQL 表中插入多行时，有几种方法。现代最佳实践优先考虑安全性（防止 SQL 注入）和效率。我们将探讨使用 MySQLi 和 PDO 扩展的方法。

关键考量：

• 安全性 (Security): 始终使用预处理语句（prepared statements）来防止 SQL 注入漏洞。
• 效率 (Efficiency): 对于大量插入，在单个查询中插入多行或使用事务（transactions）比执行单个插入语句快得多。
• 原子性 (Atomicity): 如果所有插入都必须一起成功或失败（例如，资金转账），请使用数据库事务（主要在 PDO 中演示）。

方法 1: 使用预处理语句的 MySQLi（循环）

此方法只准备一次 INSERT 语句，然后使用不同的数据多次执行。它安全且相对直观。

<?php
$servername = "localhost";
$username = "your_username"; // 替换为您的数据库用户名
$password = "your_password"; // 替换为您的数据库密码
$dbname = "myDB";         // 替换为您的数据库名

// 启用 mysqli 错误报告
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    // 创建连接（面向对象风格）
    $conn = new mysqli($servername, $username, $password, $dbname);

    // 准备 SQL 语句（防止 SQL 注入）
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");

    // 要插入的数据
    $guests = [
        ['John', 'Doe', 'john.doe@example.com'],
        ['Mary', 'Moe', 'mary.moe@example.com'],
        ['Julie', 'Dooley', 'julie.dooley@example.com']
    ];

    echo "尝试插入记录...<br>";

    // 为每位访客绑定参数并执行
    foreach ($guests as $guest) {
        $firstname = $guest[0];
        $lastname = $guest[1];
        $email = $guest[2];

        // 将变量绑定到预处理语句作为参数
        // 'sss' 指定参数的类型（字符串，字符串，字符串）
        $stmt->bind_param("sss", $firstname, $lastname, $email);

        // 执行语句
        if ($stmt->execute()) {
            echo "已成功为 $firstname $lastname 创建新记录。<br>";
        }
        // 错误处理主要由以上 mysqli_report 设置覆盖
        // 如果需要，可以在此处添加特定检查。
    }

    echo "记录插入完成。<br>";

    // 关闭语句
    $stmt->close();

} catch (mysqli_sql_exception $e) {
    // 捕获连接或查询错误
    die("数据库错误: " . $e->getMessage() . " (错误码: " . $e->getCode() . ")");
} finally {
    // 如果连接已打开，请务必关闭
    if (isset($conn)) {
        $conn->close();
    }
}
?>

方法 2: 使用事务和预处理语句的 PDO

PDO (PHP Data Objects) 提供了访问各种数据库的一致接口。使用事务可以确保所有插入都被视为一个原子单元——它们要么全部成功，要么在发生错误时全部不保存。

<?php
$servername = "localhost";
$username = "your_username"; // 替换为您的数据库用户名
$password = "your_password"; // 替换为您的数据库密码
$dbname = "myDBPDO";      // 替换为您的数据库名

try {
    // 创建 PDO 连接
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

    // 将 PDO 错误模式设置为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 要插入的数据
    $guests = [
        ['Peter', 'Griffin', 'peter@example.com'],
        ['Lois', 'Griffin', 'lois@example.com'],
        ['Chris', 'Griffin', 'chris@example.com']
    ];

    // 开启事务
    $conn->beginTransaction();
    echo "事务已开启。尝试插入...<br>";

    // 准备 SQL 语句（在循环外以提高效率）
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (:firstname, :lastname, :email)");

    // 为每位访客绑定参数并执行
    foreach ($guests as $guest) {
        $stmt->bindParam(':firstname', $guest[0]);
        $stmt->bindParam(':lastname', $guest[1]);
        $stmt->bindParam(':email', $guest[2]);
        $stmt->execute();
        echo "正在执行 " . htmlspecialchars($guest[0]) . " 的插入...<br>";
    }

    // 如果所有插入都成功，则提交事务
    $conn->commit();
    echo "事务提交成功。所有记录已插入。<br>";

} catch (PDOException $e) {
    // 如果发生错误，则回滚事务
    if ($conn->inTransaction()) {
        $conn->rollBack();
        echo "由于错误导致事务回滚。<br>";
    }
    die("数据库错误: " . $e->getMessage());

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

方法 3: 使用多个 VALUES 的单条 INSERT 语句（MySQLi/PDO）

对于插入已知的中等数量的行，构建一个包含多个值集的单条 INSERT 语句可能是性能最佳的方法。但是，动态构建查询字符串和参数绑定需要谨慎处理。

<?php
// --- 连接设置同前例 (MySQLi 或 PDO) ---
// 本例使用 PDO:
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "myDBPDO";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 要插入的数据
    $guests = [
        ['Stewie', 'Griffin', 'stewie@example.com'],
        ['Brian', 'Griffin', 'brian@example.com'],
    ];

    if (!empty($guests)) {
        // 开始构建查询
        $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ";
        $placeholders = [];
        $values = [];

        // 为每行创建占位符 (?, ?, ?)
        foreach ($guests as $guest) {
            $placeholders[] = '(?, ?, ?)';
            // 将访客数组展平到值数组中
            array_push($values, ...$guest);
        }

        // 组合占位符: (?, ?, ?), (?, ?, ?), ...
        $sql .= implode(', ', $placeholders);

        // 准备语句
        $stmt = $conn->prepare($sql);

        // 使用展平后的值数组执行
        $stmt->execute($values);

        $count = count($guests);
        echo "已成功在单个查询中插入 $count 条记录。<br>";
    } else {
        echo "未提供访客数据。<br>";
    }

} catch (PDOException $e) {
    die("数据库错误: " . $e->getMessage());
} finally {
    $conn = null;
}
?>

选择合适的方法：

• 预处理语句（循环）(Prepared Statements (Loop)): 良好的通用方法，安全，易于理解。
• PDO 事务 (PDO Transactions): 在需要原子性（全部成功或全部失败）时最佳。相较于自动提交模式，也可以提高大量插入的性能。
• 单条多值 INSERT (Single Multi-Value INSERT): 对于已知行数集合通常最快，但需要更复杂的查询构建。

已弃用方法（应避免）：mysqli_multi_query()

mysqli_multi_query() 允许在单个字符串中执行连接的多条 SQL 语句，但强烈不推荐将其用于插入数据，除非您绝对确定所有数据都已正确转义。如果涉及用户输入且未极其谨慎处理，则存在重大的 SQL 注入风险。预处理语句是更安全且通常首选的方法。