php - 使用 PHP 在一个查询 MySQL 中插入数千行

Question

我已经(再次)尝试了一整天，但在一次查询中插入大约 10k 行时尝试插入总共超过 10 亿行时出错。我想使用准备好的语句对 MySQL 执行此操作，使用 innoDB 表。硬件资源不是问题，问题一直是索引和一行/插入。

不要说我应该直接使用列表方法，因为我需要在插入之前进行一些计算。输入来自文件。

所以我有一个很大的 while 循环，循环文本文件行并且工作正常。我目前通过为每个查询插入一个来做到这一点。

    $stmt = $conn->prepare("INSERT INTO $tableName(row1, row2, row3) VALUES (?, ?, ?)");
    $stmt->bind_param("sss", $str1, $str2, $str3);
    $stmt->execute();

我看过这样的例子:

$stmt = $conn->prepare("INSERT INTO $tableName(row1, row2, row3) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?) ..... ");

但我需要将其构建为单个查询的 5-10k 行。

如果有人以前做过这样的事情，请提供一些引用。

提前致谢!

Answer 1

prepared query 的整个想法是它减少了每次运行时设置查询的开销——准备一次，重复执行。这意味着在循环中运行准备好的语句或传递一个巨大的查询之间不会有很大的差异(从数据库的 POV 来看)。

您没有在此处包含整个代码，但许多人犯的一个错误是在循环中包含语句准备。不要犯那个错误!这样的事情将是最有效的:

// these need a value before being used in bind_param
$str1 = $str2 = $str3 = "";
$stmt = $conn->prepare("INSERT INTO $tableName(row1, row2, row3) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $str1, $str2, $str3);
while ($we_have_data) {
    $str1 = "foo";
    $str2 = "bar";
    $str3 = "baz";
    $stmt->execute();
}

虽然我总是推荐使用 PDO prepared statements ，因为您可以避免绑定(bind)参数的潜在困惑:

// assuming $conn is a PDO object now
$stmt = $conn->prepare("INSERT INTO $tableName(row1, row2, row3) VALUES (?, ?, ?)");
while ($we_have_data) {
    $str1 = "foo";
    $str2 = "bar";
    $str3 = "baz";
    $stmt->execute([$str1, $str2, $str3]);
}