是否可以将 mysqli 准备好的语句与多个插入相结合? [英] Is it possible to combine mysqli prepared statement with multiple inserts?

问题描述

我精通旧的 php mysql 扩展.

I am well-versed in the old php mysql extension.

我正在编写我的第一个使用 mysqli 扩展的脚本.

I am working on my first script that uses the mysqli extension.

我将在动态生成的表中插入大量行.

I am going to be inserting a large number of rows into a table that are being generated dynamically.

是否可以在事先不知道每次插入的新行数的情况下，使用准备好的语句向表中插入多行?

Is it possible to use a prepared statement to insert multiple rows into a table without previously knowing the number of new rows that will be inserted each time?

$stmt   = $mysqli->prepare("INSERT INTO `activity` (`id`, `name`, `type`) VALUES ?, ?, ?;");

如果这是不可能的，那会更有效:

If that isn't possible, which would be more efficient:

1. 准备好的语句，一次一行

2. 非准备语句，一次约 50 行

1. prepared statement, one row at a time

2. non-prepared statement, ~50 rows at a time

// prepared statement

$stmt   = $mysqli->prepare("INSERT INTO `activity` (`id`, `name`, `type`) VALUES (?, ?, ?)");

for($i=0;$i<$limit;$i++)

{

    $stmt->bind_param('iss', $id[$i], $name[$i], $type[$i]);

    $stmt->execute();

}


// non-prepared statement

$query  = "INSERT INTO `activity` (`id`, `name`, `type`) VALUES ";

for($i=0;$i<$limit;$i++)

{

    $query  .= "\n(".$mysqli->real_escape_string($id[$i]), $mysqli->real_escape_string($name[$i]), $mysqli->real_escape_string($type[$i])."),";

}

$query  = substr($query, 0, -1).';';

PHP v.5.3.8

PHP v.5.3.8

MySQL 5.1.60 版

MySQL v. 5.1.60

推荐答案

$stmt = $mysqli->stmt_init();

if($stmt->prepare("INSERT INTO `activity` (`id`, `name`, `type`) VALUES (?, ?, ?)"))
{
   $stmt->bind_param('iss', $_id, $_name, $_type);
   for($i=0;$i<$limit;$i++)
   {
      $_id = $id[$i];
      $_name = $name[$i];
      $_type = $type[$i];
      $stmt->execute();
   }

}

应该为你做！

这篇关于是否可以将 mysqli 准备好的语句与多个插入相结合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！