INSERT dla ponad 100 tys. rekordów

0

Witam,
Napisałem skrypt dla wrzucania do bazy danych informacji przechowywanych w pliku .json(zmienna $data).

foreach ($data as $k) {  
		$brand = $data['data'][$k]['brand'];
		$query = $polaczenie->query("INSERT INTO `brand`(id_brand,name) VALUES (".$k.",'".$brand."')"); 
}
	if ($query === false){
		echo '<p>Zapytanie nie zostało wykonane!</p>';
		$polaczenie -> close();
	}
	else {
		echo '<p>Zapytanie zostało wykonane!</p>';
		$polaczenie -> close();
	}

Problem w tym, że o ile skrypt działa dla 100 danych to dla całości(około 100 tys. ) już nie ponieważ pojawia się błąd:

Notice: Undefined offset: 100 000 in C:...

Jak go zoptymalizować lub poprawić aby wykonywał się dla całości?

0

Po jednym rekordzie wrzucasz? 100tys? Się możesz wyniku nie doczekać. Popatrz jak to np. backup robi...

0

Mógłbym prosić o jakiś przykładowy kod?

1

https://dev.mysql.com/doc/refman/5.7/en/insert.html
INSERT INTO .... VALUES (...), (...), (...),...
Albo jeszcze szybsze (ale wiąże się ze skopiowaniem pliku na serwer):
https://dev.mysql.com/doc/refman/5.7/en/load-data.html

0

Przerobiłem kod na:

foreach ($data as $k => $value) {
	$brand = $data[$k]['brand'];
	$values_string = $values_string." (".$k.",'".$brand."'), ";
}
$values_string=rtrim($values_string,", ");  // usuwam przecinek z ostatniego nawiasu
$query = $polaczenie->query("INSERT INTO brand (id_brand,name) VALUES ".$values_string); 

i zapytanie nadal działa dla 100 danych a dla 1000 już nie :(

0

A jakiś błąd dostajesz?

0

Owszem ale nic konkretnego, mam takie przechwytywanie

	if ($query === false){
		echo '<p>Zapytanie nie zostało wykonane!</p>';
		$polaczenie -> close();
	}
	else {
		echo '<p>Zapytanie zostało wykonane!</p>';
		$polaczenie -> close();
	}

które zwraca mi "Zapytanie nie zostało wykonane!"

0
  1. name jest słowem kluczem w MySQL - powinieneś dać je w grawisach - INSERT INTO brand (`id_brand`,`name`) ...
  2. 1000 może być za długim stringiem. phpMyAdmin bodajże 500 rekordów na raz bierze.
    i jeszcze spróbiuj włączyć błędy i przed wykonaniem wypisać to zapytanie...
    może gdzię tam siedzi np. cudzysłów...
0

Myślę, że nie jest to wina name(ponieważ mniejszą liczba rekordów się dodaje) choć faktycznie warto zwracać uwagę na takie rzeczy. Co proponujecie w takim przypadku?

0

Może przekraczasz max_allowed_packet https://dev.mysql.com/doc/refman/5.7/en/packet-too-large.html
Czego używasz do puszczenia zapytania, może warto przechwycić błąd niż strzelać na ślepo...

0

W sumie to nie musi być INSERT. Po prostu chcę dane w formacie .json wrzucić do tabel w phpmyadmin. Konieczny jest podział ponieważ informacje o kluczu np. produkt chce mieć w innej tabeli, informacje o kluczu firma chce mieć w innej tabeli itp.

1

To czemu nie wygenerujesz pliku sql/csv i nie zaimportujesz w phpmyadmin: https://docs.phpmyadmin.net/en/latest/import_export.html#import

2

Da radę zautomatyzować, wygeneruj CSV i użyj LOAD DATA INFILE https://dev.mysql.com/doc/refman/5.7/en/load-data.html

1

W pierwszym przykładzie twój if sprawdza wynik tylko ostatniego zapytania w pętli, zapewne powinien być wewnątrz pętli.
Pytanie czy to co robisz to jednorazowe zadanie, czy do wielokrotnego wykonania ?
czy uruchamiasz ten skrypt przez serwer www , czy w shellu ?

z pewnością możesz wrzucać wiele rekordów w pętli jako osobne zapytania, więc być może w którymś zapytaniu jest błąd, jak ktoś zasugerował,
za zacząłbym od logów, mysql i php, i doczytał o jaki dokładnie błąd chodzi.

jeśli możesz uruchom ten skrypt w shellu, sprawdź logi

1

Sprawdź też ten JSON. Na pewno każdy obiekt w tym pliku ma wartość do której próbujesz się dostać? Powinieneś sprawdzić ifem czy taki istnieje.

0

Rozwiązałem problem(okazała się to po prostu literówka :( ). Chciałbym jednak do mojego skryptu dodać transakcje aby wykonywał się bezpieczniej, np zapis danych byłby wykonywany po 2000 wierszach bo jak wspominałem moje dane są zmienne i skrypt będzie się wykonywał co jakiś czas automatycznie. Jak takie coś napisać? Nie miałem z tym styczności a myślę, że to jest to bardzo ważne...

1

Transakcja na część inserta? Wsadzasz 100k wierszy, wywali się po 53k, zawiną się ostatnie 2k, będziesz mieć dodane i zapisane 52k (bo modulo 2k)... Przecież to nie ma sensu.
Mam wątpliwości, czy dodawanie tak dużej ilości danych w transakcji nie "zabije" bazy danych - na zablokowanych tabelach będą czekać albo wywalać się inne zapytania, ewentualnie duża ilość zasobów serwera bazy danych zostanie zużyta na snapshota takiej transakcji.
Przemyśl to. Najlepiej użyj dedykowanego rozwiązania dla dodawania ta dużej ilości danych - koledzy już podali linki (bulk insert).

1

Transakcja nie sprawi, że operacja będzie bardziej bezpieczna. Założenie transakcji jest takie, że wszystko od a do z w niej wykonujesz, ty to jednak chcesz rozdzielić na paczki, wystarczy, że jedna nie zadziała poprawnie i całość jest nieprawidłowa, więc to tylko niepotrzebny narzut na bazę i cała operację. Nie szukaj młotka tylko używaj narzędzi bazy które służą do takich operacji (linki już masz).

1 użytkowników online, w tym zalogowanych: 0, gości: 1