PDO 准备在单个查询中插入多行

Question

我在 MySQL 上使用这种类型的 SQL 在一个查询中插入多行值：

INSERT INTO `tbl` (`key1`,`key2`) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),...

在 PDO 上的阅读中，准备好的语句应该比静态查询提供更好的安全性。

因此，我想知道是否可以使用准备好的语句生成“通过使用一个查询插入多行值”。

如果是，我该如何实施？

Answer 1

这是解决此问题的另一个（简单）解决方案：

首先，您需要使用 count() 计算源数组（此处：$aData）的数据。 然后，您使用 array_fill() 并生成一个新数组，其中的条目数量与源数组的条目数量相同，每个条目的值为“(?,?)”（占位符的数量取决于您使用的字段；此处为：2）。 然后生成的数组需要内爆，并使用逗号作为粘合。
在 foreach 循环中，您需要根据您使用的占位符数量生成另一个索引（占位符数量 * 当前数组索引 + 1）。 您需要在每个绑定值之后将生成的索引加 1。

$do = $db->prepare("INSERT INTO table (id, name) VALUES ".implode(',', array_fill(0, count($aData), '(?,?)')));

foreach($aData as $iIndex => $aValues){
 $iRealIndex = 2 * $iIndex + 1;
 $do->bindValue($iRealIndex, $aValues['id'], PDO::PARAM_INT);
 $iRealIndex = $iRealIndex + 1;
 $do->bindValue($iRealIndex, $aValues['name'], PDO::PARAM_STR);
}

$do->execute();

Answer 2

我是这样做的：

首先定义您将使用的列名，或者将其留空，pdo 将假设您想要使用表中的所有列 - 在这种情况下，您需要告知它们在桌子上出现的确切顺序。

$cols = 'name', 'middleName', 'eMail';
$table = 'people';

现在，假设您已经准备好了一个二维数组。 迭代它，并用您的行值构造一个字符串，如下所示：

foreach ( $people as $person ) {
if(! $rowVals ) {
$rows = '(' . "'$name'" . ',' . "'$middleName'" . ',' .           "'$eMail'" . ')';
} else { $rowVals  = '(' . "'$name'" . ',' . "'$middleName'" . ',' . "'$eMail'" . ')';
}

现在，您刚才所做的是检查 $rows 是否已定义，如果没有，则创建它并存储行值和必要的 SQL 语法，因此它将是有效的陈述。 请注意，字符串应该放在双引号和单引号内，这样它们就会被立即识别。

剩下要做的就是准备语句并执行，如下所示：

$stmt = $db->prepare ( "INSERT INTO $table $cols VALUES $rowVals" );
$stmt->execute ();

到目前为止已测试最多 2000 行，执行时间很短。 将进行更多测试，如果我有进一步的贡献，我会回到这里。

问候。

Answer 3

这是我的解决方案： https://github.com/sasha-ch/Aura.Sql
基于 auraphp/Aura.Sql 库。

使用示例：

$q = "insert into t2(id,name) values (?,?), ... on duplicate key update name=name"; 
$bind_values = [ [[1,'str1'],[2,'str2']] ];
$pdo->perform($q, $bind_values);

欢迎报告错误。

Answer 4

由于尚未建议，我非常确定 LOAD DATA INFILE 仍然是加载数据的最快方法，因为它会禁用索引，插入所有数据，然后重新启用索引 - 所有这些都在单个请求中完成。

记住 fputcsv，将数据保存为 csv 应该是相当简单的。 MyISAM 是最快的，但在 InnoDB 中您仍然可以获得出色的性能。 不过，还有其他缺点，所以如果您要插入大量数据，并且不关心 100 行以下的数据，我会采用这种方法。

Answer 5

虽然是一个老问题，但所有贡献对我帮助很大，所以这是我的解决方案，它在我自己的 DbContext 类中工作。 $rows 参数只是一个表示行或模型的关联数组的数组：field name => 插入值。

如果您使用的模式使用模型，那么当将模型数据作为数组传递时（例如从模型类中的 ToRowArray 方法传递），这非常适合。

注意：这应该是不言而喻的，但绝不允许传递参数
将此方法公开给用户或依赖于任何用户输入（除了已验证和清理的插入值之外）。 $tableName 参数和列名应由调用逻辑定义； 例如，User 模型可以映射到用户表，该表的列列表映射到模型的成员字段。

public function InsertRange($tableName, $rows)
{
    // Get column list
    $columnList = array_keys($rows[0]);
    $numColumns = count($columnList);
    $columnListString = implode(",", $columnList);

    // Generate pdo param placeholders
    $placeHolders = array();

    foreach($rows as $row)
    {
        $temp = array();

        for($i = 0; $i < count($row); $i++)
            $temp[] = "?";

        $placeHolders[] = "(" . implode(",", $temp) . ")";
    }

    $placeHolders = implode(",", $placeHolders);

    // Construct the query
    $sql = "insert into $tableName ($columnListString) values $placeHolders";
    $stmt = $this->pdo->prepare($sql);

    $j = 1;
    foreach($rows as $row)
    {
        for($i = 0; $i < $numColumns; $i++)
        {
            $stmt->bindParam($j, $row[$columnList[$i]]);
            $j++;
        }
    }

    $stmt->execute();
}

Answer 6

正如我前面的答案所证明的那样，使用普通的 PDO 来做到这一点很麻烦。 诸如 Nette 之类的数据库抽象层使任务变得更加容易，而不会对速度产生重大影响：

$params = [
    'host' => 'sandbox-db',
    'database' => 'test',
    'user' => 'root',
    'pass' => '123',
];

$database = new Nette\Database\Connection("mysql:host={$params['host']};dbname={$params['database']};charset=utf8", $params['user'], $params['pass']);

$staff = [
    [
        'birthday' => new DateTime('1995-05-01'),
        'name' => 'Sharon',
        'salary' => '200',
        'boss' => true,
    ],
    [
        'birthday' => new DateTime('2000-01-01'),
        'name' => 'John',
        'salary' => '140',
        'boss' => false,
    ],
    [
        'birthday' => new DateTime('1985-08-01'),
        'name' => 'Oliver',
        'salary' => '120',
        'boss' => false,
    ],
];

$database->beginTransaction();

$database->query('INSERT INTO test', $staff);

$database->commit();

Answer 7

对于可能无法理解长行代码的人：

1. 确保为要插入的每一行都有一个数组。 例如：

   <前><代码> $数据 = [
   ['name'=>'Sam', 'job'=>'fullstack', 'device'=>'hp'],
   ['name'=>'Joey', 'job'=>'ui', 'device'=>'apple']
   ]
   //$data中的子数组也可以是索引数组

这个想法是能够轻松地形成多行的 SQL 插入查询，它应该看起来像这样：

insert into table_name (col1, col2, col3) values ('valA1', 'valA2', 'valA3'), ('valB1', 'valB2', 'valB3'); //normal sql, insecure
insert into table_name (col1, col2, col3) values (?, ?, ?), (?, ?, ?); //prepared statement, secure

希望我仍然有意义。
如果您要使用动态列名进行批量插入，则必须始终拥有预期列名的数组并为其分配默认值。 例如：

$expectedcols = ['name'=>'', 'job'=>'', 'device'=>''];

然后；

    $validcols = array_intersect_key($data[0], $expectedcols); //Takes only valid column names and throws away unexpected column names
    $allvalidcols = array_merge($expectedcols, $validcols); //assigns the values in $validcols to the values in $expectedcols. Other columns in $expectedcols required to be inserted will also be captured, but their values will be those default values you assigned to them earlier.
    $col_arr = array_keys($allvalidcols);            //extracts the safe column names.
    $columns = implode(', ', $col_arr);         //name, job, device
    $cols = count($col_arr);                    //number of columns, 3
    $temparr = array_fill(0, $cols, '?');       //first set of placeholders ['?', '?', '?']
    $tempstr = '('.implode(', ', $temparr).')'; //(?, ?, ?)
    $rows = count($data);                  //number of rows, 2
    $totalarr = array_fill(0, $rows, $tempstr); //full set of placeholders ['(?, ?, ?)', '(?, ?, ?)']
    $totalstr = implode(', ', $totalarr);       //(?, ?, ?), (?, ?, ?)
    
    $allarr = [];
    foreach($data as $ind=>$val) {
        $values = array_values($val);           
        $allarr = array_merge($allarr, $values); //['Sam', 'fullstack', 'hp', 'Joey', 'ui', 'apple']
    }

    $sql = "insert into table_name ($columns) values $totalstr";

但如果你不使用动态列名进行批量插入，请跳过 $validcols、$allvalidcols、$col_arr 和 $columns，它们不是很有必要，然后将 $cols 设置为列数，在本例中为 3 ，然后将 $sql 查询调整为：

$sql = "insert into table_name (name, age, device) values $totalstr";

最后；

$stmt = $conn->prepare($sql);
$done = $stmt->execute($allarr);

Answer 8

我的真实世界示例将所有德国邮政编码插入到空表中（稍后添加城镇名称）：

// obtain column template
$stmt = $db->prepare('SHOW COLUMNS FROM towns');
$stmt->execute();
$columns = array_fill_keys(array_values($stmt->fetchAll(PDO::FETCH_COLUMN)), null);
// multiple INSERT
$postcode = '01000';// smallest german postcode
while ($postcode <= 99999) {// highest german postcode
    $values = array();
    while ($postcode <= 99999) {
        // reset row
        $row = $columns;
        // now fill our row with data
        $row['postcode'] = sprintf('%05d', $postcode);
        // build INSERT array
        foreach ($row as $value) {
            $values[] = $value;
        }
        $postcode++;
        // avoid memory kill
        if (!($postcode % 10000)) {
            break;
        }
    }
    // build query
    $count_columns = count($columns);
    $placeholder = ',(' . substr(str_repeat(',?', $count_columns), 1) . ')';//,(?,?,?)
    $placeholder_group = substr(str_repeat($placeholder, count($values) / $count_columns), 1);//(?,?,?),(?,?,?)...
    $into_columns = implode(',', array_keys($columns));//col1,col2,col3
    // this part is optional:
    $on_duplicate = array();
    foreach ($columns as $column => $row) {
        $on_duplicate[] = $column;
        $on_duplicate[] = $column;
    }
    $on_duplicate = ' ON DUPLICATE KEY UPDATE' . vsprintf(substr(str_repeat(', %s = VALUES(%s)', $count_columns), 1), $on_duplicate);
    // execute query
    $stmt = $db->prepare('INSERT INTO towns (' . $into_columns . ') VALUES' . $placeholder_group . $on_duplicate);//INSERT INTO towns (col1,col2,col3) VALUES(?,?,?),(?,?,?)... {ON DUPLICATE...}
    $stmt->execute($values);
}

如您所见，它完全灵活。 您不需要检查列的数量或检查列所在的位置。 您只需要设置插入数据：

    $row['postcode'] = sprintf('%05d', $postcode);

我对某些查询字符串构造函数感到自豪，因为它们无需像 array_merge 这样的繁重数组函数即可工作。 尤其是 vsprintf() 是一个很好的发现。

最后我需要添加 2x while() 以避免超出内存限制。 这取决于您的内存限制，但它是避免问题的一个很好的通用解决方案（并且有 10 个查询仍然比 10.000 个查询要好得多）。

Answer 9

这是使用 PDO 插入多行的干净解决方案。

由于数组解构，它应该适用于 php 7.1+，但我想这部分可以很容易地更改。

function insertMultipleQuery(string $table, array $columns, array $items): array
{
    $placeholders = '';
    foreach ($items as $item) {
        if (!empty($placeholders)) {
            $placeholders .= ', ';
        }
        $placeholders .= '(' . implode(',', array_fill(0, count($item), '?')) . ')';
    }

    $names = '`' . implode("`,`", $columns) . '`';
    $query = "INSERT INTO {$table} ({$names}) VALUES {$placeholders}";

    $values = [];
    foreach ($items as $item) {
        foreach ($item as $value) {
            $values[] = $value;
        }
    }

    return [
        'query' => $query,
        'values' => $values,
    ];
}

样本数据：

// $pdo = new PDO(...........); // Fill in your connection config
$tableName = 'table_name';
// It should be obvious - the keys of the array is actual column names in your table in database.
$columns = [
    'name',
    'value',
    'created',
];
$dataToInsert = [
    [
        'name' => 'some name 1',
        'value' => 'some value 1',
        'created' => 'created datetime 1',
    ],
    [
        'name' => 'some name 2',
        'value' => 'some value 2',
        'created' => 'created datetime 2',
    ]
];

[
    'query' => $query,
    'values' => $values,
] = $this->insertMultipleQuery($tableName, $columns, $dataToInsert);
$stmt = $pdo->prepare($query);
$stmt->execute($values);

Answer 10

我遇到了同样的问题，这就是我为自己完成的方法，我为自己制作了一个函数（如果对您有帮助，您可以使用它）。

示例：

INSERT INTO 国家（国家、城市）VALUES（德国、柏林）、（法国、巴黎）；

$arr1 = Array("Germany", "Berlin");
$arr2 = Array("France", "France");

insertMultipleData("countries", Array($arr1, $arr2));


// Inserting multiple data to the Database.
public function insertMultipleData($table, $multi_params){
    try{
        $db = $this->connect();

        $beforeParams = "";
        $paramsStr = "";
        $valuesStr = "";

        for ($i=0; $i < count($multi_params); $i++) { 

            foreach ($multi_params[$i] as $j => $value) {                   

                if ($i == 0) {
                    $beforeParams .=  " " . $j . ",";
                }

                $paramsStr .= " :"  . $j . "_" . $i .",";                                       
            }

            $paramsStr = substr_replace($paramsStr, "", -1);
            $valuesStr .=  "(" . $paramsStr . "),"; 
            $paramsStr = "";
        }


        $beforeParams = substr_replace($beforeParams, "", -1);
        $valuesStr = substr_replace($valuesStr, "", -1);


        $sql = "INSERT INTO " . $table . " (" . $beforeParams . ") VALUES " . $valuesStr . ";";

        $stmt = $db->prepare($sql);


        for ($i=0; $i < count($multi_params); $i++) { 
            foreach ($multi_params[$i] as $j => &$value) {
                $stmt->bindParam(":" . $j . "_" . $i, $value);                                      
            }
        }

        $this->close($db);
        $stmt->execute();                       

        return true;

    }catch(PDOException $e){            
        return false;
    }

    return false;
}

// Making connection to the Database 
    public function connect(){
        $host = Constants::DB_HOST;
        $dbname = Constants::DB_NAME;
        $user = Constants::DB_USER;
        $pass = Constants::DB_PASS;

        $mysql_connect_str = 'mysql:host='. $host . ';dbname=' .$dbname;

        $dbConnection = new PDO($mysql_connect_str, $user, $pass);
        $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        return $dbConnection;
    }

    // Closing the connection
    public function close($db){
        $db = null;
    }

If insertMultipleData($table, $multi_params)< /strong> 返回 TRUE，您的数据已插入到数据库中。

Answer 11

这里给出的大多数用于创建准备好的查询的解决方案都比它们需要的更复杂。 使用 PHP 的内置函数，您可以轻松创建 SQL 语句，而无需大量开销。

给定$records，一个记录数组，其中每个记录本身就是一个索引数组（采用field => value的形式），以下函数将把记录插入到PDO 连接 $connection 上的给定表 $table，仅使用单个准备好的语句。 请注意，这是一个 PHP 5.6+ 解决方案，因为在对 array_push 的调用中使用了参数解包：

private function import(PDO $connection, $table, array $records)
{
    $fields = array_keys($records[0]);
    $placeHolders = substr(str_repeat(',?', count($fields)), 1);
    $values = [];
    foreach ($records as $record) {
        array_push($values, ...array_values($record));
    }

    $query = 'INSERT INTO ' . $table . ' (';
    $query .= implode(',', $fields);
    $query .= ') VALUES (';
    $query .= implode('),(', array_fill(0, count($records), $placeHolders));
    $query .= ')';

    $statement = $connection->prepare($query);
    $statement->execute($values);
}

Answer 12

这对我有用

$sql = 'INSERT INTO table(pk_pk1,pk_pk2,date,pk_3) VALUES '; 
$qPart = array_fill(0, count($array), "(?, ?,UTC_TIMESTAMP(),?)");
$sql .= implode(",", $qPart);
$stmt =    DB::prepare('base', $sql);
$i = 1;
foreach ($array as $value) { 
  $stmt->bindValue($i++, $value);
  $stmt->bindValue($i++, $pk_pk1);
  $stmt->bindValue($i++, $pk_pk2); 
  $stmt->bindValue($i++, $pk_pk3); 
} 
$stmt->execute();

Answer 13

像这样的事情怎么样：

        if(count($types_of_values)>0){
         $uid = 1;
         $x = 0;
         $sql = "";
         $values = array();
          foreach($types_of_values as $k=>$v){
            $sql .= "(:id_$k,:kind_of_val_$k), ";
            $values[":id_$k"] = $uid;
            $values[":kind_of_val_$k"] = $v;
          }
         $sql = substr($sql,0,-2);
         $query = "INSERT INTO table (id,value_type) VALUES $sql";
         $res = $this->db->prepare($query);
         $res->execute($values);            
        }

这背后的想法是循环遍历数组值，为准备好的语句占位符的每个循环添加“id 号”，同时将值添加到绑定参数的数组中。 如果您不喜欢使用数组中的“key”索引，可以在循环内添加 $i=0 和 $i++ 。 在此示例中，即使您有带有命名键的关联数组，只要键是唯一的，它仍然可以工作。 只需做一点工作，嵌套数组也可以。

**注意，substr 会删除 $sql 变量的最后一个空格和逗号，如果没有空格，则需要将其更改为 -1 而不是 - 2.

Answer 14

您可以使用此函数在单个查询中插入多行：

function insertMultiple($query,$rows) {
    if (count($rows)>0) {
        $args = array_fill(0, count($rows[0]), '?');

        $params = array();
        foreach($rows as $row)
        {
            $values[] = "(".implode(',', $args).")";
            foreach($row as $value)
            {
                $params[] = $value;
            }
        }

        $query = $query." VALUES ".implode(',', $values);
        $stmt = $PDO->prepare($query);
        $stmt->execute($params);
    }
}

$row 是值数组的数组。
在您的情况下，您可以使用以下命令调用该函数。

insertMultiple("INSERT INTO tbl (`key1`,`key2`)",array(array('r1v1','r1v2'),array('r2v1','r2v2')));

这样做的好处是您可以使用准备好的语句，同时使用单个查询插入多行。 安全！

Answer 15

可以对多行​​使用单个准备好的语句和单个绑定参数，仅更改提供给语句的参数。 我的项目使用此方法（0.398s）获得了比使用长绑定参数构建长语句（2.508s）更好的结果。

重要提示：

• 我为我的表使用了 InnoDB，使用事务对于性能提升非常重要
• 。通过使用文件中的 LOAD DATA 可能会受益 https://dev.mysql.com/doc/refman/8.0/en/optimizing-myisam-bulk-data-loading.html

使用@jamesvi示例，我在我的项目中使用了这种方法。

$dataVals = [['a', 'b', 'c'], ['d', 'e', 'f']];

$colAIn = '';
$colBIn = '';
$colCIn = '';

// Depending on the keys you have on your table, disabling some more checks might increase performance
// mysqli_query($con, "SET AUTOCOMMIT = 0;"); // this should be unecessary since we're using transactions
// mysqli_query($con, "SET FOREIGN_KEY_CHECKS = 0;");
// mysqli_query($con, "SET UNIQUE_CHECKS = 0;");
mysqli_begin_transaction($con);

$stmt = mysqli_prepare($con, "INSERT INTO tblName (colA, colB, colC) VALUES (?, ?, ?)");
mysqli_stmt_bind_param($stmt, "sss", $colAIn, $colBIn, $colCIn);

for ($i = 0; $i < count($dataVals); $i++) {
    $colAIn = $dataVals[$i][0];
    $colBIn = $dataVals[$i][1];
    $colCIn = $dataVals[$i][2];
    mysqli_stmt_execute($stmt);
}
mysqli_commit($con);

// mysqli_query($con, "SET AUTOCOMMIT = 0;"); // this should be unecessary since we're using transactions
// mysqli_query($con, "SET FOREIGN_KEY_CHECKS = 0;");
// mysqli_query($con, "SET UNIQUE_CHECKS = 0;");

对于程序风格感到抱歉，我为此责怪我的项目的前辈。

Answer 16

使用 PDO 准备语句插入多个值

在一个执行语句中插入多个值。 为什么因为根据 此页面 它比常规插入。

$data[] = ['valueA1', 'valueB1'];
$data[] = ['valueA2', 'valueB2'];

更多数据值，或者您可能有一个填充数据的循环。

这基本上就是我们希望插入语句的样子：

insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...)....

因此，对于准备好的插入，您需要知道创建单个 VALUES 部分的字段数以及行数，以便知道重复它的次数。

现在，代码：

// create the ?,? sequence for a single row
$values = str_repeat('?,', count($data[0]) - 1) . '?';
// construct the entire query
$sql = "INSERT INTO table (columnA, columnB) VALUES " .
    // repeat the (?,?) sequence for each row
    str_repeat("($values),", count($data) - 1) . "($values)";    

$stmt = $pdo->prepare ($sql);
// execute with all values from $data
$stmt->execute(array_merge(...$data));

请注意，这种方法是 100% 安全的，因为查询完全由代码中显式编写的常量部分构成，尤其是列名。

Answer 17

与 Balagtas 先生的答案相同，稍微清楚一些...

最近版本的 MySQL 和 PHP PDO do 支持多行 INSERT 语句。

SQL 概述

SQL 看起来像这样，假设您想要INSERT 到一个 3 列表。

INSERT INTO tbl_name
            (colA, colB, colC)
     VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) [,...]

即使使用多行插入，ON DUPLICATE KEY UPDATE 也能按预期工作； 附加：

ON DUPLICATE KEY UPDATE colA = VALUES(colA), colB = VALUES(colB), colC = VALUES(colC)

PHP 概述

您的 PHP 代码将遵循通常的 $pdo->prepare($qry) 和 $stmt->execute($params) PDO 调用。

$params 将是要传递给 INSERT 的所有值的一维数组。

在上面的例子中，它应该包含 9 个元素； PDO 将使用每组 3 个作为单行值。 （插入 3 行，每行 3 列 = 9 个元素数组。）

实现

下面的代码是为了清晰起见而编写的，而不是为了提高效率。 如果您愿意，可以使用 PHP array_*() 函数来更好地映射或遍历数据。 假设执行单个查询并且每个查询本身就是一个事务，则不需要显式事务。

假设：

• 的一行值的一维数组

$dataVals - 多维数组，其中每个元素都是要插入示例代码

// setup data values for PDO. No memory overhead thanks to copy-on-write
$dataToInsert = array();
foreach ($dataVals as $row) {
    foreach($row as $val) {
        $dataToInsert[] = $val;
    }
}

$onDup = "ON DUPLICATE KEY UPDATE colA=VALUES(colA)"; // optional

// setup the placeholders - a fancy way to make the long "(?, ?, ?)..." string
$rowPlaces = '(' . implode(', ', array_fill(0, count($colNames), '?')) . ')';
$allPlaces = implode(', ', array_fill(0, count($dataVals), $rowPlaces));

$sql = "INSERT INTO `tblName` (`colA`, `colB, colC)" . 
    " VALUES $allPlaces ON DUPLICATE KEY UPDATE $onDup";

// and then the PHP PDO boilerplate
$stmt = $pdo->prepare ($sql);
$stmt->execute($dataToInsert);

Answer 18

无论如何，我看到很多用户建议迭代 INSERT 语句，而不是像所选答案那样构建单个字符串查询。 我决定运行一个仅包含两个字段和一个非常基本的插入语句的简单测试：

<?php
require('conn.php');

$fname = 'J';
$lname = 'M';

$time_start = microtime(true);
$stmt = $db->prepare('INSERT INTO table (FirstName, LastName) VALUES (:fname, :lname)');

for($i = 1; $i <= 10; $i++ )  {
    $stmt->bindParam(':fname', $fname);
    $stmt->bindParam(':lname', $lname);
    $stmt->execute();

    $fname .= 'O';
    $lname .= 'A';
}


$time_end = microtime(true);
$time = $time_end - $time_start;

echo "Completed in ". $time ." seconds <hr>";

$fname2 = 'J';
$lname2 = 'M';

$time_start2 = microtime(true);
$qry = 'INSERT INTO table (FirstName, LastName) VALUES ';
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?)";

$stmt2 = $db->prepare($qry);
$values = array();

for($j = 1; $j<=10; $j++) {
    $values2 = array($fname2, $lname2);
    $values = array_merge($values,$values2);

    $fname2 .= 'O';
    $lname2 .= 'A';
}

$stmt2->execute($values);

$time_end2 = microtime(true);
$time2 = $time_end2 - $time_start2;

echo "Completed in ". $time2 ." seconds <hr>";
?>

虽然整个查询本身花费了几毫秒或更短的时间，但后一个（单字符串）查询始终快 8 倍或更多。 如果这是为了反映更多列上数千行的导入，那么差异可能是巨大的。

Answer 19

请注意，此答案已过时。 如今，PHP 可以使用 array_merge(...$data) 一次性合并所有数组，无需任何循环。

当 $data 数组很小时，Herbert Balagtas 接受的答案效果很好。 对于较大的 $data 数组，array_merge 函数变得异常缓慢。 我创建 $data 数组的测试文件有 28 列，大约 80,000 行。 最终脚本花了41s完成。

使用array_push()创建$insert_values而不是array_merge()导致100倍加速，执行时间为0.41s。

有问题的 array_merge()：

$insert_values = array();

foreach($data as $d){
 $question_marks[] = '('  . placeholders('?', sizeof($d)) . ')';
 $insert_values = array_merge($insert_values, array_values($d));
}

为了消除对 array_merge() 的需要，您可以构建以下两个数组：

//Note that these fields are empty, but the field count should match the fields in $datafields.
$data[] = array('','','','',... n ); 

//getting rid of array_merge()
array_push($insert_values, $value1, $value2, $value3 ... n ); 

然后可以按如下方式使用这些数组：

function placeholders($text, $count=0, $separator=","){
    $result = array();
    if($count > 0){
        for($x=0; $x<$count; $x++){
            $result[] = $text;
        }
    }

    return implode($separator, $result);
}

$pdo->beginTransaction();

foreach($data as $d){
 $question_marks[] = '('  . placeholders('?', sizeof($d)) . ')';
}

$sql = "INSERT INTO table (" . implode(",", array_keys($datafield) ) . ") VALUES " . implode(',', $question_marks);

$stmt = $pdo->prepare($sql);
$stmt->execute($insert_values);
$pdo->commit();

Answer 20

两种可能的方法：

$stmt = $pdo->prepare('INSERT INTO foo VALUES(:v1_1, :v1_2, :v1_3),
    (:v2_1, :v2_2, :v2_3),
    (:v2_1, :v2_2, :v2_3)');
$stmt->bindValue(':v1_1', $data[0][0]);
$stmt->bindValue(':v1_2', $data[0][1]);
$stmt->bindValue(':v1_3', $data[0][2]);
// etc...
$stmt->execute();

或者：

$pdo->beginTransaction();
$stmt = $pdo->prepare('INSERT INTO foo VALUES(:a, :b, :c)');
foreach($data as $item)
{
    $stmt->bindValue(':a', $item[0]);
    $stmt->bindValue(':b', $item[1]);
    $stmt->bindValue(':c', $item[2]);
    $stmt->execute();
}
$pdo->commit();

如果所有行的数据都在一个数组中，我将使用第二种解决方案。 由于使用了事务，它几乎与第一个解决方案一样快。

Answer 21

这根本不是您使用准备好的语句的方式。

每个查询插入一行是完全可以的，因为您可以使用不同的参数多次执行一个准备好的语句。 事实上，这是最大的优势之一，因为它允许您以高效、安全和舒适的方式插入大量行。

因此，也许可以实现您提出的方案，至少对于固定数量的行，但几乎可以保证这不是您真正想要的。

Answer 22

一个更简短的答案：展平按列排序的数据数组，然后

//$array = array( '1','2','3','4','5', '1','2','3','4','5');
$arCount = count($array);
$rCount = ($arCount  ? $arCount - 1 : 0);
$criteria = sprintf("(?,?,?,?,?)%s", str_repeat(",(?,?,?,?,?)", $rCount));
$sql = "INSERT INTO table(c1,c2,c3,c4,c5) VALUES$criteria";

当插入 1,000 条左右的记录时，当您需要的只是值的计数时，您不希望必须循环遍历每个记录来插入它们。

Answer 23

这是我的简单方法。

    $values = array();
    foreach($workouts_id as $value){
      $_value = "(".$value.",".$plan_id.")";
      array_push($values,$_value);
    }
    $values_ = implode(",",$values);

    $sql = "INSERT INTO plan_days(id,name) VALUES" . $values_."";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute();

Answer 24

这是我编写的一个类，使用清除选项进行多次插入：

<?php

/**
 * $pdo->beginTransaction();
 * $pmi = new PDOMultiLineInserter($pdo, "foo", array("a","b","c","e"), 10);
 * $pmi->insertRow($data);
 * ....
 * $pmi->insertRow($data);
 * $pmi->purgeRemainingInserts();
 * $pdo->commit();
 *
 */
class PDOMultiLineInserter {
    private $_purgeAtCount;
    private $_bigInsertQuery, $_singleInsertQuery;
    private $_currentlyInsertingRows  = array();
    private $_currentlyInsertingCount = 0;
    private $_numberOfFields;
    private $_error;
    private $_insertCount = 0;

    function __construct(\PDO $pdo, $tableName, $fieldsAsArray, $bigInsertCount = 100) {
        $this->_numberOfFields = count($fieldsAsArray);
        $insertIntoPortion = "INSERT INTO `$tableName` (`".implode("`,`", $fieldsAsArray)."`) VALUES";
        $questionMarks  = " (?".str_repeat(",?", $this->_numberOfFields - 1).")";

        $this->_purgeAtCount = $bigInsertCount;
        $this->_bigInsertQuery    = $pdo->prepare($insertIntoPortion.$questionMarks.str_repeat(", ".$questionMarks, $bigInsertCount - 1));
        $this->_singleInsertQuery = $pdo->prepare($insertIntoPortion.$questionMarks);
    }

    function insertRow($rowData) {
        // @todo Compare speed
        // $this->_currentlyInsertingRows = array_merge($this->_currentlyInsertingRows, $rowData);
        foreach($rowData as $v) array_push($this->_currentlyInsertingRows, $v);
        //
        if (++$this->_currentlyInsertingCount == $this->_purgeAtCount) {
            if ($this->_bigInsertQuery->execute($this->_currentlyInsertingRows) === FALSE) {
                $this->_error = "Failed to perform a multi-insert (after {$this->_insertCount} inserts), the following errors occurred:".implode('<br/>', $this->_bigInsertQuery->errorInfo());
                return false;
            }
            $this->_insertCount++;

            $this->_currentlyInsertingCount = 0;
            $this->_currentlyInsertingRows = array();
        }
        return true;
    }

    function purgeRemainingInserts() {
        while ($this->_currentlyInsertingCount > 0) {
            $singleInsertData = array();
            // @todo Compare speed - http://www.evardsson.com/blog/2010/02/05/comparing-php-array_shift-to-array_pop/
            // for ($i = 0; $i < $this->_numberOfFields; $i++) $singleInsertData[] = array_pop($this->_currentlyInsertingRows); array_reverse($singleInsertData);
            for ($i = 0; $i < $this->_numberOfFields; $i++) array_unshift($singleInsertData, array_pop($this->_currentlyInsertingRows));

            if ($this->_singleInsertQuery->execute($singleInsertData) === FALSE) {
                $this->_error = "Failed to perform a small-insert (whilst purging the remaining rows; the following errors occurred:".implode('<br/>', $this->_singleInsertQuery->errorInfo());
                return false;
            }
            $this->_currentlyInsertingCount--;
        }
    }

    public function getError() {
        return $this->_error;
    }
}

Answer 25

根据我的实验，我发现在单个事务中包含多个值行的 mysql insert 语句是最快的。

但是，如果数据太多，那么 mysql 的 max_allowed_pa​​cket 设置可能会限制单个事务插入多个值行。 因此，当数据大于mysql的max_allowed_pa​​cket大小时，以下函数将失败：

1. singleTransactionInsertWithRollback
2. singleTransactionInsertWithPlaceholders
3. singleTransactionInsert

最成功插入大数据场景中的一种是transactionSpeed方法，但它比上述方法更耗时。 因此，要解决这个问题，您可以将数据分割成更小的块并多次调用单个事务插入，或者使用 transactionSpeed 方法放弃执行速度。

这是我的研究

<?php

class SpeedTestClass
{
    private $data;

    private $pdo;

    public function __construct()
    {
        $this->data = [];
        $this->pdo = new \PDO('mysql:dbname=test_data', 'admin', 'admin');
        if (!$this->pdo) {
            die('Failed to connect to database');
        }
    }

    public function createData()
    {
        $prefix = 'test';
        $postfix = 'unicourt.com';
        $salutations = ['Mr.', 'Ms.', 'Dr.', 'Mrs.'];

        $csv[] = ['Salutation', 'First Name', 'Last Name', 'Email Address'];
        for ($i = 0; $i < 100000; ++$i) {
            $csv[] = [
                $salutations[$i % \count($salutations)],
                $prefix.$i,
                $prefix.$i,
                $prefix.$i.'@'.$postfix,
            ];
        }

        $this->data = $csv;
    }

    public function truncateTable()
    {
        $this->pdo->query('TRUNCATE TABLE `name`');
    }

    public function transactionSpeed()
    {
        $timer1 = microtime(true);
        $this->pdo->beginTransaction();
        $sql = 'INSERT INTO `name` (`first_name`, `last_name`) VALUES (:first_name, :last_name)';
        $sth = $this->pdo->prepare($sql);

        foreach (\array_slice($this->data, 1) as $values) {
            $sth->execute([
                ':first_name' => $values[1],
                ':last_name' => $values[2],
            ]);
        }

        // $timer2 = microtime(true);
        // echo 'Prepare Time: '.($timer2 - $timer1).PHP_EOL;
        // $timer3 = microtime(true);

        if (!$this->pdo->commit()) {
            echo "Commit failed\n";
        }
        $timer4 = microtime(true);
        // echo 'Commit Time: '.($timer4 - $timer3).PHP_EOL;

        return $timer4 - $timer1;
    }

    public function autoCommitSpeed()
    {
        $timer1 = microtime(true);
        $sql = 'INSERT INTO `name` (`first_name`, `last_name`) VALUES (:first_name, :last_name)';
        $sth = $this->pdo->prepare($sql);
        foreach (\array_slice($this->data, 1) as $values) {
            $sth->execute([
                ':first_name' => $values[1],
                ':last_name' => $values[2],
            ]);
        }
        $timer2 = microtime(true);

        return $timer2 - $timer1;
    }

    public function noBindAutoCommitSpeed()
    {
        $timer1 = microtime(true);

        foreach (\array_slice($this->data, 1) as $values) {
            $sth = $this->pdo->prepare("INSERT INTO `name` (`first_name`, `last_name`) VALUES ('{$values[1]}', '{$values[2]}')");
            $sth->execute();
        }
        $timer2 = microtime(true);

        return $timer2 - $timer1;
    }

    public function singleTransactionInsert()
    {
        $timer1 = microtime(true);
        foreach (\array_slice($this->data, 1) as $values) {
            $arr[] = "('{$values[1]}', '{$values[2]}')";
        }
        $sth = $this->pdo->prepare('INSERT INTO `name` (`first_name`, `last_name`) VALUES '.implode(', ', $arr));
        $sth->execute();
        $timer2 = microtime(true);

        return $timer2 - $timer1;
    }

    public function singleTransactionInsertWithPlaceholders()
    {
        $placeholders = [];
        $timer1 = microtime(true);
        $sql = 'INSERT INTO `name` (`first_name`, `last_name`) VALUES ';
        foreach (\array_slice($this->data, 1) as $values) {
            $placeholders[] = '(?, ?)';
            $arr[] = $values[1];
            $arr[] = $values[2];
        }
        $sql .= implode(', ', $placeholders);
        $sth = $this->pdo->prepare($sql);
        $sth->execute($arr);
        $timer2 = microtime(true);

        return $timer2 - $timer1;
    }

    public function singleTransactionInsertWithRollback()
    {
        $placeholders = [];
        $timer1 = microtime(true);
        $sql = 'INSERT INTO `name` (`first_name`, `last_name`) VALUES ';
        foreach (\array_slice($this->data, 1) as $values) {
            $placeholders[] = '(?, ?)';
            $arr[] = $values[1];
            $arr[] = $values[2];
        }
        $sql .= implode(', ', $placeholders);
        $this->pdo->beginTransaction();
        $sth = $this->pdo->prepare($sql);
        $sth->execute($arr);
        $this->pdo->commit();
        $timer2 = microtime(true);

        return $timer2 - $timer1;
    }
}

$s = new SpeedTestClass();
$s->createData();
$s->truncateTable();
echo "Time Spent for singleTransactionInsertWithRollback: {$s->singleTransactionInsertWithRollback()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for single Transaction Insert: {$s->singleTransactionInsert()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for single Transaction Insert With Placeholders: {$s->singleTransactionInsertWithPlaceholders()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for transaction: {$s->transactionSpeed()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for AutoCommit: {$s->noBindAutoCommitSpeed()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for autocommit with bind: {$s->autoCommitSpeed()}".PHP_EOL;
$s->truncateTable();

仅包含两列的表的 100,000 个条目的结果如下

$ php data.php
Time Spent for singleTransactionInsertWithRollback: 0.75147604942322
Time Spent for single Transaction Insert: 0.67445182800293
Time Spent for single Transaction Insert With Placeholders: 0.71131205558777
Time Spent for transaction: 8.0056409835815
Time Spent for AutoCommit: 35.4979159832
Time Spent for autocommit with bind: 33.303519010544