Echte alphanumerische/natürliche Sortierung in MySQL – warum ist die Antwort immer eine Rekursion?

Gestern habe ich versucht, die alphanumerische Sortierung in MySQL zu lösen, und bin gescheitert. (Lesen Sie diesen Artikel hier)

Ich war aber nah dran und hatte das richtige Konzept, nur die falsche Umsetzung.

Heute bin ich aufgewacht und hatte eine Offenbarung ... Rekursion.

Das Problem mit der Rekursion besteht darin, dass man Rekursion verstehen muss, um Rekursion durchführen zu können ... und ich verstehe Rekursion nicht genug, um Rekursion in MySQL durchzuführen.

Allerdings mit ein bisschen Chat-Gippity hin und her (womit ich meine, dass es schreibt, was ich verlangt habe, etwa 25 % von dem zurückbekomme, was ich verlangt habe, es reparieren und es in einen neuen Chat einspeisen, damit es funktioniert Ich habe eine funktionierende Antwort bekommen!

Auf den Punkt

Darf ich Ihnen meinen Abgesang vorstellen, mein Meisterwerk, die Antwort auf das Leben selbst (ok, gut, die einzige funktionierende Lösung für echte alphanumerische Sortierung in MySQL, die ich gesehen habe).

WITH RECURSIVE process_numbers AS (
    SELECT 
        data_value,
        data_value AS remaining_data,
        CAST('' AS CHAR(20000)) AS processed_data,
        1 AS iteration
    FROM test_data

    UNION ALL

    SELECT
        data_value,
        CASE 
            WHEN LOCATE(REGEXP_SUBSTR(remaining_data, '[0-9]+'), remaining_data) > 0 THEN
                SUBSTRING(
                    remaining_data,
                    LOCATE(REGEXP_SUBSTR(remaining_data, '[0-9]+'), remaining_data)
                    + LENGTH(REGEXP_SUBSTR(remaining_data, '[0-9]+'))
                )
            ELSE '' 
        END AS remaining_data,

        CONCAT(
            processed_data,
            CASE 
                WHEN LOCATE(REGEXP_SUBSTR(remaining_data, '[0-9]+'), remaining_data) > 0 THEN
                    LEFT(remaining_data, LOCATE(REGEXP_SUBSTR(remaining_data, '[0-9]+'), remaining_data) - 1)
                ELSE remaining_data
            END,
            CASE
                WHEN REGEXP_SUBSTR(remaining_data, '[0-9]+') IS NOT NULL THEN
                    RIGHT(CONCAT('0000000000', REGEXP_SUBSTR(remaining_data, '[0-9]+')), 10)
                ELSE ''
            END
        ) AS processed_data,

        iteration + 1
    FROM process_numbers
    WHERE LENGTH(remaining_data) > 0
          AND iteration < 100
)


SELECT 
    data_value,
    CONCAT(processed_data, remaining_data) AS sort_key
FROM process_numbers
WHERE remaining_data = ""
ORDER BY sort_key;

Und wenn Sie es ausprobieren (und versuchen möchten, es zu brechen), können Sie mit dieser DB-Geige spielen

Wie funktioniert das?

Es macht das, was ich ursprünglich tun wollte: Nehmen Sie jede Zahlengruppe und füllen Sie sie auf insgesamt 10 Ziffern auf.

Wenn Sie dem also ein paar Zeichenfolgen mit 11 aufeinanderfolgenden numerischen Ziffern zuführen, funktioniert es ohne Anpassung natürlich nicht, aber ansonsten funktioniert es gut!

Sie sehen, MySQL kann Zahlen korrekt sortieren, sogar im lexikografischen Sortiermodus, aber es hat einen Fehler.

„11“ zählt als kleiner als „2“, da (effektiv) ein Zeichen nach dem anderen sortiert wird. „2“ ist also größer als „1“ und steht daher an erster Stelle. Dann prüft es das nächste Zeichen. Ab diesem Zeitpunkt ist die Sortierung falsch (zumindest bei Zahlen).

Um dies besser zu verstehen, stellen Sie sich vor, 1 wäre tatsächlich der Buchstabe „b“ und 2 wäre der Buchstabe „c“.

So „sieht“ MySQL Zahlen, sie sind nur ein weiteres Zeichen.

Wenn ich also „bb“ und „c“ hätte, erwarten, dass „bb“ vor „c“ steht. Tauschen Sie nun die Zahlen wieder ein und Sie können sehen, warum „11“ vor „2“ kommt.

Das ist also ein Hack?

Ja, wir beheben das Problem, indem wir die Zahlen durch Auffüllen „nach hinten“ verschieben.

Um auf unser Beispiel zurückzukommen: Wenn wir „11“ und „2“ auf die Länge 3 auffüllen und „a“ als 0 verwenden, passiert Folgendes:

011 = abb
002 = aac 

Beachten Sie, wie die Sortierung jetzt ablaufen würde:

• Zeichen 1: ist „a“ größer als „a“ – nein, sie sind gleich.
• Zeichen 2: ist „b“ größer als „a“ – ja, setzen Sie das „a“ vor das „b“
• Zeichen 3: ist jetzt irrelevant und wir haben bereits früher ein Vorkommen gefunden, das anders und größer war.

Nach dieser Logik haben wir jetzt:

002 = aac (the second "a" comes before the second "b" in the next row)
011 = abb

Und so funktioniert es!

Wirst du die Sache mit der Rekursion erklären?

Irgendwie. Ich bin damit schon „rund um die Häuser“ gegangen und mein Wissen ist oberflächlich, aber ich werde es versuchen.

Das Problem lag darin, wie RegEx in MySQL funktioniert. REGEX_SUBSTR findet immer nur eine Übereinstimmung und gibt diese dann für jede andere gefundene Übereinstimmung zurück. Deshalb funktionierte meine Lösung von gestern nicht richtig.

Aber REGEX_REPLACE hat seine eigenen Probleme, bei denen es die Zeichenfolgenlänge einer Übereinstimmung scheinbar nicht korrekt offenlegt (wir können also kein LPAD damit korrekt durchführen)

Deshalb dachte ich über Rekursion als Antwort nach.

Ich kann REGEX_SUBSTR verwenden, um das richtige Auffüllverhalten zu erhalten, und da jede Schleife durch RegEx im Wesentlichen ein neuer Funktionsaufruf ist, „merkt“ es sich nicht an die vorherige Übereinstimmung, sodass dieses Problem gelöst wird.

Und wenn Sie einen kurzen Einblick in die Logik wünschen, ist es tatsächlich nicht so beängstigend, wie es aussieht!

• Wir durchlaufen eine bestimmte Zeichenfolge und suchen nach beliebigen Zahlen (der gesamten Zahl, nicht nur einem einzelnen Zeichen).
• Wir entfernen das dann aus den verbleibenden_Daten, damit wir es nicht erneut abgleichen.
• Wir nehmen die Zahl, die wir gerade gefunden haben, und ergänzen sie auf insgesamt 10 Ziffern.
• Wir suchen dann nach dem nächsten numerischen Teil in der Zeichenfolge und wiederholen den Vorgang, wobei wir „processed_data“ als unsere endgültige Zeichenfolge aufbauen.
• Sobald wir schließlich keine Zahlen mehr verarbeiten müssen, fügen wir alle verbleibenden Buchstaben am Ende von „processed_data“ hinzu, um die Transformation abzuschließen, und geben dies als sort_key zurück.

Dann können wir diesen Sortierschlüssel in unserer Abfrage verwenden, um die Spalte korrekt zu ordnen.

Und der Iterationsteil ist ein reines Sicherheitstool, um sicherzustellen, dass der MySQL-Server nicht völlig über genügend Speicher verfügt oder die Abfrage abstürzt, wenn eine ausreichend komplexe Zeichenfolge verarbeitet wird (oder ein Fehler in der Logik vorliegt, die bedeutet). es würde für immer wiederkehren).

Das ist ein Wrap!

Ist es nicht lustig, wie das Schlafen auf Dingen neue Perspektiven eröffnet?

Vielleicht sollte ich den polyphasischen Schlaf ausprobieren, damit ich jeden Tag zwei- bis dreimal häufiger bei Problemen schlafen kann und ein 10-facher Entwickler werde? haha.

Wie auch immer, da haben Sie es, eine einigermaßen robuste echte alphanumerische Sortierung.

Oh, und in Wirklichkeit sollten Sie den Sortierschlüssel wahrscheinlich mithilfe von GENERATE oder einer gespeicherten Prozedur in eine gespeicherte Spalte in Ihrer Datenbank konvertieren. Leider scheint der Spielplatz, den ich nutze, das nicht zu unterstützen und es ist ein Sonntag, also überlasse ich das Ihnen, lieber Zuschauer!

Ein schönes Restwochenende und eine tolle Woche.

Das obige ist der detaillierte Inhalt vonEchte alphanumerische/natürliche Sortierung in MySQL – warum ist die Antwort immer eine Rekursion?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!