PDO Prepared Вставляет несколько строк в одном запросе

Источник

user2689491 17 мар '15 в 19:26

Поскольку это еще не было предложено, я уверен, что LOAD DATA INFILE по-прежнему является самым быстрым способом загрузки данных, поскольку он отключает индексирование, вставляет все данные, а затем повторно включает индексы - все в одном запросе.

Сохранение данных в формате csv должно быть довольно тривиальным, учитывая fputcsv. MyISAM работает быстрее, но вы все равно получаете большую производительность в InnoDB. Есть и другие недостатки, поэтому я бы пошел по этому пути, если вы вставляете много данных, и не беспокоитесь о менее чем 100 строках.

Источник

user2958643 26 янв '17 в 02:11

Вот мое решение: 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);

Сообщения об ошибках приветствуются.

Источник

user4541523 07 фев '15 в 20:30

Как видно из ответов, предшествующих моим, делать это с ванилью затруднительно.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();

Источник

user10126479 31 мар '23 в 15:16

Хотя старый вопрос, все вклады мне очень помогли, так что вот мое решение, которое работает в моем собственном DbContext учебный класс. $rows Параметр - это просто массив ассоциативных массивов, представляющих строки или модели: field name => insert value,

Если вы используете шаблон, который использует модели, это хорошо вписывается, когда данные модели передаются в виде массива, скажем, из 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();
}

Источник

user624247 19 фев '17 в 09:50

Возможно использование одного подготовленного оператора и одного параметра привязки для нескольких строк, изменяя только аргументы, предоставленные оператору. Мой проект показал лучшие результаты при использовании этого метода (0,398 с), чем при построении длинного оператора с длинным параметром привязки (2,508 с).

Важная заметка:

Я использовал InnoDB для своей таблицы, использование транзакции важно для повышения производительности.
С таблицей MyISAM вы можете получить выгоду, используя ЗАГРУЗКУ ДАННЫХ из файла 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;");

Извините за процедурный стиль, виню в этом предшественников моего проекта.

Источник

user4520136 26 окт '23 в 05:21

Для людей, у которых могут возникнуть проблемы с пониманием длинных рядов кодов:

Обязательно используйте один массив для каждой вставляемой строки. Например:

         $data = [ 
           ['name'=>'Sam', 'job'=>'fullstack', 'device'=>'hp'],
           ['name'=>'Joey', 'job'=>'ui', 'device'=>'apple']
         ]
 //the subarrays in $data can also be indexed arrays

Идея состоит в том, чтобы иметь возможность легко формировать запрос SQL Insert для нескольких строк, который должен выглядеть следующим образом:

      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);

Источник

user10153934 29 июн '23 в 00:28

Мой пример из реального мира, чтобы вставить все немецкие почтовые индексы в пустую таблицу (чтобы добавить названия городов позже):

// 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).

Источник

user318765 05 мар '15 в 22:27

test.php

<?php
require_once('Database.php');

$obj = new Database();
$table = "test";

$rows = array(
    array(
    'name' => 'balasubramani',
    'status' => 1
    ),
    array(
    'name' => 'balakumar',
    'status' => 1
    ),
    array(
    'name' => 'mani',
    'status' => 1
    )
);

var_dump($obj->insertMultiple($table,$rows));
?>

database.php

<?php
class Database 
{

    /* Initializing Database Information */

    var $host = 'localhost';
    var $user = 'root';
    var $pass = '';
    var $database = "database";
    var $dbh;

    /* Connecting Datbase */

    public function __construct(){
        try {
            $this->dbh = new PDO('mysql:host='.$this->host.';dbname='.$this->database.'', $this->user, $this->pass);
            //print "Connected Successfully";
        } 
        catch (PDOException $e) {
            print "Error!: " . $e->getMessage() . "<br/>";
            die();
        }
    }
/* Insert Multiple Rows in a table */

    public function insertMultiple($table,$rows){

        $this->dbh->beginTransaction(); // also helps speed up your inserts.
        $insert_values = array();
        foreach($rows as $d){
            $question_marks[] = '('  . $this->placeholders('?', sizeof($d)) . ')';
            $insert_values = array_merge($insert_values, array_values($d));
            $datafields = array_keys($d);
        }

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

        $stmt = $this->dbh->prepare ($sql);
        try {
            $stmt->execute($insert_values);
        } catch (PDOException $e){
            echo $e->getMessage();
        }
        return $this->dbh->commit();
    }

    /*  placeholders for prepared statements like (?,?,?)  */

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

        return implode($separator, $result);
    }

}
?>

Источник

user8527293 20 ноя '17 в 09:17

Это сработало для меня

    $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();

Источник

user2573610 28 окт '14 в 19:05

Вы можете вставить несколько строк в один запрос с помощью этой функции:

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')));

Преимущество заключается в том, что вы используете подготовленные операторы, вставляя несколько строк одним запросом. Безопасность!

Источник

user966189 07 мар '14 в 09:58

У меня была та же проблема, и вот как я выполняю для себя, и я сделал для себя функцию для нее (и вы можете использовать ее, если это поможет вам).

Пример:

INSERT INTO страны (страна, город) ЦЕННОСТИ (Германия, Берлин), (Франция, Париж);

$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;
    }

Если insertMultipleData($table, $multi_params) возвращает TRUE, ваши данные были вставлены в вашу базу данных.

Источник

user1749684 12 сен '18 в 14:03

Большинство решений, приведенных здесь для создания подготовленного запроса, являются более сложными, чем они должны быть. Используя встроенные функции PHP, вы можете легко создавать оператор SQL без значительных накладных расходов.

Дано $recordsмассив записей, где каждая запись сама является индексированным массивом (в форме field => value), следующая функция будет вставлять записи в данную таблицу $tableна соединении PDO $connection, используя только одно подготовленное утверждение. Обратите внимание, что это решение 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);
}

Источник

user1356675 30 сен '15 в 13:15

Вот чистое решение для вставки нескольких строк с помощью 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);

Источник

user919489 13 июл '23 в 17:46

Как насчет этого:

        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);            
        }

Идея заключается в том, чтобы циклически перебирать значения вашего массива, добавляя "номера идентификаторов" в каждый цикл для ваших подготовленных заполнителей операторов, в то же время вы добавляете значения в свой массив для параметров привязки. Если вам не нравится использовать "ключевой" индекс из массива, вы можете добавить внутри цикла $i=0 и $i++. Любой из них работает в этом примере, даже если у вас есть ассоциативные массивы с именованными ключами, он все равно будет работать, если ключи будут уникальными. Немного поработав, это подойдет и для вложенных массивов.

** Обратите внимание, что substr удаляет последние пробелы и запятую в переменных $sql, если у вас нет пробела, вам нужно изменить это значение на -1, а не на -2.

Источник

user2803225 26 май '20 в 06:38

Массив union должен быть еще быстрее чем array_pushтак что-то вроде:

$cumulativeArray += $rowArray;