Große Datensätze einfach und sicher in eine MySQL-Datenbank importieren
Jeder der schon einmal mit Datenbanken zu tun hatte, kennt das Problem: Wie bekomme ich den riesen Haufen an Datensätzen möglichst einfach in die Datenbank? Natürlich könnte man jetzt via insert Befehl jeden einzelnen Eintrag manuell oder über einen csv Import anlegen, was wenn ich aber auch Ausnahmebehandlungen haben möchte? Dieses Problem lösen wir mithilfe
Eine Prozedur ist an sich nichts anderes wie eine Art Funktion, die in der Datenbank angelegt wird. Der große Vorteil von Prozeduren ist der, dass diese Kontrollstrukturen wie If-Bedingungen oder auch Schleifen mit sich bringen.
Im folgenden Beispiel zeige ich, wie man einen Datensatz aus Excel einfach in eine Datenbank importieren kann. Als erstes benötigt man eine simple Exceltabelle Wie man hier schon sieht, kann es vorkommen, dass Spalten nicht korrekt oder gar nicht ausgefüllt werden. Wenn man diese Tabelle direkt in die Tabelle laden würde, hätte man uneinheitliche Einträge oder leere Felder. Die Spalte Lieblingsessen soll außerdem in Die Tabelle Essen gespeichert werden und dem Profil über die die Essen ID in der Tabelle Lieblingsessen zugeordnet werden. Die Excel muss als .csv abgespeichert werden. Danach benötigt man ein Programm wie Notepad++, um die Tabelle in UTF-8 ohne BOM zu konvertieren, da sonst die Umlaute nicht richtig angezeigt werden. Als nächstes erstellen wir die Tabelle in der Datenbank, um die csv zu importieren. Wichtig dabei ist, dass die Tabelle eine Spalte mit id hat, welche auf auto increment gesetzt ist. Diese Spalte wird für den späteren Import in die anderen Tabellen wichtig. (Screenshot aus HeidiSQL) Create Code :
CREATE TABLE `import_profiles` ( `ID` INT(255) NOT NULL AUTO_INCREMENT, `NAME` VARCHAR(255) NULL DEFAULT NULL, `VORNAME` VARCHAR(255) NULL DEFAULT NULL, `BENUTZER` VARCHAR(255) NULL DEFAULT NULL, `ADMIN` VARCHAR(255) NULL DEFAULT NULL, `LIEBLINGSESSEN` VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (`ID`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=4;
Danach importieren wir die csv Datei in die Tabelle. Dafür gibt es zwei Möglichkeiten: entweder man nutzt ein Tool wie HeidiSQL oder macht es mit folgendem Code:
LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\DEIN PFAD\\Essen.csv' REPLACE INTO TABLE `blog db`.`import_profiles` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`NAME`, `VORNAME`, `BENUTZER`, `ADMIN`, `LIEBLINGSESSEN`);
Der Befehl IGNORE 1 LINES führt dazu, dass die erste Zeile im Excel ausgelassen wird, in unserem Fall stehen dort die Spaltenbeschreibungen. Im nächsten Schritt wird die Prozedur erstellt. Das Grundgerüst sieht wie folgt aus:
CREATE PROCEDURE `add_data`() BEGIN ... END
Die Idee hinter dem Import ist folgende: Über eine Schleife werden alle Zeilen der Import Tabelle durchlaufen und in Variablen gespeichert, die dann anschließend in die jeweiligen Tabellen eingefügt werden. Die Schleife in unserer Prozedur sieht so aus:
#Start und Endvariable (So wird generell eine Variable definiert) SET @COUNTER = (SELECT MIN(ID) FROM import_profiles); SET @BREAK = (SELECT MAX(ID) FROM import_profiles); #Schleife LABEL1 : REPEAT ... #Abbruchbedingung UNTIL @COUNTER>@BREAK END REPEAT LABEL1;
Da es während der Ausführung zu Fehlern kommen kann (fehlende Einträge in csv o.ä.), erstellen wir in der Prozedur noch einen temporären TABLE. Dieser wird automatisch gelöscht, sobald die Sitzung beendet wird.
CREATE TEMPORARY TABLE IF NOT EXISTS BUGS ( PROFIL_ID INT(10), IMPORT_ID INT(10), NAME varchar(255), VORNAME varchar(255), `TEXT` TEXT ); #Leert den Table, falls die Prozedur mehrmals aufgerufen wird DELETE FROM BUGS;
Wenn das geschafft ist, definieren wir die zu importierenden Variablen.
LABEL1 : REPEAT SET @IMPORT_ID = @COUNTER; SET @NAME = (SELECT NAME FROM import_profiles where ID = @IMPORT_ID); SET @VORNAME = (SELECT VORNAME FROM import_profiles where ID = @IMPORT_ID); SET @ADMIN = (SELECT ADMIN FROM import_profiles where ID = @IMPORT_ID); SET @LIEBLINGSESSEN = (SELECT ID FROM essen where essen in(SELECT lieblingsessen FROM import_profiles where ID = @IMPORT_ID));SET @COUNTER = @COUNTER + 1; UNTIL @COUNTER>@BREAK END REPEAT LABEL1; SELECT * FROM BUGS; SELECT * FROM import_profiles where id in (select import_id from bugs);
Jetzt können wir die Daten in die bestehenden Tabellen importieren. In der Tabelle Profil befinden sich die Profildaten wie Name, Vorname, Adminstatus und Profil ID. In der Tabelle essen befinden sich Gerichte mit einer ID. In Lieblingsessen stehen die Profil ID und die Essen ID.
IF NOT EXISTS (SELECT 1 FROM profil where name = @name and vorname = @vorname) then INSERT INTO profil (NAME,vorname)(SELECT @name,@vorname); END IF; SET @profil_id = (SELECT ID from profil where name = @name and vorname = @vorname); IF (@admin = "ja") THEN UPDATE profil set admin = @admin where id = @profil_id; ELSEIF (@admin ="nein" or @admin ="-") then UPDATE profil set admin = "nein" where id = @profil_id; ELSE INSERT INTO bugs (PROFIL_ID,IMPORT_ID,NAME,VORNAME,TEXT)(SELECT @profil_id,@import_id, @name,@vorname,'Adminstatus prüfen'); END IF;
Zunächst wird geprüft, ob das Profil bereits existiert, wenn nicht, wird ein neues Profil angelegt. Danach wird die Profil ID in einer Variable gespeichert, da sie später noch in der Tabelle lieblingsessen benötigt wird. Danach wird geprüft, ob ein Adminstatus in dem Import Table gesetzt ist. Wenn nicht, bzw wenn der Status nicht erkannt wird, wird dieses Profil in die Bugtabelle geschrieben, was die Fehlersuche bei vielen Daten erheblich vereinfacht. Im letzten Schritt wird das Lieblingsessen eingefügt. Falls es das Essen noch nicht gibt, wird es in der Tabelle Essen angelegt.
if @lieblingsessen is null and (select lieblingsessen from import_profiles where id = @import_id) NOT LIKE "" then insert into essen (essen)(SELECT lieblingsessen from import_profiles where id = @import_id); SET @LIEBLINGSESSEN = (SELECT ID FROM essen where essen in(SELECT LIEBLINGSESSEN FROM import_profiles where ID = @IMPORT_ID)); end if; IF @LIEBLINGSESSEN IS NOT NULL THEN IF NOT EXISTS (SELECT 1 from lieblingsessen where profil_id = @profil_id) then insert into lieblingsessen (profil_id,essen_id) (select @profil_id,@lieblingsessen); ELSE update lieblingsessen set essen_id = @lieblingsessen; END IF; ELSE INSERT INTO bugs (PROFIL_ID,IMPORT_ID,NAME,VORNAME,TEXT)(SELECT @profil_id,@import_id, @name,@vorname,'Lieblingsessen prüfen'); END IF;
Wenn man alle Komponenten zusammenfügt, müsste es so aussehen:
CREATE PROCEDURE `add_data`() BEGIN SET @COUNTER = (SELECT MIN(ID) FROM import_profiles); SET @BREAK = (SELECT MAX(ID) FROM import_profiles); CREATE TEMPORARY TABLE IF NOT EXISTS BUGS ( PROFIL_ID INT(10), IMPORT_ID INT(10), NAME varchar(255), VORNAME varchar(255), `TEXT` TEXT ); DELETE FROM BUGS; LABEL1 : REPEAT SET @IMPORT_ID = @COUNTER; SET @NAME = (SELECT NAME FROM import_profiles where ID = @IMPORT_ID); SET @VORNAME = (SELECT VORNAME FROM import_profiles where ID = @IMPORT_ID); SET @ADMIN = (SELECT ADMIN FROM import_profiles where ID = @IMPORT_ID); SET @LIEBLINGSESSEN = (SELECT ID FROM essen where essen in(SELECT LIEBLINGSESSEN FROM import_profiles where ID = @IMPORT_ID)); IF NOT EXISTS (SELECT 1 FROM profil where name = @name and vorname = @vorname) then INSERT INTO profil (NAME,vorname)(SELECT @name,@vorname); END IF; SET @profil_id = (SELECT ID from profil where name = @name and vorname = @vorname); IF (@admin = "ja") THEN UPDATE profil set admin = @admin where id = @profil_id; ELSEIF (@admin ="nein" or @admin ="-") then UPDATE profil set admin = "nein" where id = @profil_id; ELSE INSERT INTO bugs (PROFIL_ID,IMPORT_ID,NAME,VORNAME,TEXT)(SELECT @profil_id,@import_id, @name,@vorname,'Adminstatus prüfen'); END IF; if @lieblingsessen is null and (select lieblingsessen from import_profiles where id = @import_id) NOT LIKE "" then insert into essen (essen)(SELECT lieblingsessen from import_profiles where id = @import_id); SET @LIEBLINGSESSEN = (SELECT ID FROM essen where essen in(SELECT LIEBLINGSESSEN FROM import_profiles where ID = @IMPORT_ID)); end if; IF @LIEBLINGSESSEN IS NOT NULL THEN IF NOT EXISTS (SELECT 1 from lieblingsessen where profil_id = @profil_id) then insert into lieblingsessen (profil_id,essen_id) (select @profil_id,@lieblingsessen); ELSE update lieblingsessen set essen_id = @lieblingsessen; END IF; ELSE INSERT INTO bugs (PROFIL_ID,IMPORT_ID,NAME,VORNAME,TEXT)(SELECT @profil_id,@import_id, @name,@vorname,'Lieblingsessen prüfen'); END IF; SET @COUNTER = @COUNTER + 1; UNTIL @COUNTER>@BREAK END REPEAT LABEL1; SELECT * FROM BUGS; SELECT * FROM import_profiles where id in (select import_id from bugs); END
Wenn man diesen Code ausführt, wird eine Prozedur erstellt. Um diese abzurufen, muss man folgendes abfragen :
call procedure add_data();
Wenn die Prozedur durchgelaufen ist, sollten alle Daten eingefügt worden sein. Auf den ersten Blick sieht das nach ziemlich viel Aufwand aus, geht aber relativ schnell und ist beliebig erweiterbar.
Das könnte Dich auch interessieren
SÜDKURIER Nachrichten auf Amazons Alexa
Alexa ist der Sprachassistent in Amazons Echo Lautsprecher. Aber wie bekommt man jetzt die Nachrichten vom SÜDKURIER auf Amazons Alexa? Natürlich kann Alexa d...
Digitale Transformation geht unter die Haut
Link zum content-manager.de-Artikel unten Echte digitale Transformation muss in die Tiefe eines Unternehmens gehen und betrifft auch die Unternehmenskultur.
ZEIT 2.0: So verlief unser Tag beim Halunkenhack
Es gibt bei den Mediafavoriten zwei Besprechungsräume, die man über Outlook buchen kann. Einer dieser Räume ist dabei besonders beliebt. Aber warum extra noc...