Natürliche Sortierung bedeutet beispielsweise die Ausgabe folgender Daten 1A, 10C, 10A, 2A, 3B in der folgenden Reihenfolge. Die führenden Ziffern werden dabei als Zahlen interpretiert: 1A, 2A, 3B, 10A, 10C.
Mit der üblichen ORDER BY-Klausel ist das Ergebnis nicht zu erzielen. Das folgende Beispiel zeigt das Standardverhalten.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE DATABASE mydb; USE mydb; DROP TABLE IF EXISTS items; CREATE TABLE items ( item VARCHAR(10) NOT NULL ); INSERT INTO items (item) VALUES ('1A'), ('10C'), ('10A'), ('2A'), ('3B'); SELECT * FROM items ORDER BY item; |
Die Ausgabe erfolgt in der Sortierung: 10A, 10C, 1A, 2A, 3B – sie ist offensichtlich alphabetisch.
Es gibt nun verschiedene Herangehensweisen, das Problem zu lösen, die gewünschte Sortierung zu erhalten.
Natürliche Sortierung durch Zerlegung
Die einfachste und m.E. offensichtlichste Möglichkeit besteht darin, das Datenfeld item in zwei Teile zu zerlegen.
|
1 2 3 4 5 6 7 8 9 10 |
DROP TABLE IF EXISTS items; CREATE TABLE items ( itemno INT NOT NULL, itemstr VARCHAR(10) NOT NULL ); INSERT INTO items (itemno, itemstr) VALUES (1, 'A'), (10, 'C'), (10, 'A'), (2, 'A'), (3, 'B'); SELECT CONCAT(itemno, itemstr) FROM items ORDER BY itemno, itemstr; |
Bei der Ausgabe wird dann erst numerisch nach dem Datenfeld itemno und danach alphabetisch nach dem Feld itemstr sortiert. Damit die Ausgabe den Erwartungen entspricht werden die beiden Felder mittels CONCAT zusammengefügt.
Diese Form der Zerlegung ist immer möglich.
Natürliche Sortierung dynamisch bestimmen
Trennzeichen
Manchmal lässt es die Datenbeschaffenheit zu, ein stets eindeutig vorhandenes Trennzeichen zu nutzen. Betrachten wir eine leichte Modifikation der oben vorgestellten Daten.
|
1 2 3 4 5 6 |
DROP TABLE IF EXISTS items; CREATE TABLE items ( item VARCHAR(10) NOT NULL ); INSERT INTO items (item) VALUES ('1-A'), ('10-C'), ('10-A'), ('2-A'), ('3-B'); |
Für eine natürliche Sortierung müssen wir ein Item für die Sortierung in den linken und rechten Teil vom Bindestrich zerlegen und die beiden Teile separat sortieren. Mit den integrierten Funktionen POSITION und SUBSTRING, die wohl vielen zuerst einfallen, kann das leicht bewirkt werden. Der resultierende Code wäre aber etwas umständlich. Man würde sich wohl selbst entsprechende Funktionen schreiben, die unter Ausnutzung dieser Funktionen den linken und rechten Teil eines Items liefern.
Vielleicht weniger bekannt, aber für unsere Zwecke sehr viel einfacher zu nutzen, ist die integrierte Funktion SUBSTRING_INDEX. Sie liefert die Teilzeichenkette vor dem n-ten Auftreten des angegebenen Trennzeichens. In unserem Fall taucht das Trennzeichen nur einmal auf. Wir benötigen den linken und den rechten Teil. Das ist einfach.
|
1 2 3 |
SELECT * FROM items ORDER BY SUBSTRING_INDEX(item, '-', 1), SUBSTRING_INDEX(item, '-', -1); |
Positive Zähler beginnen links, negative Zähler rechts. Daher erhalten wir mit -1 den rechts stehenden Teil eines Items. Das Ganze funktioniert auch bei komplexeren Strukturen.
Trick: Längenfunktion
Je nach Datenlage können wir uns aber eventuell auch eines weniger offensichtlichen Tricks bedienen. Dazu müssen die Daten allerdings entsprechend beschaffen sein. Nehmen wir an, es gebe immer eine Kennung, die aus einem einzelnen Buchstaben bestehe, der zudem immer gleich ist. Dem folgt dann eine Ziffernfolge, die die eigentliche Reihenfolge bei der Sortierung bestimmen soll.
|
1 2 3 4 5 6 |
DROP TABLE IF EXISTS items; CREATE TABLE items ( item VARCHAR(10) NOT NULL ); INSERT INTO items (item) VALUES ('A1'), ('A11'), ('A10'), ('A2'), ('A3'); |
Wie gehabt, funktioniert ein einfaches ORDER BY nicht entsprechend dem, was wir uns wünschen. Wir können in diesem Fall aber die Länge der Zeichenketten ausnutzen. Längere Zeichenketten sind größer als kürzere. Innerhalb gleicher Längen funktioniert die alphabetische Sortierung. Das liegt daran, weil die Ziffern in allen Zeichensätzen mit 0 bis 9 durchnummeriert sind.
|
1 2 |
SELECT * FROM items ORDER BY LENGTH(item), item; |
Das funktioniert nicht mehr, wenn die Datensätze mit unterschiedlichen Buchstaben beginnen!
Reguläre Ausdrücke
MySQL wäre nicht MySQL, gäbe es nicht auch für solche Problemstellungen eine einfache Lösung. Die betrachteten Beispieldaten seien die folgenden:
|
1 2 3 4 5 6 |
DROP TABLE IF EXISTS items; CREATE TABLE items ( item VARCHAR(10) NOT NULL ); INSERT INTO items (item) VALUES ('A1'), ('A11'), ('AB10'), ('C3'), ('A10'), ('A2'), ('A3'); |
Wir habe verschiedene Buchstaben und verschiedene Anzahlen von Buchstaben, die dem numerischen Teil vorstehen. Mithilfe regulärer Ausdrücke ist es jedoch ein Leichtes, auch diese Daten einer natürlichen Sortierung zuzuführen. Wir müssen lediglich den alphanumerischen Teil vom numerischen Teil trennen. Da diese Teile jeweils unterschiedlich lang sein können, bieten sich reguläre Ausdrücke an, ein Item zu analysieren und für die Sortierung passend zu zerlegen. Selbstgeschriebene Funktionen sind natürlich auch möglich, m.E. aber viel zu umständlich und letztlich auch wohl langsamer.
|
1 2 3 4 5 6 |
SELECT * FROM items ORDER BY REGEXP_SUBSTR(item, '^[[:alpha:]]*'), CAST(REGEXP_SUBSTR(item, '[[:digit:]]*$') AS UNSIGNED); |
Das Statement sieht komplizierter aus, als es ist. REGEXP_SUBSTR(item, '^[[:alpha:]]*') extrahiert alle führenden Zeichen, die keine Ziffern sind. Der zweite Teil des Sortierausdrucks extrahiert alle Ziffern am Ende der Zeichenkette und wandelt Sie in ganze Zahlen um – sie werden jetzt numerisch sortiert. Sehen Sie sich die Dokumentation zu regulären Ausdrücken an. Es gibt neben REGEXP_SUBSTR noch weitere Funktionen, die reguläre Ausdrücke unterstützen.
Fazit
Auch wenn MySQL die natürliche Sortierung nicht direkt unterstützt, ist es dennoch ein Leichtes, diese für gegebene Daten zu bewirken. Im günstigsten Fall haben wir Einfluss auf das Datenbankdesign, wie im ersten Fall gezeigt. Der Trick mit der Längenfunktion ist ein echter Sonderfall. Er zeigt aber sehr schön, wie mit ein wenig Fantasie ein Workaround gefunden werden kann.
Reguläre Ausdrücke bieten dagegen einen sehr allgemeinen Ansatz, das Problem einer natürlichen Sortierung lösen zu können. Wer mit ihnen nicht vertraut ist, wird dann eventuell ein wenig herumexperimentieren müssen.
