Es gibt unterschiedliche Gründe, warum es passieren kann, dass in MM-Tabellen doppelte Datensätze enthalten sind. Oft stammen die Duplikate aus unglücklichen Import- oder Migrationsskripten, oft sind sie entstanden, weil die Tabellenstruktur zu spät strikt genug definiert wurde.
Das Kind ist nun aber schon in den Brunnen gefallen, und wir wollen es retten.
Mit ein wenig Schiebereien klappt das im MySQL recht fix.

Szenario

Nehmen wir an, wir hätten eine MM-Tabelle folgender Form:

#
# Table structure for table 'tx_example_category_mm'
#
CREATE TABLE tx_example_category_mm
(
  uid_local   int(11) DEFAULT '0' NOT NULL,
  uid_foreign int(11) DEFAULT '0' NOT NULL,
  sorting     int(11) DEFAULT '0' NOT NULL,

  KEY         uid_local (uid_local),
  KEY         uid_foreign (uid_foreign)
);

Jedes Pärchen aus uid_local und uid_foreign sollte eindeutig sein udn nur einmal vorkommen. Dies hätte erzwungen werden können durch einen UNIQUE KEY – wurde aber wohl vergessen.

Im Nachhinein jetzt diesen Index hinzuzufügen, verwehrt MySQL, da es Duplikate gibt, die UNIQUE-Bedingung also verletzt.

ALTER TABLE tx_example_category_mm ADD UNIQUE KEY uidx(uid_local, uid_foreign);

=> „Error in query (1062): Duplicate entry ’12-13′ for key ‚uidx‘„

Lösung

Vor MySQL 5.7 war eine einfache Lösung¹, den Index zu setzen, und MySQL dabei die Duplikate verwerfen zu lassen. IGNORE im ALTER TABLE-Befehl wurde jedoch in MySQL v5.6 „deprecated“ und in v5.7 entfernt².

# Not working anymore since MySQL 5.7 
ALTER IGNORE TABLE tx_example_category_mm ADD UNIQUE KEY uidx(uid_local, uid_foreign);

Morgan hatte damals im Blogbeitrag „The future of ALTER IGNORE TABLE syntax“³ Überlegungen angestellt, wie man dennoch mit der Problematik umgehen kann, und dabei die Probleme des IGNORE (z.B. bei Replikationen der Tabelle) mit in den Griff bekommt.

Ab MySQL 5.7 benötigt man den Zwischenschritt über eine Kopie der Tabelle:

# Working with MySQL 5.7 and newer
CREATE TABLE tx_example_category_mm_new LIKE tx_example_category_mm;
ALTER TABLE tx_example_category_mm_new ADD UNIQUE KEY uidx(uid_local, uid_foreign);
INSERT IGNORE INTO tx_example_category_mm_new SELECT * FROM tx_example_category_mm;
DROP TABLE tx_example_category_mm;
RENAME TABLE tx_example_category_mm_new TO tx_example_category_mm;

Es wird also zunächst die Tabellen-Struktur als Basis für eine temporäre Tabelle genutzt. In der neuen Tabelle wird dann der bislang fehlende UNIQUE-Key gesetzt. Anschließend werden alle Datensätze aus der ursprünglichen Tabelle in die neue Tabelle kopiert, wobei Fehler aufgrund von doppelten Keys via IGNORE im INSERT-Befehl abgefangen werden. Als letztes wird die Quell-Tabelle durch die neu erstellte ersetzt (bzw. formal korrekt: die Quell-Tabelle wird gelöscht und die temporär-Tabelle erhält den Namen der Quell-Tabelle).

Herzlichen Dank an die +Pluswerk München AG (damals noch Marit AG) und Morgan Tocker für das Teilen ihrer Überlegungen.

Backup nicht vergessen!
Vor derartigen Änderungen an der Datenbank bitte sicherstellen, dass ein Backup vorhanden ist. Aber das ist ja klar ;)

Quellen

1. Delete duplicate records from MM tables
2. WL#7395: Deprecate (5.6) and remove (5.7) IGNORE for ALTER TABLE
3. The future of ALTER IGNORE TABLE syntax