你可以按照此处提到的代码构建预处理语句,
PDO Prepared Inserts multiple rows in single query
PHP 逻辑将类似于,
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;
}
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;
}
foreach
循环中。 - hakre