[gelöst]Einfügen von Ca 10.000 Einträgen in MySQL

[alter_Bekannter]

Zunächste mal: ganz klassisch und angeblich falsch in der Schleife gehts.

Was leider nicht funktioniert ist die schnellere Variante mit einem großen Query aus ~10.000 Inserts.

Genaue zahlen kann ich nicht nennen weil ich die Daten aus einer json Datei Extrahiere die ich hier gerne reinstellen kann.
Aber ich glaube auf ein paar Tausend Einträge kommts da eh nicht mehr an.

Besagte json:
http://mtgjson.com/json/AllCards.json

Insertquery:
[src=php]
$card_insert_query = "INSERT INTO `cards`
(`id`, `name`, `layout`, `manaCost`, `cmc`, `colors`, `type`,
`types`, `subtypes`, `text`, `power`, `toughness`, `imageName`)
VALUES
(NULL,
'" . @$card["name"] . "', '" . @$card["layout"] . "', '" . @$card["manaCost"] . "', '" . @$card["cmc"] . "', '" . $colors . "', '" . normalize_text(@$card["type"]) . "',
'" . $types . "', '" . $subtypes . "', '" . normalize_text(@$card["text"]) . "', '" . @$card["power"] . "', '" . @$card["toughness"] . "', '" . @$card["imageName"] . "');";[/src]

Die Daten sind zumindest in weiten Teilen ok, denn auf die "falsche" Variante gehts ja, allerdings verliert SQL da scheinbar seine Fähigkeit richtig zu zählen...

Fehlermeldungstechnisch siehts übrigens mau aus, hab noch nichts gefunden, daher hab ich auch vergessen es zu erwähnen.
SQL File vom geglückten Import über Querys in der Schleife kann ich bei Interesse auch hochladen, aber das Ding ist 2,5MB groß und enthält tendenziell keine Informationen, daher lass ich das erstmal.

Lösung:
mysqli Erweiterung benutzt, ist sowieso mittlerweile empfohlen, was solls...

Da gibts ne Multiquery Funktion, damit klappts.

Achtung:
Die Funktion startet mindestens einen zusätzlichen Thread!
Das Skript ist durchgelaufen lange bevor die Inserts alle drin sind!

Bei mir führt das zwar jetzt nicht zu Problemen aber dieses Verhalten sollte definitiv nicht Standard sein, so verhalten sich PHP Skripte einfach üblicherweise nicht.
Ist natürlich eine sinnvolle Ergänzung als Option, kein Zweifel.

 

[epiphora]

Re: Einfügen von Ca 10.000 Einträgen in MySQL

14775 Datensätze sind's, um genau zu sein.

Ich hab noch nicht genau verstanden, wo jetzt das eigentliche Problem liegt! Willst Du die Datei einfach einmalig importieren oder suchst Du eine saubere Möglichkeit, das JSON-Dokument als Tabelle in Deine Datenbank zu importieren?

Eine Fehlerquelle könnte es übrigens sein, dass Du möglicherweise vergisst, Anführungszeichen zu ersetzen. Einfache Anführungszeichen kommen nämlich teilweise auch im name-Attribut vor beim Basteln vom Query scheinst Du den nicht noch extra zu escapen.

 

[alter_Bekannter]

Re: Einfügen von Ca 10.000 Einträgen in MySQL

Stimmt, das sollte ich auf jeden Fall noch abdecken. Stimmt, das eigentliche Problem, tja das Problem ist das nichts feststellbares passiert.

Eigentlich müsste das Error log doch quasi explodieren bei so vielen Fehlern, aber nichts.

Ich hatte zwar Zeitweise diesen Fehler:
Mysql Server has gone away

Aber den hab ich dann einfach mit der Erhöhung von "max_allowed_packet" behoben.
Mir fällt gerade noch was ein, hat XAMPP im speziellen da vielleicht irgendwelche seltsamen Eigenarten in den Loggingdirektiven oder so?
Win 7 64Bit, allerdings weiss ich nicht ob das XAMPP auch 64Bit ist. Hab jetzt außer einem wenig wichtigen Kimsufi2(den ich dazu jetzt
nicht verwenden will) kein Linuxsystem zum gegenprüfen.

Ziel ist übrigens sowohl als auch, beides, wenn ich das jetzt schon mache wollte ich es gleich auch richtig machen.

Edit:
Durch das Escapen sinds jetzt zumindest schonmal deutlich mehr Einträge geworden. Dummer Fehler... War ja aber eh nur ne lokale Testumgebung.


Update:
Irgendwie kommts mir jetzt lächerlich vor:
Wenn ich den erzeugten Query mit echo ausgeben lasse und dann über phpmyadmin ausführen lasse(copy&paste), dann gehts.
Wenn ich den erzeugten query aber selber mit PHP also
mysql_unbuffered_query($cards_insert_query);
oder
mysql_query($cards_insert_query);
Ausführen lasse gehts nicht, ich guck jetzt mal ob die error Funktion was ausspuckt aber daran kann ich nicht so recht glauben wenns ja über phpmyadmin mit dem selben Query wie erwartet geht...

 

[accC]

Re: Einfügen von Ca 10.000 Einträgen in MySQL

Ich glaube mysql_query stört sich an ";" am Ende des Strings. Aber das sollte eigentlich dann zu 0 Einträgen führen. Daher bin ich mir nicht sicher.

 

[alter_Bekannter]

Re: Einfügen von Ca 10.000 Einträgen in MySQL

Nö, das isses auch nicht, gerade probiert. DIe Fehlermeldung ist übrigens Erwartuungsgemäß ziemlich seltsam(und ändert sich auch dadurch nicht):

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO `cards`
(`id`, `name`, `layout`, `manaCost`, `cmc`, `colors`, `ty' at line 10

Aus irgendeinem Grund schneidet er scheinbar den Query ab. Da ich den jetzt aber auch extrem verkürzt habe und die Grenzen erhöht habe kann es an der Länge nicht mehr liegen...
Ich versuche ja schließlich schon nur noch 3 auf einmal. Und in phpmyadmin hab ich 20 getestet.

Edit:
Hätte früher mal: "multiple querys PHP" oder sowas googlen sollen.
mysql_query() unterstützt nur genau einen query auf einmal, mehrere Inserts auf einmal ist also nicht. Brauche ne andere Funktion oder es wird aufwendiger.

 

[theSplit]

Offtopic aber zum Thema SQL, ich will da keinen extra Thread zum Thema aufmachen

Wie definiert und durchsucht man eigentlich ein Array als Datenstruktur in SQL?
Gibt es dabei nur die Option für eine ID eine Tabelle mit "ID" und "Wertzuordnung" anzulegen oder kann man zum Beispiel einen String von "1, 2, 3" in SQL direkt auswerten lassen?
Finde es ganz spannend, weil das z.B. in MongoDB super einfach geht und Arrays ganz normal indexiert und ausgewertet werden können und einfach irgendwo "enthalten" sein können in dem dazugehörigen Datensatz.

Es geht mir jetzt auch nicht darum MongoDB hier als bessere Lösung darzustellen, aber da SQL nun ja doch (noch) weltweit die Oberhand hat als Datenbankformat - wie bildet man dort einen solche Datenstruktur ab?

 

[alter_Bekannter]

Wie viele Einträge soll das Array haben?

Das Array bekommt ne Nummer und die Einträge in eine Separate Tabelle. Geht problemelos auch bei sehr großen Arrays.

 

[accC]

Wie du ein Array definierst?


Am Beispiel von PHP:
Wenn du etwas hast wie ..

[src=php]$myarray = array(
0 => "asdf",
1 => "test",
2 => "bla",
3 => "blup"
);[/src]

.. und willst das jetzt in MySQL speichern, dann würde ich eine Tabelle anlegen, etwa:

[src=mysql]CREATE TABLE `myarray` (
`index` INT(225) PRIMARY,
`value` varchar(25) STRING
);[/src]

Jetzt kannst du die Werte in die Tabelle ablegen..

[src=php]$handle = @mysql_connect($host, $name, $pass);
if(!$handle){
die("Sorry, no connection..");
}
mysql_select_db($database);
$query_str = "INSERT INTO `myarray` (ìndex, value) VALUES";
foreach($myarray as $index => $value){
$query_str = " (" . mysql_real_escape_string($index, $handle) . ", " . mysql_real_escape_string($value, $handle) . "), ";
}

$query_str = substr($query_str, 0, -2); // überflüssiges ", " entfernen
$query_ret = @mysql_query($query_str, $handle);

if($query_ret){
echo "I'm done..";
}else{
die("Somehting went wrong: <code>" . $query_str . "</code>");
}[/src]

Suchen kannst du dann einfach mit einem SELECT

[src=mysql]SELECT * FROM `myarray` WHERE `index` = "3"[/src]

[src=mysql]SELECT * FROM `myarray` WHERE `value` = "test"[/src]


Das kann man natürlich noch beliebig ausbauen. Man könnte sich Typen, Namespaces usw. zu Variablen merken.
Für's Beispiel wollte ich es aber weitestgehend simpel halten.

--- [2014-10-04 23:00 CEST] Automatisch zusammengeführter Beitrag ---

Gibt es dabei nur die Option für eine ID eine Tabelle mit "ID" und "Wertzuordnung" anzulegen oder kann man zum Beispiel einen String von "1, 2, 3" in SQL direkt auswerten lassen?

MySQL bringt, wie viele andere Datenbank(management)systeme viele Operationen für Strings mit, genaueres kannst du da in der jeweiligen Doku nachlesen.

Es geht mir jetzt auch nicht darum MongoDB hier als bessere Lösung darzustellen, aber da SQL nun ja doch (noch) weltweit die Oberhand hat als Datenbankformat

Nun zunächst mal ist MongoDB ein spezielles DBMS, eben ein NoSQL (not only SQL ~ nicht nur SQL) fähiges DBMS. Wenn du es vergleichen willst, müsstest du es einem speziellen DBMS aus der klassischen SQL Welt entgegen stellen, etwa MySQL (MySQL, PostgreSQL, MSSQL, SQLite). Wenn du lediglich die Technik vergleichen möchtest, dann solltest du NoSQL und SQL gegenüberstellen. Wobei wir bei letzterem ein bisschen mogeln, denn bei NoSQL gibt es verschiedene Systeme (klick).

MongoDB als prinzipiell SQL Datenbanken überlegen zu bezeichnen wäre auch ziemlich töricht. Natürlich bringen verschiedene Architekturen für NoSQL Datenbanken (bspw. Key-Value Store, Graphen- oder dokumentbasierte Datenbanken) klassischen SQL-Datenbanken in einigen Fällen Vorteile mit, allerdings werden diese Vorteile eben auch teuer erkauft. Welche Datenbank sich am ehesten eignet, hängt immer vom Einsatzzweck ab. NoSQL-Datenbanken eignen sich beispielsweise niemals zur Kontenverwaltung. Dafür sind facebook, twitter oder google besser damit bedient, als etwa mit MySQL.. Ob ein Tweet nun heute oder morgen oder ggf. "irgendwann" mal auf irgendeinem der Twitterserver liegt spielt im Prinzip keine Rolle, auch ob eine Änderung an einem Beitrag jetzt, in 10 Minuten oder eben erst morgen live geht, spielt keine Rolle. Für eine Bank könnte es aber tödlich werden, wenn du dich nicht mehr zuverlässig auf Konsistenz des Systems verlassen kannst.

wie bildet man dort einen solche Datenstruktur ab?

Siehe den Post-Teil weiter oben.. Ist jetzt natürlich nur ein Beispiel..

 

[theSplit]

Wie du ein Array definierst?


Am Beispiel von PHP:
Wenn du etwas hast wie ..

[src=php]$myarray = array(
0 => "asdf",
1 => "test",
2 => "bla",
3 => "blup"
);[/src]

.. und willst das jetzt in MySQL speichern, dann würde ich eine Tabelle anlegen, etwa:

[src=mysql]CREATE TABLE `myarray` (
`index` INT(225) PRIMARY,
`value` varchar(25) STRING
);[/src]

Jetzt kannst du die Werte in die Tabelle ablegen..

Genau, das war ja auch das Problem/Frage welche ich schilderte.
Du nimmst für jeden Wert im Array einen eigenen Datenbankeintrag mit "index" und "value".

Geht das nicht sinnvoll anders so das wir das Array auch so in der Datenbank hätten wie es behandelt wird?
Also von mir aus einen String mit den Elementen? "asdf, test, bla, blupp" - der aber durchsucht werden kann?

Gibt es dabei nur die Option für eine ID eine Tabelle mit "ID" und "Wertzuordnung" anzulegen oder kann man zum Beispiel einen String von "1, 2, 3" in SQL direkt auswerten lassen?

MySQL bringt, wie viele andere Datenbank(management)systeme viele Operationen für Strings mit, genaueres kannst du da in der jeweiligen Doku nachlesen.

Kann man denn nun einen String, der ein Array beinhaltet, sinnvoll durchsuchen? Fast zu 90% eine "Ja", "nein" Frage.

*Edit:

MongoDB als prinzipiell SQL Datenbanken überlegen zu bezeichnen wäre auch ziemlich töricht. Natürlich bringen verschiedene Architekturen für NoSQL Datenbanken (bspw. Key-Value Store, Graphen- oder dokumentbasierte Datenbanken) klassischen SQL-Datenbanken in einigen Fällen Vorteile mit, allerdings werden diese Vorteile eben auch teuer erkauft. Welche Datenbank sich am ehesten eignet, hängt immer vom Einsatzzweck ab. NoSQL-Datenbanken eignen sich beispielsweise niemals zur Kontenverwaltung. Dafür sind facebook, twitter oder google besser damit bedient, als etwa mit MySQL.. Ob ein Tweet nun heute oder morgen oder ggf. "irgendwann" mal auf irgendeinem der Twitterserver liegt spielt im Prinzip keine Rolle, auch ob eine Änderung an einem Beitrag jetzt, in 10 Minuten oder eben erst morgen live geht, spielt keine Rolle. Für eine Bank könnte es aber tödlich werden, wenn du dich nicht mehr zuverlässig auf Konsistenz des Systems verlassen kannst.

MongoDB, kann, auch für Konsistenz garantieren, dafür gibt es Safe-Writes der genau zurückgibt ob der Wert auch wirklich gespeichert werden konnte. Aber das nur mal an Rande, da es mir auch nicht darum ging darüber eine Diskussion zu entfachen

 

[accC]

Genau, das war ja auch das Problem/Frage welche ich schilderte.
Du nimmst für jeden Wert im Array einen eigenen Datenbankeintrag mit "index" und "value".

Geht das nicht sinnvoll anders so das wir das Array auch so in der Datenbank hätten wie es behandelt wird?
Also von mir aus einen String mit den Elementen? "asdf, test, bla, blupp" - der aber durchsucht werden kann?

Ich bin mir recht sicher, dass zumindest MySQL von Haus aus keine Datenstruktur "Array" kennt. Falls du das meinst, also nein, du kannst MySQL nicht sagen..

[src=mysql]CREATE TABLE mystorage (
`variable_name` STRING
`variable_content` ARRAY
);[/src]

und dann ganze Arrays ablegen

[src=mysql]INSERT INTO mystorage (`namensliste`, array('Peter', 'Hans', 'Nicole', 'Laura'));[/src]

[src=mysql]SELECT * FROM `mystorage` WHERE IN_ARRAY(`variable_content`, `Peter`);[/src]

So etwas ist zumindest mir nicht bekannt, das würde auch mWn auch bereits der ersten Normalform von SQL widersprechen.

 

[theSplit]

Danke dir - ich hab auch nur Ansatzweise von "Sets" gelesen, als ich mal sporadisch danach gesucht habe wie man ein Array abbilden könnte, aber das ist dann vermutlich dann ein etwas wenig anderes als das was ich hier gerade als Array verstehe.

Aber um dazu jetzt etwas sinnvolles zu zu sagen, ich hab mich mit den Datentypen, die nicht gleich logisch nachvollziehbar sind wie "int, char, vchar...", also die anderen, nicht beschäftigt.

 

[accC]

MongoDB, kann, auch für Konsistenz garantieren, dafür gibt es Safe-Writes der genau zurückgibt ob der Wert auch wirklich gespeichert werden konnte. Aber das nur mal an Rande, da es mir auch nicht darum ging darüber eine Diskussion zu entfachen

Dass MongoDB eine Dokumenten basierte Datenbank ist, wie diese funktionieren, wozu sie entwickelt wurden und wozu nicht, ist dir klar, oder?

Es kommt wie gesagt auf den Einsatzzweck an.

 

[theSplit]

Ich hab mich dazu zu wenig mit Datenbanken beschäftigt um Aussagen treffen zu können ob X oder Y besser sind - ich glaube das kann man nicht so einfach pauschalisieren - dafür müßte man sich genau mit den Unterschieden und den Anwendungen auseinander setzen die darauf zugreifen bzw. Daten ablegen und wie diese gebraucht werden. Und vermutlich, das schriebst du ja selbst, kommt es auf diesen Anwendungsfall an.

Es gab da mal so eine Tabelle zwischen "Daten-Sicherheit/Integrität", "Performance" und "Anforderungen" (an Hardware) - aber das ist schon länger her das ich mich aktiv damit beschäftigt habe - daher kann ich das gerade auch gar nicht alles so wiedergeben.

 

[accC]

Ja, ich habe da auch eine Übersicht im Kopf, die gezeigt hat, welches DBMS wo seine Stärken und wo seine (relativen) Schwächen (gegenüber den anderen) hat und wann man welches am besten einsetzt. Falls ich die Grafik dazu noch mal finde, poste ich sie hier irgendwo.

Allerdings gehört das ja nicht unbedingt zum Problem des TS.

 

[Kugelfisch]

Zum ursprünglichen Thema:

Was leider nicht funktioniert ist die schnellere Variante mit einem großen Query aus ~10.000 Inserts.

Dieser Ansatz ist nicht signifikant besser als in einer Schleife einzelne INSERT-Queries zu erzeugen, weil es sich dabei nach wie vor um unabhängige Queries handelt. Schneller (und ohne Multiquery-Funktionalität realisierbar) wäre ein einzelnes INSERT-Query der Form
[src=mysql]INSERT INTO `cards` (`name`, `layout`, `manaCost`, `cmc`, `colors`, `type`, `types`, `subtypes`, `text`, `power`, `toughness`, `imageName`) VALUES
( ... [Datensatz 1] ...),
( ... [Datensatz 2] ...),
[...]
( ... [Datensatz 14775] ...)
[/src]
welches dann nur einmal geparst werden müsste. Auch eine Schleife, welche ein Prepared Statement nutzt, das einmal vor der Schleife präpariert und bei jedem Schleifendurchlauf bloss mit neunen Daten gefüllt wird, wäre effizienter (und liesse zudem die Maskierungsproblematik entfallen).


Zur Abbildung von Arrays in MySQL:
MySQL ist ein relationales DBMS, daher werden mit Absicht nur primitive Datentypen unterstützt. 1:n- oder n-Relationen, wie sie Arrays üblicherweise sind, lassen sich durch mehrere Tabellen und entsprechende Fremdschlüssel abbilden, was zusätzlich den Vorteil bringt, dass Daten nicht redundant vorgehalten werden (Normalform). Einige Beispiele, welche auch die Abbildung von Arrays beinhalten, finden sich unter https://de.wikipedia.org/wiki/Normalisierung_(Datenbank).

 

[evillive]

Ein Array (mysql)

[src=mysql]CREATE TABLE IF NOT EXISTS `array` (
`n` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `array` (`n`) VALUES
(2),
(3),
(1),
(7);[/src]

Wenn du jetzt das dritte Element aus dem Array haben willst:
[src=mysql]
SELECT n FROM `array` limit 2,1[/src]

 

[accC]

Du hast lediglich eine Tabelle (ohne Indizes) angelegt, was nichts anderes ist, als das, was ich bereits in #8 gezeigt habe, nur eben mit Indizes. Ein Array, so wie danach gefragt wurde, existiert allerdings nicht, da es wie gesagt gegen die Normalformen verstoßen würde.

 

[evillive]

nach was wurde gefragt?

 

[alter_Bekannter]

@Kugelfisch:

Danke für den Hinweis. wie würde denn die Variante mit einem prepared Statement aussehen? Das kenne ich nur vom hören.

 

[accC]

nach was wurde gefragt?

Das, was ich bereits in #10 skizziert habe, eine Datenstruktur Array.