如何使用预准备语句一次在mysql数据库中插入多行？

I am trying to use staticsan´s answer in this question for prepared statements. Lets take this example:

我试图在这个问题中使用staticsan的答案来准备陈述。让我们举个例子:

$stmt = $mysqli->prepare("INSERT INTO something (userid, time, title) VALUES (?, ?, ?)");
$stmt->bind_param('iis', $userid, time(), $title);
$stmt->execute();

In staticsan´s answer imploding the array is adding all the values into the mysql statement so that in the end we can insert multiple data into the database with just one statement. How would this be done in my example?

在staticsan的回答中,将数组中的所有值添加到mysql语句中,以便最终我们可以使用一个语句将多个数据插入到数据库中。在我的例子中如何做到这一点?

3 个解决方案

#1

This is completely valid:

这完全有效:

$stmt = $mysqli->prepare("INSERT INTO something (userid, time, title) VALUES (?, ?, ?)");

$stmt->bind_param('iis', $userid, time(), $title);
$stmt->execute();

$stmt->bind_param('iis', $userid, time(), $title);
$stmt->execute();

$stmt->bind_param('iis', $userid, time(), $title);
$stmt->execute();

$stmt->bind_param('iis', $userid, time(), $title);
$stmt->execute();

You can foreach over your array of values to insert and bind and execute each time. It wont be quite as fast as the bulk insert in the example you linked, but it will be more secure.

您可以遍历每个值的数组,以便每次插入和绑定并执行。它不会像您链接的示例中的批量插入一样快,但它会更安全。

#2

You can build prepared statement using code as mentioned here,

您可以使用此处提到的代码构建预准备语句,

PDO Prepared Inserts multiple rows in single query

PDO Prepared在单个查询中插入多行

PHP logic will be sort of like,

PHP逻辑有点像,

    /**
 * Insert With Ignore duplicates in Mysql DB.
 *
 * @param string $date Date.
 */
public static function insertWithIgnore($em, $container, $tableName, $fields, $rows)
{
    $query = "INSERT IGNORE INTO $tableName (`" . implode('`,`', $fields) . "`) VALUES ";
    $placeHolr = array_fill(0, count($fields), "?");
    $qPart = array_fill(0, count($rows), "(" . implode(',', $placeHolr) . ")");
    $query .= implode(",", $qPart);

    $pdo = self::getPDOFromEm($em, $container);
    $stmt = $pdo->prepare($query);
    $i = 1;
    foreach ($rows as $row) {
        $row['created_at'] = date("Y-m-d H:i:s");
        foreach ($fields as $f) {
            if (!isset($row[$f])) {
                $row[$f] = null;
            }
            $stmt->bindValue($i++, $row[$f]);
        }
    }

    $result = $stmt->execute();

    if ($result == false) {
        $str = print_r($stmt->errorInfo(), true);
        throw new \Exception($str);
    }

    $stmt->closeCursor();
    $pdo = null;
}

/**
 * Replace old rows in Mysql DB.
 *
 * @param string $date Date.
 */
public static function replace($em, $container, $tableName, $fields, $rows, $extraFieldValues = null)
{
    if ($extraFieldValues != null) {
        $fields = array_unique(array_merge($fields, array_keys($extraFieldValues)));
    }

    $query = "REPLACE INTO $tableName (`" . implode('`,`', $fields) . "`) VALUES ";
    $placeHolr = array_fill(0, count($fields), "?");
    $qPart = array_fill(0, count($rows), "(" . implode(',', $placeHolr) . ")");
    $query .= implode(",", $qPart);

    $pdo = self::getPDOFromEm($em, $container);
    $stmt = $pdo->prepare($query);
    $i = 1;
    foreach ($rows as $row) {            
        if ($extraFieldValues != null) {
            $row = array_merge($row, $extraFieldValues);
        }
        foreach ($fields as $f) {
            $stmt->bindValue($i++, $row[$f]);
        }                        
    }
    $stmt->execute();
    if (!$stmt) {
        throw new \Exception("PDO::errorInfo():" . print_r($stmt->errorInfo(), true));
    }
    $stmt->closeCursor();
    $pdo = null;
}

#3

-1

The query looks like this:

查询如下所示:

INSERT INTO people (name, age)
  VALUES
  ('Helen', 24),
  ('Katrina', 21),
  ('Samia', 22),
  ('Hui Ling', 25),
  ('Yumie', 29)

#1