Jaki jest najlepszy sposób na WSTAWIENIE dużego zestawu danych do bazy danych MySQL (lub dowolnej innej bazy danych ogólnie)

W ramach projektu PHP muszę wstawić wiersz do bazy danych MySQL. Oczywiście jestem do tego przyzwyczajony, ale wymagało to wstawienia do 90 kolumn w jednym zapytaniu. Wynikowe zapytanie wygląda okropnie i monolitycznie (zwłaszcza wstawianie moich zmiennych PHP jako wartości):

INSERT INTO mytable (column1, colum2, ..., column90) 
VALUES
('value1', 'value2', ..., 'value90')

i martwię się, że nie zamierzam tego robić we właściwy sposób. Dużo czasu zajęło mi też (nudne) wpisanie wszystkiego i obawiam się, że pisanie kodu testowego będzie równie nudne.

Jak profesjonaliści radzą sobie z szybkim pisaniem i testowaniem tych zapytań? Czy istnieje sposób na przyspieszenie tego procesu?

Joe, twój ostatni komentarz wiele wyjaśniał. Myślę, że prawdziwym problemem jest projektowanie danych. Nowe kolumny mogą być potrzebne, gdy zmienia się format dokumentu, a według mojego doświadczenia formaty dokumentów często się zmieniają. Zamiast 90-kolumnowej tabeli, z pojedynczym wierszem na raport, zapisałbym dane raportu w tabeli z czterema kolumnami: id_dokumentu, id_formatu, nazwa_pola, wartość_pola. Każdy raport będzie reprezentowany przez 90 wierszy, po jednym dla każdej wartości pola w raporcie. Powinno to znacznie uprościć kod.

Zasadniczo najszybszym sposobem na załadowanie dużego zestawu danych do bazy danych SQL jest użycie natywnego interfejsu ładowania zbiorczego. O ile mi wiadomo, każdy SQL dbms ma co najmniej jeden.

Dokumenty MySQL: Korzystanie z modułu ładującego luzem

Jeśli ja miał zamienić Tab- lub plik rozdzielany przecinkami do instrukcji SQL INSERT, używam awk odczytać pliku wejściowego i zapisać plik wyjściowy. W awk nie ma nic specjalnego; tak się składa, że ​​jest to język przetwarzania tekstu, który znam najlepiej. Możesz uzyskać te same wyniki, pisząc kod w Perl, Python, Ruby, Rexx, Lisp i tak dalej.

Jeśli możesz łatwo umieścić nazwy kolumn w arkuszu kalkulacyjnym Excel, możesz napisać makra Excela, aby wygenerować kod dla różnych zapytań i instrukcji DML, a następnie po prostu wkleić wartości do innej kolumny, a instrukcja wstawiania / aktualizacji zostanie utworzona automatycznie. Ręczne pisanie jest bardzo powolnym sposobem, aby to zrobić, więc sprawdź, czy możesz znaleźć sztuczki przy użyciu istniejących narzędzi. Wiele edytorów tekstu zorientowanych na programistów ma również możliwość rejestrowania i przechowywania makr, dzięki czemu powtarzalne zadania są znacznie szybsze i łatwiejsze.

Jeśli masz plik csv, możesz użyć LOAD DATA INFILE ... do importowania danych.

Jeśli musisz użyć zapytań „INSERT”, wówczas wykonywanie masowych wstawek przyspieszy proces. Zamiast uruchamiać zapytanie „INSERT” dla każdego wiersza, zgrupuj wiersze, powiedzmy 100 i uruchom zapytanie. Coś takiego:

INSERT INTO theTable (col1, col2, col3,....., col89, col90) 
VALUES
(val11, val12, val13, ........, val189, val190),
(val21, val22, val23, ........, val289, val290),
.......
......
(val101, val102, val103, ........, va1089, val1090);

Skutecznym sposobem zapisania danych zapytania w wielu kolumnach w MySQL DB jest konwersja tych danych do formatu JSON lub YAML i wstawienie ich jako pojedynczej jednostki. Zmienia „napisz wstawkę do tabeli z 90 kolumnami” na „napisz wstawkę do tabeli z jedną kolumną”.

W tym podejściu nie wszystko musi zostać podzielone na podstawowe komponenty, a pojedynczy układ odniesienia jest przechowywany tylko w 1 kolumnie.

Dzięki MySQL możesz użyć alternatywnej składni insertinstrukcji:

insert into table
        set column1 = value1
          , column2 = value2
          , column3 = value3

Twój scenariusz wygląda bardzo dobrze pasujący do rozwiązania NoSQL, ponieważ lista atrybutów może się zmieniać za każdym razem, gdy zmienia się format. Czy oceniłeś inne opcje niż MySQL? Kop wokół DynamoDB / MongoDB / Cassandra - to może być lepsze dopasowanie.

Istnieje bardziej wydajny sposób wstawiania danych do bazy danych przy użyciu php i mysql. Możemy użyć LOAD COMMAND do wstawienia danych. Wstawia dane niezwykle szybko.

W tym celu utwórz plik płaski (na przykład użyłem pliku .csv) z danymi za pomocą fputcsv()funkcji. Następnie wstaw dane za pomocą polecenia LOAD. Składnia niektóre podobne jak poniżej:

LOAD DATA LOCAL INFILE "C:/downloads/local/my_data_file.csv"
INTO TABLE  my_data
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Spróbuj wykonać następujące czynności. Pracował dla mnie.

Nazwy formularzy muszą być równe nazwom kolumn bazy danych

Uzyskaj wartości, jak poniżej:

foreach ($_GET as $formName => $value) {
    $sql = mysql_query("UPDATE table_name SET $formName = '$value' WHERE ID= $id");
}

Najpierw musisz wstawić identyfikator przed pętlą foreach. możesz uzyskać następny identyfikator, wykonując:

SELECT MAX(id) FROM .....

dodaj 1 do id i wstaw go.