Duplicate Data finden und bereinigen in MySQL

Doppelte Datensätze entstehen in jeder produktiven Datenbank — durch Importfehler, Race Conditions bei gleichzeitigen Schreibzugriffen, fehlende UNIQUE Constraints oder migrierte Altdaten. In Magento können Kundenduplikate zu falschen Bestellzuordnungen führen, doppelte Produkteinträge die Suche verwirren und redundante Preisdaten die Kalkulation brechen. Dieser Artikel zeigt, wie man SQL Duplikate entfernen kann und anschließend dauerhaft verhindert — von der einfachen GROUP-BY-Analyse bis zum sicheren Massen-Delete auf Tabellen mit Millionen von Zeilen.

Duplikate finden mit GROUP BY und HAVING

Der erste Schritt ist immer das Sichtbarmachen der Duplikate. Die klassische Methode verwendet GROUP BY auf den Spalten, die eindeutig sein sollten, und filtert Gruppen mit mehr als einem Eintrag per HAVING COUNT(*) > 1. Dieser Query zeigt das Problem, aber noch nicht die konkreten Zeilen-IDs.

-- Einfache Duplikatsuche über eine Spalte
SELECT
    email,
    COUNT(*) AS anzahl
FROM customer_entity
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY anzahl DESC;

-- Duplikate über mehrere Spalten finden
SELECT
    firstname,
    lastname,
    email,
    COUNT(*) AS anzahl
FROM customer_entity
GROUP BY firstname, lastname, email
HAVING COUNT(*) > 1
ORDER BY anzahl DESC;

-- Gesamtanzahl zu löschender Duplikate ermitteln
SELECT SUM(anzahl - 1) AS zu_loeschende_duplikate
FROM (
    SELECT COUNT(*) AS anzahl
    FROM customer_entity
    GROUP BY email
    HAVING COUNT(*) > 1
) AS dup;
-- (anzahl - 1) weil immer einer der Duplikate erhalten bleibt

ROW_NUMBER() zur präzisen Duplikat-Identifikation

Die Window-Funktion ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) nummeriert alle Zeilen innerhalb jeder Gruppe. Die Zeile mit rn = 1 ist der zu behaltende Original-Datensatz, alle mit rn > 1 sind Duplikate. Die Sortierung in ORDER BY entscheidet, welche Zeile als erste gilt — typischerweise die älteste (kleinste ID) oder die neueste (größte ID).

-- Alle Zeilen nummerieren: rn=1 = behalten, rn>1 = löschen
SELECT
    entity_id,
    email,
    created_at,
    ROW_NUMBER() OVER (
        PARTITION BY email        -- Gruppe: gleiche E-Mail = Duplikatgruppe
        ORDER BY entity_id ASC    -- ASC = ältester (kleinste ID) bleibt erhalten
    ) AS rn
FROM customer_entity
ORDER BY email, rn;

-- Nur die zu löschenden IDs anzeigen (Probelauf vor dem DELETE)
SELECT entity_id
FROM (
    SELECT
        entity_id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY entity_id ASC
        ) AS rn
    FROM customer_entity
) AS ranked
WHERE rn > 1;

-- Variante: Neuesten Datensatz behalten (ORDER BY entity_id DESC)
SELECT entity_id
FROM (
    SELECT
        entity_id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY entity_id DESC  -- neueste ID bleibt erhalten
        ) AS rn
    FROM customer_entity
) AS ranked
WHERE rn > 1;

Bevor man löscht, sollte man immer den SELECT ausführen und die zu löschenden IDs manuell prüfen. Bei Magento-Tabellen ist besondere Vorsicht geboten — ein DELETE auf customer_entity kann Foreign-Key-Verletzungen in abhängigen Tabellen auslösen.

Das CTE-Delete-Pattern: Sicher und lesbar löschen

Das eleganteste Muster für das SQL Duplikate entfernen kombiniert einen CTE mit einem DELETE. Der CTE identifiziert die Duplikat-IDs, der DELETE entfernt sie. MySQL erlaubt kein direktes DELETE FROM cte, daher wird der CTE als Subselect oder über einen JOIN angebunden.

-- Methode 1: CTE mit Subselect (MySQL 8.0+, empfohlen)
WITH duplicates AS (
    SELECT entity_id
    FROM (
        SELECT
            entity_id,
            ROW_NUMBER() OVER (
                PARTITION BY email
                ORDER BY entity_id ASC  -- kleinste ID behalten
            ) AS rn
        FROM customer_entity
    ) AS ranked
    WHERE rn > 1
)
DELETE FROM customer_entity
WHERE entity_id IN (SELECT entity_id FROM duplicates);

-- Methode 2: Self-Join DELETE (einfacher, auch für ältere MySQL-Versionen)
DELETE c1
FROM customer_entity c1
INNER JOIN customer_entity c2
    ON c1.email = c2.email           -- gleiche E-Mail
    AND c1.entity_id > c2.entity_id; -- c1 hat höhere ID → c1 ist Duplikat
-- Erklärung: c2 ist der ältere (kleinere ID), c1 das neuere Duplikat
-- Das Ergebnis: für jede E-Mail bleibt die älteste ID erhalten

-- WICHTIG: Immer zuerst als SELECT testen!
-- SELECT c1.entity_id, c1.email
-- FROM customer_entity c1
-- INNER JOIN customer_entity c2
--     ON c1.email = c2.email
--     AND c1.entity_id > c2.entity_id;

E-Mail-Validierung vor der Bereinigung

Manchmal entstehen vermeintliche Duplikate durch Schreibfehler, unterschiedliche Groß-/Kleinschreibung oder führende/nachgestellte Leerzeichen. Vor der Bereinigung lohnt sich ein genereller Datenqualitätscheck.

-- Ungültige E-Mail-Formate finden
SELECT entity_id, email
FROM customer_entity
WHERE email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$';

-- Duplikate durch Groß-/Kleinschreibung (Collation-Problem)
SELECT
    LOWER(email)                   AS email_lower,
    COUNT(*)                       AS cnt,
    GROUP_CONCAT(entity_id ORDER BY entity_id SEPARATOR ', ') AS entity_ids
FROM customer_entity
GROUP BY LOWER(email)
HAVING cnt > 1;

-- Führende oder nachgestellte Leerzeichen (häufiger Importfehler)
SELECT entity_id, email, LENGTH(email) AS len, LENGTH(TRIM(email)) AS trimmed_len
FROM customer_entity
WHERE email != TRIM(email);

-- Leerzeichen-Fehler vor der Bereinigung korrigieren
UPDATE customer_entity
SET email = TRIM(email)
WHERE email != TRIM(email);

Magento-Kunden: Duplikate sicher bereinigen

In Magento hat die Tabelle customer_entity Foreign-Key-Abhängigkeiten zu customer_address_entity, customer_entity_varchar, customer_entity_int, sales_order und weiteren Tabellen. Ein direktes DELETE ist riskant. Der korrekte Ansatz ist: Bestellungen und Daten ummappen, dann erst löschen.

-- Schritt 1: Duplikate mit Bestellanzahl auflisten
-- (Kunden mit mehr Bestellungen sollten erhalten bleiben)
SELECT
    ce.entity_id,
    ce.email,
    ce.created_at,
    COUNT(so.entity_id)            AS bestellungen,
    ROW_NUMBER() OVER (
        PARTITION BY ce.email
        ORDER BY COUNT(so.entity_id) DESC, ce.entity_id ASC
    ) AS rn  -- rn=1: dieser Kunde bleibt erhalten
FROM customer_entity ce
LEFT JOIN sales_order so ON so.customer_id = ce.entity_id
GROUP BY ce.entity_id, ce.email, ce.created_at
ORDER BY ce.email, rn;

-- Schritt 2: Bestellungen des Duplikats auf den Original-Kunden umschreiben
-- (entity_id_orig = ID des zu behaltenden, entity_id_dup = ID des zu löschenden)
UPDATE sales_order
SET customer_id = :entity_id_orig
WHERE customer_id = :entity_id_dup;

-- Schritt 3: EAV-Attribute des Duplikats löschen
DELETE FROM customer_entity_varchar  WHERE entity_id = :entity_id_dup;
DELETE FROM customer_entity_int      WHERE entity_id = :entity_id_dup;
DELETE FROM customer_entity_text     WHERE entity_id = :entity_id_dup;
DELETE FROM customer_entity_decimal  WHERE entity_id = :entity_id_dup;
DELETE FROM customer_entity_datetime WHERE entity_id = :entity_id_dup;
DELETE FROM customer_address_entity  WHERE parent_id  = :entity_id_dup;

-- Schritt 4: Duplikat-Kundeneintrag löschen
DELETE FROM customer_entity WHERE entity_id = :entity_id_dup;

-- Schritt 5: Magento-Caches leeren nach Massenbereinigung
-- bin/magento cache:flush customer

Chunked Deletes für große Tabellen

Bei Tabellen mit Millionen von Zeilen führt ein einziges großes DELETE zu langen Row-Locks und kann andere Datenbankoperationen für Sekunden oder Minuten blockieren. Die Lösung ist ein chunked Delete, das in kleinen Batches von 500–2000 Zeilen arbeitet.

-- Duplikat-IDs einmalig in Temp-Tabelle sammeln
CREATE TEMPORARY TABLE dup_ids AS
SELECT entity_id
FROM (
    SELECT
        entity_id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY entity_id ASC
        ) AS rn
    FROM customer_entity
) AS ranked
WHERE rn > 1;

-- Index auf die Temp-Tabelle für schnelle Joins
ALTER TABLE dup_ids ADD INDEX idx_entity_id (entity_id);

-- Anzahl Duplikate kontrollieren
SELECT COUNT(*) AS total_duplikate FROM dup_ids;

-- Batch-Delete in 1000er-Portionen
-- In einer Schleife ausführen bis COUNT(*) FROM dup_ids = 0:

DELETE c
FROM customer_entity c
INNER JOIN dup_ids d ON c.entity_id = d.entity_id
LIMIT 1000;

-- Verarbeitete IDs aus Temp-Tabelle entfernen
DELETE d
FROM dup_ids d
LEFT JOIN customer_entity c ON c.entity_id = d.entity_id
WHERE c.entity_id IS NULL;

-- Shell-Loop für automatisierte Bereinigung:
-- while [ $(mysql magento -sNe "SELECT COUNT(*) FROM dup_ids") -gt 0 ]; do
--   mysql magento -e "DELETE c FROM customer_entity c
--                     INNER JOIN dup_ids d ON c.entity_id = d.entity_id LIMIT 1000"
--   mysql magento -e "DELETE d FROM dup_ids d
--                     LEFT JOIN customer_entity c ON c.entity_id = d.entity_id
--                     WHERE c.entity_id IS NULL"
--   sleep 0.5
-- done

UNIQUE Constraint nach der Bereinigung

Der letzte und wichtigste Schritt nach jedem Dedup ist das Absichern der Eindeutigkeit auf Datenbankebene. Ein UNIQUE INDEX verhindert dauerhaft, dass neue Duplikate entstehen — unabhängig davon, ob der Anwendungscode korrekt ist.

-- Kontrollabfrage: Sind noch Duplikate vorhanden?
SELECT email, COUNT(*) AS cnt
FROM customer_entity
GROUP BY email
HAVING cnt > 1;
-- Muss leer sein, sonst schlägt das ALTER TABLE fehl!

-- UNIQUE Constraint hinzufügen (kleine Tabellen)
ALTER TABLE customer_entity
    ADD UNIQUE INDEX uidx_customer_email (email);

-- Für große Tabellen ohne Downtime (Percona pt-online-schema-change):
-- pt-online-schema-change \
--   --alter "ADD UNIQUE INDEX uidx_customer_email (email)" \
--   --execute D=magento,t=customer_entity

-- Composite UNIQUE über mehrere Spalten
ALTER TABLE catalog_product_entity
    ADD UNIQUE INDEX uidx_product_sku (sku);

-- Vorhandene Indexe prüfen
SHOW INDEX FROM customer_entity WHERE Non_unique = 0;

INSERT ... ON DUPLICATE KEY: Duplikate bei Importen verhindern

Statt Duplikate nachträglich zu bereinigen, kann man sie bei Importen und Upserts von Anfang an verhindern. INSERT ... ON DUPLICATE KEY UPDATE aktualisiert den vorhandenen Datensatz bei einem UNIQUE-Konflikt, statt einen Fehler zu werfen oder einen neuen Datensatz einzufügen.

-- UPSERT: einfügen oder aktualisieren wenn UNIQUE-Konflikt
INSERT INTO product_price (product_id, store_id, price)
VALUES (42, 1, 19.99)
ON DUPLICATE KEY UPDATE
    price      = VALUES(price),
    updated_at = NOW();

-- MySQL 8.0.20+ Syntax (VALUES() ist deprecated):
INSERT INTO product_price (product_id, store_id, price)
VALUES (42, 1, 19.99) AS new_vals
ON DUPLICATE KEY UPDATE
    price      = new_vals.price,
    updated_at = NOW();

-- INSERT IGNORE: bei UNIQUE-Konflikt stillschweigend überspringen
INSERT IGNORE INTO catalog_url_rewrite (entity_type, entity_id, request_path, store_id)
VALUES ('product', 42, 'mein-produkt.html', 1);

-- WICHTIG: REPLACE INTO NICHT in Magento verwenden!
-- REPLACE löscht den alten Datensatz und fügt einen neuen ein
-- → ändert die Auto-Increment-ID → bricht Foreign Keys!

Häufige Fragen zu SQL Duplikate entfernen