Mehrfache Sequenzen mit AUTO_INCREMENT zu erstellen, ist mithilfe der Datenbank-Engine MyISAM kein Problem. InnoDB gestattet dies nicht. Dennoch gibt es eine einfache Lösung für das Problem.
Problemstellung
Um die Problemstellung klar verständlich zu machen, wähle ich ein einfaches Beispiel. Nehmen Sie an, Sie wollten für eine von Ihnen erstellte Software ein Online-Portal erstellen, in welchem Anwender festgestellte Bugs melden können. Sie haben Kategorien gebildet, denen die gemeldeten Bugs zugeordnet werden sollen. Innerhalb einer Kategorie sollen die Meldungen durchnummeriert werden.
|
1 2 3 4 5 6 7 8 |
+-------------+-------+---------------------+ | kategorie | lfdnr | beschreibung | +-------------+-------+---------------------+ | Grafikkarte | 1 | Karte nicht erkannt | | Grafikkarte | 2 | Streifenbildung | | Sound | 1 | kein Sound | +-------------+-------+---------------------+ 3 rows in set (0,00 sec) |
Zugegeben, es sind nur drei Datensätze. Das Prinzip wird aber deutlich. In der Kategorie Grafikkarte sind im Beispiel zwei Fehler gemeldet worden, die mit einer laufenden Nummer versehen sind. In der Kategorie Sound wurde nur ein Fehler gemeldet.
Mehrfache Sequenzen mit AUTO_INCREMENT
Die Datenbank-Engine MyISAM, die in vergangenen Zeiten die Standard-Engine von MySQL war, bietet für die Aufgabenstellung eine einfache Lösung. Man kann das zu nummerierende Datenfeld einfach mit AUTO_INCREMENT vereinbaren. Dabei ist lediglich dessen Positionierung im Primärschlüssel zu beachten. Das zu nummerierende Feld muss nach den kategorisierenden Feldern stehen. Im Beispiel ist das nur ein Feld, es dürfen aber auch mehrere sein.
|
1 2 3 4 5 6 7 8 |
DROP TABLE IF EXISTS bugreports; CREATE TABLE bugreports ( lfdnr INT NOT NULL AUTO_INCREMENT, kategorie VARCHAR(20) NOT NULL, # referenziert eine Kategorietabelle beschreibung VARCHAR(100) NOT NULL, PRIMARY KEY (kategorie, lfdnr) ) ENGINE = MyISAM; |
Üblicherweise wird die Kategorie mittels eines Fremdschlüssels auf eine Master-Tabelle verweisen. Das habe ich mir hier geschenkt. Beachten Sie, die Felder in der Tabellendefinition beliebig anordnen zu können. Um mehrfache Sequenzen mit AUTO_INCREMENT zu erzeugen, ist einzig die Feldreihenfolge in der Primärschlüsseldefinition entscheidend.
Fügen wir die oben gezeigten Datensätze in die Tabelle ein.
|
1 2 3 4 5 |
INSERT INTO bugreports (kategorie, beschreibung) VALUES ('Grafikkarte', 'Karte nicht erkannt') , ('Sound', 'kein Sound') , ('Grafikkarte', 'Streifenbildung'); |
Dann erhalten wir mit folgender Abfrage das eingangs dargestellte Ergebnis.
|
1 2 3 4 5 6 7 8 |
SELECT kategorie , lfdnr , beschreibung FROM bugreports ORDER BY kategorie, lfdnr; |
Nachteilig an der Wahl von MyISAM als Datenbank-Engine ist jedoch, die Vorteile von InnoDB nicht nutzen zu können. Ganz trivial können Sie keine Fremdschlüsselbeziehung zu einer Kategorietabelle definieren. Umgekehrt präsentiert Ihnen MySQL eine Fehlermeldung, wenn Sie InnoDB als Datenbank-Engine für die Tabelle nutzen wollen. Dabei geben Sie statt MyISAM InnoDB an oder lassen die Klausel weg. Bei aktuellen Standard-Installationen von MySQL ist InnoDB die Standard-Datenbank-Engine.
Dumm gelaufen könnte man nun denken. Es gibt jedoch eine einfache Lösung mithilfe von Triggern, die ich Ihnen im Folgenden vorstellen möchte.
Mehrfache Sequenzen ohne AUTO_INCREMENT
Da AUTO_INCREMENT bei der Verwendung von InnoDB nur dann möglich ist, wenn das betreffende Feld alleinig den Primärschlüssel bildet, muss eine andere Lösung her. Die Lösung ist ein Trigger.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
DROP TABLE IF EXISTS bugreports; CREATE TABLE bugreports ( lfdnr INT NOT NULL, kategorie VARCHAR(20) NOT NULL, # referenziert eine Kategorietabelle beschreibung VARCHAR(100) NOT NULL, PRIMARY KEY (kategorie, lfdnr) ); DELIMITER $$ CREATE TRIGGER before_insert_bugreports BEFORE INSERT ON bugreports FOR EACH ROW BEGIN SET NEW.lfdnr = ( SELECT IFNULL(MAX(lfdnr), 0) + 1 FROM bugreports WHERE kategorie = NEW.kategorie ); END; DELIMITER ; |
Fügen Sie die obigen Datensätze in diese Tabelle ein, so erhalten Sie die gleiche Nummerierung, entsprechend den Kategorien, wie bei der Lösung mit MyISAM und AUTO_INCREMENT. Das Verhalten ist jedoch im Vergleich zu AUTO_INCREMENT insofern anders, als das die letzte vergebene Nummer innerhalb einer Kategorie erneut vergibt, wenn der betreffende letzte Datensatz gelöscht wurde. Damit kann man aber, wie ich meine, problemlos leben. Das gewünschte Grundprizip bleibt ja erhalten.
Fazit
Mehrfache Sequenzen mit AUTO_INCREMENT zu erstellen ist einfach, erfordern aber die Datenbank-Engine MyISAM. Für InnoDB die Standard-Datenbank-Engine, welche die meisten Entwickler aus guten Gründen bevorzugen, gibt es eine einfache Lösung, denselben Effekt mittels eines entsprechenden Triggers zu implementieren.
