0

[PHP] Dodanie wielu wierszy do bazy używając PHP PDO – SQL Server INSERT Multiple Rows – PDO Interface

  • 2022-08-07
  • PHP

Miałem za zadanie wykonać INSERT do bazy danych przy pomocy interfejsu PDO. Ilość wierszy, które chcę zaimportować wynosi ~3600. Przetestowałem kilka rozwiązań, ostatecznie napisałem własną funkcję bindującą dane przechowywane w tablicy Array.

Cel

Dodanie wielu wierszy (~ 3,6 tys) do bazy danych przy pomocy interfejsu PDO, wykorzystując bindowanie wartości.

(eng. Insert multiple rows using PDO Interface with binding values).

Problem

Złożoność obliczeniowa dostępnych snippetów znalezionych tutaj lub tutaj jest nie optymalna. Dla 100 wierszy powyższe rozwiązania są subiektywnie szybkie, dla 1000 wierszy, zapytanie wykonuje się blisko 46 sekund.

Rozwiazanie

Moje rozwiązanie dla 1000 wierszy wykonało się w ~ 3 sekundy.

Dla 3700 wierszy funkcja potrzebowała 3,5 sekundy aby przetworzyć dane. Na czas ten wpłynęły poniższe czynniki:

  1. Pobranie pliku .csv z zewnętrznego serwera (feed produktowy)
  2. Wykonanie instrukcji Insert lub Update
    1. jeżeli wiersz o takim samym kluczu (key) nie istnieje w bazie danych, wykonywana jest instrukcja INSERT
    2. Jeżeli wiersz o takim samym kluczu (key) istnieje w bazie danych, wykonywana jest funkcja ON DUPLICATE KEY UPDATE

Poniższa funkcja jest wykonywana przez cron’a zaparkowanego na serwerze. Artykuł poświęcony cron’owi pojawi się wkrótce..

Poniżej funkcja:

/**
 * Insert Multiple Row Data to database using PDO Binding
 *
 * @param array $tableName name of table in database
 * @param array $data array of data
 * @param array $fields array of column name
 * @param bool $updateDuplicate flag: if true: ON DUPLICATE KEY UPDATE
 * 
 * @author Paweł Ryt <ryt.pawel@gmail.com>
 * @return $status 
 */ 
function insertMultipleRowsToDB ( $tableName = '', $data = null, $fields = null, $updateDuplicate = true ) { 
    global $query, $user, $pass;

    $status = false;
    $sqlQuery = '';
    $prepareData = [];

    $sqlQuery = 'INSERT INTO ' . $tableName;

    // (`col_name_1`,`col_name_2`,`col_name_3`,`col_name_4`,`col_name_5`) 
    $prepareInsertFields = "`" . implode("`,`", $fields) . "`";
    $sqlQuery .= ' (' . $prepareInsertFields . ')';
    
    $sqlQuery .= ' VALUES ';

    /* This loop return bind variable:
        ( :0_variable_1, :0_variable_2, :0_variable_3, :0_variable_4, :0_variable_5 ),
        ( :1_variable_1, :1_variable_2, :1_variable_3, :1_variable_4, :1_variable_5 ),
        ...
        ( :n_variable_1, :n_variable_2, :n_variable_3, :n_variable_4, :n_variable_5 )
    */
    $index = 0;
    foreach ($data as $key => $product) {
        $prepareInsertValues = ":" . $index ."_" . implode(", :" . $index ."_" , $fields);
        $sqlQuery .= '( ' . $prepareInsertValues . ' ),';

        $index += 1;
    }
    // remove last char ","
    $sqlQuery = substr($sqlQuery, 0, -1); 

    /* matching bind variable and values 
        [0_variable_1] => 'John'
        [0_variable_2] => 'Doe'
        [0_variable_3] => 'Canada'
        [0_variable_4] => 'EU'
        [0_variable_5] => 'Male'

        [1_variable_1] => 'Jane'
        [1_variable_2] => 'Grecky'
        [1_variable_3] => 'Canada'
        [1_variable_4] => 'EU'
        [1_variable_5] => 'Female'
        ...
        [n_variable_1] => 'Will'
        [n_variable_2] => 'Smith'
        [n_variable_3] => 'Mexico'
        [n_variable_4] => 'America'
        [n_variable_5] => 'Male'
    */
    $index = 0;
    foreach ($data as $singleRow) {
        foreach ($fields as $field) {
            $arrayValue = '';
            $arrayKey = $index ."_" . $field;
            if ( isset($singleRow[ $field ]) ) {
                $arrayValue = $singleRow[ $field ] ;
            }
            $prepareData[$arrayKey] = $arrayValue;
        }
        $index += 1;
    }

    if ( $updateDuplicate ) :
        $sqlQuery .= 'ON DUPLICATE KEY UPDATE ';

        foreach ($fields as $value) {
            $sqlQuery .= $value .'= VALUES('.$value.'),';
        }

        $sqlQuery = substr($sqlQuery, 0, -1); // remove last char ","
    endif;

    try {
        $pdoInsert = new PDO($query, $user, $pass);

        $pdoInsert->exec("set names utf8mb4");

        $pdoInsertSQL = $pdoInsert->prepare($sqlQuery);
        $status = $pdoInsertSQL->execute($prepareData);
    } catch (PDOException $e){
        $status = $e;
    }

    return $status;
}

Szczegółowy opis:

Funkcja insertMultipleRowsToDB przyjmuje 4 parametry:

  • $tableName – string – nazwa tabeli w bazie danych, dla której chcemy dokonać operacji INSERT/UPDATE
  • $data – array – tablica assocjacyjna w której przechowywane są dane, które zostaną dodane do bazy danych
  • $fields – array – tablica zawierająca nazwy kolumn w bazie danych
  • $updateDuplicate – boolean – flaga odpowiadająca za instrukcję ON DUPLICATE KEY UPDATE.
    true:
    • Jeżeli istnieje duplikat (tj. w bazie istnieje już taki klucz), to pozostałę pola tego wierszu zostaną nadpisane
    • Jeżeli w bazie nie istnieje taki sam klucz, zostanie wykonana instrukcja Insert.

Zmienna $sqlQuery przechowuje finalną postać całego zapytania. Wartość zmiennej finalnie, w trakcie przekazania do funkcji $pdoInsert->prepare() wygląda mniej więcej tak:

INSERT INTO 
    nazwa_tabeli (`col_name_1`,`col_name_2`,`col_name_3`,`col_name_4`,`col_name_5`)
VALUES
    ( :0_variable_1, :0_variable_2, :0_variable_3, :0_variable_4, :0_variable_5 ),
    ( :1_variable_1, :1_variable_2, :1_variable_3, :1_variable_4, :1_variable_5 ),
    ( :2_variable_1, :2_variable_2, :2_variable_3, :2_variable_4, :2_variable_5 ),
    ( :n_variable_1, :n_variable_2, :n_variable_3, :n_variable_4, :n_variable_5 )
ON DUPLICATE KEY UPDATE
    col_name_1 = VALUES (col_name_1),
    col_name_2 = VALUES (col_name_2),
    col_name_3 = VALUES (col_name_3),
    col_name_4 = VALUES (col_name_4),
    col_name_5 = VALUES (col_name_5)

Zmienna $prepareData przechowuje bindowane wartości. Wartość zmiennej w trakcie przekazania do funkcji $pdoInsertSQL->execute() wygląda tak:

[0_variable_1] => 'John'
[0_variable_2] => 'Doe'
[0_variable_3] => 'Canada'
[0_variable_4] => 'EU'
[0_variable_5] => 'Male'

[1_variable_1] => 'Jane'
[1_variable_2] => 'Grecky'
[1_variable_3] => 'Canada'
[1_variable_4] => 'EU'
[1_variable_5] => 'Female'
...
[n_variable_1] => 'Will'
[n_variable_2] => 'Smith'
[n_variable_3] => 'Mexico'
[n_variable_4] => 'America'
[n_variable_5] => 'Male'

Alternatywne rozwiązania:

Pojedyńczy INSERT przy użyciu interfejsu PDO:

$sql = "INSERT INTO users (name, surname, sex) VALUES (?,?,?)";
$stmt= $pdo->prepare($sql);
$stmt->execute([$name, $surname, $sex]);

Insert wielu wierszy jednocześnie przy użyciu pętli foreach:

Problemem tego rozwiązania jest wykonanie funkcji execute() dla KAŻDEGO wiersza. Problemem poniższego kodu jest złożoność obliczeniowa. Poniższy kod dla 1000 wierszy wykonywał się ~ 45 sekundy. Dla 3,6 tysięcy wierszy kod nie wykonał się wcale – serwer zwrócił error 5XX. 

function insertToDatabaseProductsForeach ( $data = null ) {
    global $query, $user, $pass;

    $status = false;
    $dateNow = date('Y-m-d H:i:s');

    try {
        $pdoInsert = new PDO($query, $user, $pass);

        // prepare the SQL query once
        $stmt = $pdoInsert->prepare("INSERT INTO products SET price_net = ?, data_update = ?");
        
        $pdoInsert->beginTransaction();
        
        // loop over the data array
        foreach ($data as $row) {
            $stmt->execute([$row['price_tax_free'], $dateNow]);
        }

        $pdoInsert->commit();

        $status = true;
    } catch (PDOException $e){
        $status = $e;
    }

    return $status;
}

You may also like...

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *