MariaDB-Kurs / CSV importieren und exportieren: Pfade, Rechte und Zeichenkodierung
Für einen CSV-Import in MariaDB müssen Trennzeichen, Anführungszeichen, Zeilenenden und Zeichenkodierung zum Dateiformat passen. Laden Sie zunächst den Originaltext in eine Zwischentabelle, prüfen Sie die Werte und wandeln Sie sie erst danach in die Zieltypen um. LOAD DATA LOCAL INFILE liest eine Clientdatei; SELECT INTO OUTFILE schreibt auf dem Server und verwendet deshalb andere Pfade und Berechtigungen.
Feststellen, welcher Computer die Datei verarbeitet
Comma-Separated Values (CSV) bezeichnet Textdaten mit durch Kommas getrennten Feldern; Werkzeuge verwenden allerdings auch andere Trennzeichen. Der Dateiname legt weder Regeln für Anführungszeichen und Maskierung noch Kodierung und fehlende Werte eindeutig fest. Stimmen Sie das Format mit dem erzeugenden und empfangenden Werkzeug ab.
| Operation | Dateisystem | Voraussetzungen |
|---|---|---|
| LOAD DATA LOCAL INFILE | Clientcomputer | LOCAL bei Client und Server erlaubt |
| LOAD DATA INFILE | Datenbankserver | FILE-Recht und serverseitige Pfadregeln |
| SELECT INTO OUTFILE | Datenbankserver | FILE, secure_file_priv und Betriebssystemrechte |
Erlauben Sie LOCAL nur gegenüber einem vertrauenswürdigen Server und berücksichtigen Sie die Dateien, die der Client lesen und übertragen kann. Erweitern Sie nicht pauschal globale Einstellungen oder Dateirechte, um einen Ladefehler zu verdecken. Die Übung zum Client und zu SQL-Dateien erklärt Dateipfade; die INSERT-Übung erläutert die Zuordnung von Spalten und Werten.
Eine UTF-8-Datei und eine Zwischentabelle vorbereiten
Erstellen Sie auf dem Client eine neue products.csv unter C:/mariadb-lab, ohne eine bestehende Datei zu überschreiben. Speichern Sie folgenden Inhalt als UTF-8, damit die Beispielzeichen erhalten bleiben. Die erste Zeile ist die Kopfzeile. Der erste koreanische Produktname bedeutet „großes Notizbuch“ und enthält selbst ein Komma; die Anführungszeichen halten ihn als ein Feld zusammen.
product_id,product_name,price
1,"노트, 대형",3500.00
2,"머그컵",8000.00
3,"볼펜",1500.00
Verbinden Sie sich als course_user mit dem Übungsserver, wählen Sie shopcourse und prüfen Sie SELECT VERSION(), DATABASE();. Stellen Sie fest, ob csv45_stage und csv45_products noch nicht existieren. Prüfen Sie andernfalls Definitionen und Inhalte, bevor Sie über die Entfernung entbehrlicher Übungsobjekte entscheiden. Ersetzen Sie keine Anwendungstabellen.
Die Zwischentabelle, auch Staging-Tabelle genannt, erhält die ursprünglichen Texte für die Prüfung vor der Zahlenumwandlung. Legen Sie beide Tabellen an:
USE shopcourse;
CREATE TABLE csv45_stage(raw_id TEXT,raw_name TEXT,raw_price TEXT)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE csv45_products(product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL CHECK(price>=0))
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Die Clientdatei laden und Warnungen sofort untersuchen
Starten Sie den Client im Betriebssystemterminal mit LOCAL-Unterstützung für den vertrauenswürdigen Übungsserver. Passen Sie den Port an und geben Sie das Passwort an der Abfrage ein:
mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --local-infile=1 --default-character-set=utf8mb4 shopcourse
Führen Sie im SQL-Client den folgenden Block aus. SHOW WARNINGS folgt unmittelbar auf den Ladevorgang, bevor eine andere Anweisung die zugehörigen Diagnoseinformationen ersetzen kann.
LOAD DATA LOCAL INFILE 'C:/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;
SELECT * FROM csv45_stage;
SELECT * FROM csv45_stage
WHERE raw_id IS NULL OR raw_id NOT REGEXP '^[0-9]+$'
OR raw_price IS NULL OR raw_price NOT REGEXP '^[0-9]+([.][0-9]{1,2})?$'
OR raw_name IS NULL OR raw_name='';
SELECT CAST(raw_id AS UNSIGNED) AS normalized_id,COUNT(*)
FROM csv45_stage GROUP BY CAST(raw_id AS UNSIGNED) HAVING COUNT(*)>1;
-- After format checks pass, verify numeric ranges and name length
SELECT * FROM csv45_stage
WHERE CAST(raw_id AS DECIMAL(65,0))<1
OR CAST(raw_id AS DECIMAL(65,0))>2147483647
OR CAST(raw_price AS DECIMAL(65,2))>99999999.99
OR CHAR_LENGTH(raw_name)>100;
Die Zwischentabelle soll drei Datensätze enthalten; die drei Prüfungsabfragen sollen leere Ergebnisse liefern. Verbietet die Serverrichtlinie local_infile den Vorgang, klären Sie dies mit der Administration. Der strikte SQL-Modus garantiert nicht, dass LOCAL jede fehlerhafte Eingabe zurückweist. Prüfen Sie Warnungen und Originaltexte.
Der Aufbau trennt Zeilen am Zeilenvorschub LF und entfernt einen abschließenden Wagenrücklauf CR aus dem Preisfeld. Das deckt auch den einfachen üblichen Windows-Fall CRLF ab. Eingebettete Zeilenumbrüche und komplexe Maskierungsregeln erfordern eine passende Vereinbarung mit dem erzeugenden Werkzeug. Prüfen Sie zuerst das Textformat; erst danach sind Zahlenumwandlung, normalisierte Dubletten und Grenzwerte zu beurteilen.
Läuft der Client unter Ubuntu, erstellen Sie im Terminal ein Arbeitsverzeichnis und speichern Sie darin den CSV-Inhalt als UTF-8:
mkdir -p "$HOME/mariadb-lab"
printf '%s\n' "$HOME/mariadb-lab/products.csv"
Verwenden Sie bei LOCAL den tatsächlichen absoluten Pfad aus der vorigen Ausgabe. Der folgende Beispielpfad setzt lediglich einen Ubuntu-Benutzernamen learner voraus.
LOAD DATA LOCAL INFILE '/home/learner/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;
Führen Sie ausschließlich die zu Ihrem Client passende Windows- oder Ubuntu-Ladevariante aus. Beide nacheinander würden Zeilen in der Zwischentabelle verdoppeln. Der Exportpfad eines Ubuntu-Servers wird unabhängig davon geprüft. Die offizielle LOAD-DATA-INFILE-Referenz erläutert LOCAL und die Dateiverarbeitung.
Nur geprüfte Werte in die typisierten Spalten übertragen
Verlangen Sie leere Ergebnisse für Formatfehler, normalisierte Dubletten und Grenzwertverletzungen. Kennungen müssen positiv sein und in einen vorzeichenbehafteten INT passen, Preise in DECIMAL(10,2) und Namen in 100 Zeichen. Die Texte 01 und 1 ergeben dieselbe numerische Kennung. Prüfen Sie deshalb Eindeutigkeit nach der Normalisierung.
Schlägt eine Prüfung fehl, korrigieren Sie die Quelldatei oder legen Sie zuerst eine ausdrückliche Verarbeitungspolitik fest. Erstellte Tabellen allein sind kein Grund, ungeprüfte Werte mit INSERT zu übernehmen.
INSERT INTO csv45_products(product_id,product_name,price)
SELECT CAST(raw_id AS UNSIGNED),raw_name,CAST(raw_price AS DECIMAL(10,2))
FROM csv45_stage;
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id;
Erwartet werden drei Produkte. Der erste Name muss sein Komma behalten; die anderen Namen stehen für Becher und Kugelschreiber. Vergleichen Sie jedes Feld mit der Quelle. Wiederholtes Laden kann zusätzliche Staging-Zeilen anhängen. Bewahren Sie Quelldatei und Diagnose auf, bevor Sie einen fehlerhaften Versuch bereinigen.
Eine CSV-Datei mit berechtigtem Konto auf dem Server exportieren
Verwenden Sie ein freigegebenes Verwaltungs- oder Exportkonto mit FILE-Recht. Prüfen Sie secure_file_priv, die Schreibrechte des Serverprozesses und einen neuen Zieldateinamen. Auf einem entfernten Server bezeichnet C: dessen Dateisystem und nicht den Clientcomputer.
SELECT @@secure_file_priv;
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE 'C:/mariadb-lab/products-export.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Die Ausgabe enthält drei CSV-Datensätze ohne Kopfzeile. Eine vorhandene Datei wird nicht überschrieben. Fügen Sie eine benötigte Kopfzeile durch ein festgelegtes Exportverfahren hinzu. Eine tabulatorgetrennte Ausgabe von mariadb --batch wird durch die Dateiendung .csv nicht zu einer kommagetrennten Datei.
Prüfen Sie auf einem Ubuntu-Server ebenfalls secure_file_priv und die Schreibrechte des Dienstkontos. Ist /var/lib/mysql-files/ das freigegebene Verzeichnis, kann ein neuer Dateiname wie im folgenden Beispiel verwendet werden. Dies ist kein persönliches Verzeichnis des Clients.
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE '/var/lib/mysql-files/products-export-new.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Ersetzen Sie das Verzeichnis durch den tatsächlich freigegebenen Pfad und führen Sie auf dem betreffenden Server eine Exportvariante aus. Löschen Sie keine bestehende Datei und erweitern Sie FILE nicht bloß zur Umgehung einer Ablehnung. Die offizielle SELECT-INTO-OUTFILE-Referenz beschreibt Voraussetzungen und Ausgabeformat.
Einzelne Felder nach dem Export prüfen und die Übung bereinigen
Prüfen Sie neben der Datensatzanzahl Kommas, Unicode-Zeichen, leere Texte, NULL und Zeilenumbrüche. CSV-Werkzeuge verwenden nicht überall dieselbe NULL-Konvention. Legen Sie fest, ob ein leeres Feld einen leeren Text oder einen fehlenden Wert bezeichnet. Dieses Beispiel verwendet bewusst drei befüllte Felder pro Datensatz.
Entscheiden Sie gesondert über die Aufbewahrung der Quell- und Exportdateien. Entfernen Sie die Tabellen nur als entbehrliche Objekte dieser Übung. DROP TABLE löscht Definitionen und Daten und ist nicht durch ein gewöhnliches ROLLBACK rückgängig zu machen. Das Entfernen der Tabellen löscht nicht die CSV-Dateien.
DROP TABLE shopcourse.csv45_products;
DROP TABLE shopcourse.csv45_stage;




