Tri naturel dans MySQL

Question

Est-il un moyen élégant d'avoir performant, tri naturel dans une base de données MySQL?

Par exemple, si j'ai ce jeu de données:

• Final Fantasy
• Final Fantasy 4
• Final Fantasy 10
• Final Fantasy 12
• Final Fantasy 12: Chains of Promathia
• Final Fantasy Aventure
• Final Fantasy Origins
• Final Fantasy Tactics

Toute autre élégante solution que de diviser les noms de jeux dans leurs composantes

• Titre: "Final Fantasy"
• Numéro de: "12"
• Sous-titre: "Chains of Promathia"

assurez-vous qu'ils viennent de sortir dans le bon ordre? (10) après 4, pas avant 2).

Cela est une douleur dans le** parce que chaque maintenant et puis, il y a un autre jeu qui casse le mécanisme de l'analyse du titre du jeu (par exemple, "Warhammer 40,000", "James Bond 007")

Answer 1

Voici une solution rapide:

SELECT alphanumérique, entier FROM trie_test ORDER BY LENGTH (alphanumérique), alphanumérique

Answer 2

Même fonction que celle publiée par @plalx, mais réécrite dans MySQL:

 DROP FUNCTION IF EXISTS `udf_FirstNumberPos`;
DELIMITER ;;
CREATE FUNCTION `udf_FirstNumberPos` (`instring` varchar(4000)) 
RETURNS int
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
    DECLARE position int;
    DECLARE tmp_position int;
    SET position = 5000;
    SET tmp_position = LOCATE('0', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF; 
    SET tmp_position = LOCATE('1', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('2', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('3', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('4', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('5', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('6', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('7', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('8', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('9', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;

    IF (position = 5000) THEN RETURN 0; END IF;
    RETURN position;
END
;;

DROP FUNCTION IF EXISTS `udf_NaturalSortFormat`;
DELIMITER ;;
CREATE FUNCTION `udf_NaturalSortFormat` (`instring` varchar(4000), `numberLength` int, `sameOrderChars` char(50)) 
RETURNS varchar(4000)
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
    DECLARE sortString varchar(4000);
    DECLARE numStartIndex int;
    DECLARE numEndIndex int;
    DECLARE padLength int;
    DECLARE totalPadLength int;
    DECLARE i int;
    DECLARE sameOrderCharsLen int;

    SET totalPadLength = 0;
    SET instring = TRIM(instring);
    SET sortString = instring;
    SET numStartIndex = udf_FirstNumberPos(instring);
    SET numEndIndex = 0;
    SET i = 1;
    SET sameOrderCharsLen = LENGTH(sameOrderChars);

    WHILE (i <= sameOrderCharsLen) DO
        SET sortString = REPLACE(sortString, SUBSTRING(sameOrderChars, i, 1), ' ');
        SET i = i + 1;
    END WHILE;

    WHILE (numStartIndex <> 0) DO
        SET numStartIndex = numStartIndex + numEndIndex;
        SET numEndIndex = numStartIndex;

        WHILE (udf_FirstNumberPos(SUBSTRING(instring, numEndIndex, 1)) = 1) DO
            SET numEndIndex = numEndIndex + 1;
        END WHILE;

        SET numEndIndex = numEndIndex - 1;

        SET padLength = numberLength - (numEndIndex + 1 - numStartIndex);

        IF padLength < 0 THEN
            SET padLength = 0;
        END IF;

        SET sortString = INSERT(sortString, numStartIndex + totalPadLength, 0, REPEAT('0', padLength));

        SET totalPadLength = totalPadLength + padLength;
        SET numStartIndex = udf_FirstNumberPos(RIGHT(instring, LENGTH(instring) - numEndIndex));
    END WHILE;

    RETURN sortString;
END
;;
 

Usage:

 SELECT name FROM products ORDER BY udf_NaturalSortFormat(name, 10, ".")
 

Answer 3

Je pense que c'est pourquoi beaucoup de choses sont triées par date de sortie.

Une solution pourrait être de créer une autre colonne dans votre table pour la "Clé de tri". Il peut s'agir d'une version assainie du titre, conforme à un modèle que vous créez pour faciliter le tri ou à un compteur.

Answer 4

MySQL n'autorise pas ce genre de "tri naturel", donc il semble que la meilleure façon d'obtenir ce que vous êtes après est de diviser votre jeu de données comme vous l'avez décrit ci-dessus (id séparé de champ, etc), ou, à défaut, effectuer un tri basé sur un non-élément de titre, indexé en fonction de l'élément dans votre base de données (date, id inséré dans la base de données, etc).

Avoir la db de faire le tri pour vous, c'est presque toujours va être plus rapide que la lecture de grands ensembles de données dans votre langage de programmation de choix et le tri là-bas, donc si vous avez aucun contrôle sur la db schéma ici, puis regardez l'ajout de facilement triés champs comme décrit ci-dessus, il vous permettra de gagner beaucoup de tracas et de maintenance à long terme.

Les demandes d'ajout d'un "naturel tri" de venir de temps en temps sur le MySQL de bugs et des forums de discussion, et de nombreuses solutions s'articulent autour de décapage des parties de vos données et de moulage pour l' ORDER BY de la partie de la requête, par exemple

SELECT * FROM table ORDER BY CAST(mid(name, 6, LENGTH(c) -5) AS unsigned)

Ce type de solution pourrait juste au sujet de travailler sur votre Final Fantasy exemple ci-dessus, mais n'est pas particulièrement flexible et peu de chances d'étendre proprement à un ensemble de données, y compris, par exemple, "Warhammer 40,000" et "James Bond 007" j'ai peur.