Daten aus CSV, XML und APIs in MySQL einspielen und validieren

LOAD DATA INFILE: Grundlagen und Berechtigungen

LOAD DATA INFILE ist MySQL's eingebauter Hochgeschwindigkeits-Import-Mechanismus für tabellarische Textdateien (CSV, TSV etc.). Er ist typischerweise 10–20x schneller als entsprechende INSERT-Statements, weil MySQL die Daten direkt einliest, ohne den SQL-Parser für jede Zeile zu durchlaufen. Für Massendatenimporte — beispielsweise Produktkataloge mit 50.000 Zeilen aus einem ERP-System — ist LOAD DATA INFILE die effizienteste Methode.

Die Berechtigung FILE muss dem MySQL-Nutzer explizit gewährt werden, wenn LOAD DATA INFILE (ohne LOCAL) ausgeführt wird. Das FILE-Privileg erlaubt dem MySQL-Server, Dateien auf dem Server-Dateisystem zu lesen. Die Alternative LOAD DATA LOCAL INFILE liest die Datei vom Client-System — dafür ist das FILE-Privileg nicht nötig, aber der MySQL-Server muss mit local_infile=ON konfiguriert sein.

-- Grant FILE privilege for server-side LOAD DATA INFILE
GRANT FILE ON *.* TO 'import_user'@'localhost';

-- Enable LOCAL INFILE on server (my.cnf or at runtime)
-- [mysqld]
-- local_infile = ON

SET GLOBAL local_infile = ON;

-- Check current local_infile setting
SHOW VARIABLES LIKE 'local_infile';
SHOW VARIABLES LIKE 'secure_file_priv';  -- must be empty or path to CSV

Wichtige Optionen: FIELDS, LINES, IGNORE

Die Stärke von LOAD DATA INFILE liegt in seinen flexiblen Parsing-Optionen. Mit FIELDS TERMINATED BY, FIELDS ENCLOSED BY und LINES TERMINATED BY kann praktisch jedes CSV-Format konfiguriert werden. Die Option IGNORE n LINES überspringt die ersten n Zeilen — wichtig für CSV-Dateien mit Headerzeile. Die Reihenfolge der Spalten in der Datei kann über einen Spaltenlisten-Ausdruck auf die Tabellenspalten gemappt werden.

-- Full LOAD DATA INFILE example for a product catalog CSV
-- CSV format: sku,name,price,qty,category_id,description
-- First line is header (IGNORE 1 LINES)

LOAD DATA INFILE '/var/lib/mysql-files/products_import.csv'
INTO TABLE staging_product_import
CHARACTER SET utf8mb4
FIELDS
  TERMINATED BY ','          -- column separator
  ENCLOSED BY '"'            -- text qualifier
  ESCAPED BY '\\'            -- escape character for special chars
LINES
  TERMINATED BY '\n'         -- line ending (use \r\n for Windows CSV)
IGNORE 1 LINES               -- skip header row
(                            -- column mapping
  sku,
  product_name,              -- maps to column 'product_name' in staging table
  @price_raw,                -- load raw into variable for transformation
  @qty_raw,
  category_id,
  @description_raw
)
SET
  price = CAST(REPLACE(@price_raw, ',', '.') AS DECIMAL(12,4)),
  qty = CAST(@qty_raw AS UNSIGNED),
  product_description = NULLIF(TRIM(@description_raw), ''),
  imported_at = NOW();

-- Check how many rows were loaded
SELECT ROW_COUNT() AS rows_loaded;

Die SET-Klausel nach der Spaltenliste ist ein mächtiges Feature: Sie erlaubt Transformationen während des Ladens. Im Beispiel werden Preise mit Komma als Dezimaltrennzeichen in das korrekte Dezimalformat konvertiert, leere Strings in NULL umgewandelt, und ein Import-Timestamp gesetzt — alles in einem einzigen LOAD DATA INFILE-Statement. Das spart einen separaten UPDATE-Schritt.

LOCAL vs. Server-seitig: Sicherheit und Performance

LOAD DATA INFILE ohne LOCAL liest die Datei direkt auf dem MySQL-Server vom Dateisystem. Das ist schneller, weil keine Netzwerkübertragung nötig ist, aber erfordert das FILE-Privileg und setzt voraus, dass die Datei im secure_file_priv-Verzeichnis liegt. LOAD DATA LOCAL INFILE liest die Datei vom Client und überträgt sie über die MySQL-Verbindung — geringfügig langsamer, aber sicherer in Umgebungen mit mehreren Datenbanknutzern.

-- Server-side LOAD DATA (faster, requires FILE privilege and secure_file_priv path)
LOAD DATA INFILE '/var/lib/mysql-files/catalog.csv'
INTO TABLE staging_catalog
FIELDS TERMINATED BY ';' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

-- Client-side LOAD DATA LOCAL (no FILE privilege needed)
LOAD DATA LOCAL INFILE '/home/user/downloads/catalog.csv'
INTO TABLE staging_catalog
FIELDS TERMINATED BY ';' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

-- For Docker/container environments: copy file to MySQL container first
-- docker cp catalog.csv mysql_container:/var/lib/mysql-files/
-- Then use server-side LOAD DATA INFILE

-- Check secure_file_priv path (files must be in this directory)
SHOW VARIABLES LIKE 'secure_file_priv';

Staging-Table-Ansatz: Laden, validieren, transformieren

Der professionelle Ansatz für Massendaten-Imports ist der Staging-Table-Workflow: Erst werden die Rohdaten in eine Staging-Tabelle geladen (mit LOAD DATA INFILE), dann werden die Daten validiert und transformiert, und schließlich werden nur gültige Datensätze in die Produktionstabellen übernommen. Dieser dreistufige Prozess ist deutlich robuster als direkt in Produktionstabellen zu importieren, weil Fehler frühzeitig isoliert werden.

Die Staging-Tabelle hat eine einfache Struktur mit VARCHAR-Spalten für alle Felder (damit keine Typ-Validierungsfehler beim Laden entstehen), plus Metaspalten für Import-Status, Fehler-Meldungen und Timestamps. Nach dem Laden analysiert man die Staging-Tabelle mit Validierungsabfragen und markiert fehlerhafte Zeilen, bevor man den eigentlichen Import durchführt.

-- Step 1: Create flexible staging table (all VARCHAR, no constraints)
CREATE TEMPORARY TABLE staging_product_import (
  staging_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  raw_sku     VARCHAR(255),
  raw_name    VARCHAR(500),
  raw_price   VARCHAR(50),
  raw_qty     VARCHAR(50),
  raw_cat_id  VARCHAR(50),
  raw_desc    TEXT,
  import_status ENUM('pending', 'valid', 'error') DEFAULT 'pending',
  error_msg   TEXT,
  imported_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Step 2: Load raw CSV data into staging
LOAD DATA LOCAL INFILE '/tmp/products.csv'
INTO TABLE staging_product_import
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES
(raw_sku, raw_name, raw_price, raw_qty, raw_cat_id, raw_desc);

-- Step 3: Validate and mark invalid rows
UPDATE staging_product_import
SET import_status = 'error', error_msg = 'Invalid price format'
WHERE raw_price NOT REGEXP '^[0-9]+([.,][0-9]{1,4})?$';

UPDATE staging_product_import
SET import_status = 'error', error_msg = 'SKU too long or empty'
WHERE raw_sku IS NULL OR raw_sku = '' OR LENGTH(raw_sku) > 64;

-- Mark remaining rows as valid
UPDATE staging_product_import
SET import_status = 'valid'
WHERE import_status = 'pending';

-- Step 4: Insert valid rows into production table
INSERT INTO catalog_product_staging
  (sku, name, price, qty)
SELECT
  TRIM(raw_sku),
  TRIM(raw_name),
  CAST(REPLACE(raw_price, ',', '.') AS DECIMAL(12,4)),
  CAST(raw_qty AS UNSIGNED)
FROM staging_product_import
WHERE import_status = 'valid';

LOAD XML INFILE für strukturierte Daten

MySQL unterstützt neben CSV auch den direkten Import von XML-Dateien mit LOAD XML INFILE. Das XML muss eine flache Struktur haben, bei der jedes Element einer Tabellenzeile entspricht. Für verschachtelte XML-Strukturen (wie komplexe Produkt-XMLs mit Attributen als Child-Elementen) ist vorgeschaltete XSLT-Transformation oder serverseitiges Parsing notwendig.

-- LOAD XML INFILE: for flat XML structures
-- XML format expected:
-- <catalog>
--   <product><sku>ABC-001</sku><name>Test Product</name><price>29.99</price></product>
--   <product>...</product>
-- </catalog>

LOAD XML LOCAL INFILE '/tmp/products.xml'
INTO TABLE staging_product_import
ROWS IDENTIFIED BY '<product>';   -- each <product> element = one row

-- Alternative: field-level mapping for non-matching element/column names
LOAD XML LOCAL INFILE '/tmp/products.xml'
INTO TABLE staging_product_import
ROWS IDENTIFIED BY '<product>'
(raw_sku, raw_name, raw_price);   -- explicit column mapping

-- Check loaded rows
SELECT COUNT(*) AS loaded_rows FROM staging_product_import;
SELECT * FROM staging_product_import LIMIT 5;

Validierungsabfragen für fehlerhafte Staging-Daten

Nachdem Daten in die Staging-Tabelle geladen wurden, ist die Validierungsphase entscheidend für die Datenqualität des nachgelagerten Imports. Ein guter Validierungsprozess prüft mehrere Dimensionen: Format-Validierung (ist der Preis eine gültige Zahl?), Vollständigkeit (fehlende Pflichtfelder), Referenzintegrität (existiert die Category ID in der Zieltabelle?) und Business-Regeln (ist der Preis im plausiblen Bereich?).

-- Comprehensive validation queries for import staging table

-- 1. Format validation: invalid prices
SELECT staging_id, raw_sku, raw_price, 'invalid price format' AS issue
FROM staging_product_import
WHERE raw_price NOT REGEXP '^[0-9]+([.][0-9]{1,4})?$'
  AND raw_price IS NOT NULL AND raw_price != '';

-- 2. Referential integrity: category IDs that don't exist in Magento
SELECT DISTINCT s.raw_cat_id, 'category not found in Magento' AS issue
FROM staging_product_import s
LEFT JOIN catalog_category_entity c ON s.raw_cat_id = c.entity_id
WHERE c.entity_id IS NULL
  AND s.raw_cat_id IS NOT NULL;

-- 3. Duplicate SKUs within the import file
SELECT raw_sku, COUNT(*) AS occurrences
FROM staging_product_import
GROUP BY raw_sku
HAVING COUNT(*) > 1;

-- 4. SKUs that already exist in Magento (would be updates, not inserts)
SELECT s.raw_sku, cpe.entity_id AS existing_entity_id
FROM staging_product_import s
INNER JOIN catalog_product_entity cpe ON TRIM(s.raw_sku) = cpe.sku;

-- 5. Business rule: prices out of plausible range
SELECT staging_id, raw_sku, raw_price
FROM staging_product_import
WHERE CAST(REPLACE(raw_price, ',', '.') AS DECIMAL(12,4)) > 100000
   OR CAST(REPLACE(raw_price, ',', '.') AS DECIMAL(12,4)) < 0;

Magento ImportExport vs. direktes SQL

Magento bietet ein eigenes ImportExport-Framework, das über bin/magento import:run oder die Admin-Oberfläche Produkte, Kunden und andere Entitäten importiert. Wann sollte man das Magento-Framework verwenden, und wann direktes SQL mit LOAD DATA INFILE?

Das Magento ImportExport-Framework hat klare Vorteile: Es übernimmt alle komplexen Logiken wie EAV-Attribut-Verarbeitung, URL-Rewrites-Generierung, Index-Trigger und Media-Import. Es ist die sichere Wahl für Standard-Importe, weil es alle Magento-internen Regeln berücksichtigt. Direktes SQL mit LOAD DATA INFILE ist dagegen sinnvoll für nicht-Entitätsdaten (Lager, Preise aus externen Systemen), für sehr große Datenmengen wo Magento-Framework zu langsam ist, und für technische Tabellen (Konfigurationsdaten, Custom-Module-Tabellen).

-- When to use Magento ImportExport (framework):
-- Products with many attributes, customer data, category assignments
-- bin/magento import:run --entity=catalog_product --behavior=append --validation-strategy=validation-stop-on-errors

-- When to use direct SQL LOAD DATA INFILE:
-- 1. Bulk inventory updates (cataloginventory_stock_item)
LOAD DATA LOCAL INFILE '/tmp/inventory_update.csv'
INTO TABLE cataloginventory_stock_item_tmp
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(product_id, stock_id, qty, is_in_stock);

-- 2. Price updates from external system (catalog_product_entity_decimal)
-- Load into staging first, then update production
INSERT INTO catalog_product_entity_decimal (attribute_id, store_id, entity_id, value)
SELECT 75, 0, cpe.entity_id, s.new_price  -- attribute_id 75 = price
FROM staging_price_update s
INNER JOIN catalog_product_entity cpe ON s.sku = cpe.sku
ON DUPLICATE KEY UPDATE value = VALUES(value);

-- After direct SQL price update: invalidate price index
-- bin/magento indexer:reindex catalog_product_price

Fehlerbehandlung bei Duplikaten und Constraints

Beim Import in Produktionstabellen können Primärschlüssel-Verletzungen, Unique-Constraint-Konflikte und Foreign-Key-Verletzungen auftreten. MySQL bietet mehrere Strategien zur Behandlung dieser Fälle. INSERT IGNORE überspringt Zeilen, die einen Constraint verletzen, ohne den gesamten Import zu stoppen. INSERT INTO ... ON DUPLICATE KEY UPDATE aktualisiert bestehende Zeilen bei Konflikt statt sie zu überspringen. REPLACE INTO löscht die bestehende Zeile und fügt die neue ein — Achtung: erzeugt neue entity_id.

-- Handling duplicates during import

-- Option 1: INSERT IGNORE (skip conflicting rows silently)
INSERT IGNORE INTO catalog_product_entity (sku, type_id, attribute_set_id)
SELECT raw_sku, 'simple', 4
FROM staging_product_import
WHERE import_status = 'valid';

-- Option 2: ON DUPLICATE KEY UPDATE (upsert)
INSERT INTO catalog_product_entity (sku, type_id, attribute_set_id, updated_at)
SELECT raw_sku, 'simple', 4, NOW()
FROM staging_product_import
WHERE import_status = 'valid'
ON DUPLICATE KEY UPDATE
  updated_at = NOW(),
  type_id = VALUES(type_id);

-- Option 3: Check for duplicates before import
SELECT COUNT(*) AS would_conflict
FROM staging_product_import s
INNER JOIN catalog_product_entity cpe ON TRIM(s.raw_sku) = cpe.sku
WHERE s.import_status = 'valid';

-- Error handling: capture failed rows with handler
START TRANSACTION;
INSERT INTO catalog_product_entity (sku, type_id, attribute_set_id)
SELECT raw_sku, 'simple', 4
FROM staging_product_import WHERE import_status = 'valid';
-- Check row count vs expected
SELECT ROW_COUNT() AS inserted;
COMMIT;

FAQ: LOAD DATA INFILE und MySQL-Import