Véritable tri alphanumérique/naturel dans MySQL - pourquoi la réponse est-elle toujours récursive ?

Hier, j'ai tenté de résoudre le tri alphanumérique dans MySQL et j'ai échoué. (lire cet article ici)

Je me suis quand même approché et j'ai eu le bon concept, juste une mauvaise exécution.

Aujourd'hui, je me suis réveillé et j'ai eu une révélation... récursion.

Le problème avec la récursivité est qu'il faut comprendre la récursivité pour pouvoir faire de la récursion... et je ne comprends pas suffisamment la récursivité pour faire de la récursivité dans MySQL.

Cependant, avec un peu de Chat Gippity dans les deux sens (j'entends par là lui faire écrire ce que j'ai demandé, récupérer environ 25 % de ce que j'ai demandé, le réparer et l'alimenter dans un nouveau chat pour que ce ne soit pas le cas) (ne continue pas à se répéter pendant environ 2 heures) J'ai obtenu une réponse fonctionnelle !

Au point

Puis-je vous présenter mon chant du cygne, mon chef-d'œuvre, la réponse à la vie elle-même (ok très bien, la seule solution efficace au véritable tri alphanumérique dans MySQL que j'ai vue).

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;

Et si vous voulez l'essayer (et essayer de le casser), vous pouvez jouer avec ce violon DB

Alors, comment ça marche ?

Il fait ce que je voulais faire à l'origine, prendre chaque groupe de nombres et les compléter à 10 chiffres au total.

Donc, évidemment, si vous alimentez ceci avec quelques chaînes avec 11 chiffres numériques consécutifs, cela ne fonctionnera pas sans ajustement, mais à part ça, cela fonctionne bien !

Vous voyez, MySQL peut trier les nombres correctement, même en mode de classement lexicographique, mais il a un défaut.

Il considère "11" comme plus petit que "2" car il trie un caractère à la fois (efficacement). Donc "2" est plus grand que "1", donc il vient en premier. Ensuite, il vérifie le caractère suivant, auquel cas le tri est incorrect (au moins pour les nombres).

Pour mieux comprendre cela, imaginez si 1 était réellement la lettre "b" et 2 était la lettre "c".

C'est ainsi que MySQL "voit" les nombres, ce ne sont que des caractères comme les autres.

Donc, si j'avais "bb" et "c", vous vous attendriez à ce que "bb" vienne avant "c". Maintenant, échangez les chiffres et vous comprendrez pourquoi « 11 » vient avant « 2 ».

Alors c'est un hack ?

Oui, nous supprimons le problème en déplaçant les chiffres vers l'arrière via le remplissage.

Pour en revenir à notre exemple, si nous complétons "11" et "2" à 3 en longueur et utilisons "a" comme 0, voici ce qui se passe :

011 = abb
002 = aac 

remarquez comment se déroulerait maintenant le tri :

• caractère 1 : est-ce que "a" est plus grand que "a" - non, ce sont les mêmes.
• caractère 2 : est-ce que "b" est plus grand que "a" - oui, mettez le "a" avant le "b"
• personnage 3 : n'est plus pertinent et nous avons déjà trouvé une occurrence plus tôt qui était différente et plus grande.

Donc, selon cette logique, nous avons maintenant :

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

Et c'est comme ça que ça marche !

Allez-vous expliquer le truc de la récursion ?

En quelque sorte. J'ai fait le tour des maisons avec celui-ci et mes connaissances sont superficielles, mais je vais essayer.

Le problème venait du fonctionnement de RegEx dans MySQL. REGEX_SUBSTR ne trouvera qu'une seule correspondance, puis continuera à la renvoyer pour chaque autre correspondance trouvée. C'est pourquoi ma solution d'hier ne fonctionnait pas correctement.

Mais REGEX_REPLACE a ses propres problèmes où il ne semble pas exposer correctement la longueur de chaîne d'une correspondance (nous ne pouvons donc pas LPAD avec correctement)

C'est pourquoi j'ai pensé à la récursivité comme réponse.

Je peux utiliser REGEX_SUBSTR pour obtenir le comportement de remplissage correct, et comme chaque boucle via RegEx est essentiellement un nouvel appel de fonction, elle ne "se souvient" pas de la correspondance précédente, donc cela résout ce problème.

Et si vous voulez un bref aperçu de la logique, ce n'est en fait pas aussi effrayant qu'il y paraît !

• Nous parcourons une chaîne donnée, à la recherche de n'importe quel nombre (le nombre entier, pas seulement un seul caractère).
• Nous supprimons ensuite cela de left_data afin de ne plus le faire correspondre.
• Nous prenons le numéro que nous venons de faire correspondre et le complétons pour qu'il comporte 10 chiffres au total.
• Nous recherchons ensuite la partie numérique suivante dans la chaîne et répétons le processus, en créantprocessed_data comme notre chaîne finale.
• Enfin, une fois que nous n'avons plus de nombres à traiter, nous ajoutons les lettres restantes à la fin de Processed_data pour terminer la transformation et nous le renvoyons sous sort_key.

Ensuite, nous pouvons utiliser cette clé de tri dans notre requête pour trier correctement la colonne.

Et la partie itération est purement un outil de sauvegarde, pour s'assurer qu'elle ne manque pas complètement de mémoire au serveur MySQL ou ne plante pas la requête si une chaîne suffisamment complexe est traitée (ou s'il y a une erreur dans la logique qui signifie cela se reproduirait pour toujours).

C'est fini !

N'est-ce pas drôle à quel point dormir sur des choses apporte une nouvelle perspective ?

Peut-être devrais-je essayer le sommeil polyphasique pour pouvoir dormir sur des problèmes 2 à 3 fois plus souvent chaque jour et devenir un développeur 10x ? haha.

Quoi qu'il en soit, voilà, un vrai tri alphanumérique raisonnablement robuste.

Oh et en réalité, vous devriez probablement convertir la clé de tri en une colonne stockée sur votre base de données à l'aide de GENERATE ou d'une procédure stockée. Malheureusement, le terrain de jeu que j'utilise ne semble pas supporter cela et c'est un dimanche donc je vous laisse le soin, cher spectateur !

Passez une bonne fin de week-end et une bonne semaine.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!