Naturel de Tri dans MySQL

Voici une solution rapide:

SELECT alphanumeric, 
       integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric

vient de trouver ceci:

SELECT names FROM your_table ORDER BY games + 0 ASC

fait un tri naturel quand les nombres sont à l'avant, pourrait fonctionner pour le milieu aussi bien.

même fonction que Postée par @plalx, mais réécrite à 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 = CHAR_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, CHAR_LENGTH(instring) - numEndIndex));
    END WHILE;

    RETURN sortString;
END
;;

Utilisation:

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

MySQL ne permet pas ce genre de "tri naturel", il semble donc que la meilleure façon d'obtenir ce que vous recherchez est de diviser vos données configurées comme vous l'avez décrit ci-dessus (champ ID séparé, etc), ou à défaut, effectuer un tri basé sur un élément non-titre, élément indexé dans votre db (date, id inséré dans la db, etc).

avoir le db faire le tri pour vous sera presque toujours plus rapide que la lecture de grands ensembles de données dans le langage de programmation de votre choix et le tri là-bas, donc si vous avez un contrôle sur tout le schéma de base de données ici, puis regarder à ajouter des champs facilement triés comme décrit ci-dessus, il vous fera économiser beaucoup de tracas et de maintenance à long terme.

des demandes d'ajout d'une " sorte naturelle "surgissent de temps en temps sur les bogues MySQL et forums de discussion , et de nombreuses solutions tournent autour de la suppression de certaines parties de vos données et de leur moulage pour le ORDER BY partie de la requête, par exemple

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

ce genre de solution pourrait à peu près être fait pour travailler sur votre dernier exemple de fantaisie ci-dessus, mais n'est pas particulièrement flexible et peu probable d'étendre proprement à un ensemble de données, y compris, disons," Warhammer 40,000 "et" James Bond 007 " j'ai bien peur.

j'ai écrit cette fonction pour MSSQL 2000 il y a quelque temps:

/**
 * Returns a string formatted for natural sorting. This function is very useful when having to sort alpha-numeric strings.
 *
 * @author Alexandre Potvin Latreille (plalx)
 * @param {nvarchar(4000)} string The formatted string.
 * @param {int} numberLength The length each number should have (including padding). This should be the length of the longest number. Defaults to 10.
 * @param {char(50)} sameOrderChars A list of characters that should have the same order. Ex: '.-/'. Defaults to empty string.
 *
 * @return {nvarchar(4000)} A string for natural sorting.
 * Example of use: 
 * 
 *      SELECT Name FROM TableA ORDER BY Name
 *  TableA (unordered)              TableA (ordered)
 *  ------------                    ------------
 *  ID  Name                    ID  Name
 *  1.  A1.                 1.  A1-1.       
 *  2.  A1-1.                   2.  A1.
 *  3.  R1      -->         3.  R1
 *  4.  R11                 4.  R11
 *  5.  R2                  5.  R2
 *
 *  
 *  As we can see, humans would expect A1., A1-1., R1, R2, R11 but that's not how SQL is sorting it.
 *  We can use this function to fix this.
 *
 *      SELECT Name FROM TableA ORDER BY dbo.udf_NaturalSortFormat(Name, default, '.-')
 *  TableA (unordered)              TableA (ordered)
 *  ------------                    ------------
 *  ID  Name                    ID  Name
 *  1.  A1.                 1.  A1.     
 *  2.  A1-1.                   2.  A1-1.
 *  3.  R1      -->         3.  R1
 *  4.  R11                 4.  R2
 *  5.  R2                  5.  R11
 */
CREATE FUNCTION dbo.udf_NaturalSortFormat(
    @string nvarchar(4000),
    @numberLength int = 10,
    @sameOrderChars char(50) = ''
)
RETURNS varchar(4000)
AS
BEGIN
    DECLARE @sortString varchar(4000),
        @numStartIndex int,
        @numEndIndex int,
        @padLength int,
        @totalPadLength int,
        @i int,
        @sameOrderCharsLen int;

    SELECT 
        @totalPadLength = 0,
        @string = RTRIM(LTRIM(@string)),
        @sortString = @string,
        @numStartIndex = PATINDEX('%[0-9]%', @string),
        @numEndIndex = 0,
        @i = 1,
        @sameOrderCharsLen = LEN(@sameOrderChars);

    -- Replace all char that has to have the same order by a space.
    WHILE (@i <= @sameOrderCharsLen)
    BEGIN
        SET @sortString = REPLACE(@sortString, SUBSTRING(@sameOrderChars, @i, 1), ' ');
        SET @i = @i + 1;
    END

    -- Pad numbers with zeros.
    WHILE (@numStartIndex <> 0)
    BEGIN
        SET @numStartIndex = @numStartIndex + @numEndIndex;
        SET @numEndIndex = @numStartIndex;

        WHILE(PATINDEX('[0-9]', SUBSTRING(@string, @numEndIndex, 1)) = 1)
        BEGIN
            SET @numEndIndex = @numEndIndex + 1;
        END

        SET @numEndIndex = @numEndIndex - 1;

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

        IF @padLength < 0
        BEGIN
            SET @padLength = 0;
        END

        SET @sortString = STUFF(
            @sortString,
            @numStartIndex + @totalPadLength,
            0,
            REPLICATE('0', @padLength)
        );

        SET @totalPadLength = @totalPadLength + @padLength;
        SET @numStartIndex = PATINDEX('%[0-9]%', RIGHT(@string, LEN(@string) - @numEndIndex));
    END

    RETURN @sortString;
END

GO

donc, alors que je sais que vous avez trouvé une réponse satisfaisante, je me débattais avec ce problème pendant un certain temps, et nous avions précédemment déterminé que cela ne pouvait pas être fait raisonnablement bien dans SQL et nous allions devoir utiliser javascript sur un tableau JSON.

Voici comment je l'ai résolu en utilisant SQL. J'espère que c'est utile pour les autres:

j'ai eu des données telles que:

Scene 1
Scene 1A
Scene 1B
Scene 2A
Scene 3
...
Scene 101
Scene XXA1
Scene XXA2

en fait, je n'ai pas" moulé " les choses bien que je supposons que cela ait aussi fonctionné.

j'ai d'abord remplacé les parties qui étaient immuables dans les données, dans ce cas "scène", puis fait un LPAD pour aligner les choses. Cela semble permettre assez bien pour l'alpha chaînes de trier correctement ainsi que le numérotés.

Mon ORDER BY clause ressemble à:

ORDER BY LPAD(REPLACE(`table`.`column`,'Scene ',''),10,'0')

évidemment cela n'aide pas avec le problème original qui n'était pas si uniforme - mais j'imagine que ce serait probablement travailler pour beaucoup d'autres problèmes liés, donc mettre là-bas.

1. ajouter une clé de tri (rang) dans votre tableau. ORDER BY rank

2. utilisez la colonne" Date de libération". ORDER BY release_date

3. lors de L'extraction des données à partir de SQL, faites votre objet faire le tri, par exemple, si l'extraction dans un ensemble, en faire un arbre, et faire de votre modèle de données mettre en œuvre Comparable et d'appliquer l'algorithme de tri naturel ici (tri d'insertion sera suffisant si vous utilisez un langue sans collections) comme vous allez lire les lignes de SQL un par un comme vous créez votre modèle et l'insérez dans la collection)

concernant la meilleure réponse de Richard Toth https://stackoverflow.com/a/12257917/4052357

méfiez-vous des chaînes encodées UTF8 qui contiennent des caractères et des nombres de 2 octets (ou plus), p.ex.

12 南新宿

en utilisant la fonction LENGTH() de MySQL dans udf_NaturalSortFormat retournera la longueur de byte de la chaîne et sera incorrect, à la place d'utiliser CHAR_LENGTH() qui retournera la longueur de caractère correcte.

dans mon cas, l'utilisation de LENGTH() a causé des requêtes qui n'ont jamais été complétées et a entraîné une utilisation 100% CPU pour MySQL

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 = CHAR_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, CHAR_LENGTH(instring) - numEndIndex));
    END WHILE;

    RETURN sortString;
END
;;

p. S. J'aurais ajouté ceci comme commentaire à l'original mais je n'ai pas assez de réputation (encore)

une autre option est de faire le tri en mémoire après avoir tiré les données de mysql. Bien que ce ne soit pas la meilleure option du point de vue de la performance, si vous ne triez pas des listes énormes, vous devriez être très bien.

si vous jetez un coup d'oeil au billet de Jeff, vous pouvez trouver beaucoup d'algorithmes pour n'importe quelle langue avec laquelle vous pourriez travailler. http://www.codinghorror.com/blog/archives/001018.html

si vous ne voulez pas réinventer la roue ou avoir un mal de tête avec beaucoup de code qui ne fonctionne pas, il suffit d'utiliser Drupal Natural Sort ... Il suffit de lancer le SQL qui vient zippé (MySQL ou Postgre), et c'est tout. Lorsque vous faites une requête, commandez simplement en utilisant:

... ORDER BY natsort_canon(column_name, 'natural')

Ajouter un champ pour "clé de tri" qui a toutes les chaînes de chiffres zéro-rembourré à une longueur fixe et puis trier sur ce champ à la place.

si vous pouvez avoir de longues chaînes de chiffres, une autre méthode consiste à pré-tendre le nombre de chiffres (Largeur fixe, zéro-rembourré) à chaque chaîne de chiffres. Par exemple, si vous n'avez pas plus de 99 chiffres dans une rangée, alors pour "Super Blast 10 Ultra" la touche de tri serait "Super Blast 0210 Ultra".

vous pouvez aussi créer de manière dynamique la "colonne de tri":

SELECT name, (name = '-') boolDash, (name = '0') boolZero, (name+0 > 0) boolNum 
FROM table 
ORDER BY boolDash DESC, boolZero DESC, boolNum DESC, (name+0), name

de Cette façon, vous pouvez créer des groupes pour trier.

dans ma requête, je voulais le " - " devant tout, puis les nombres, puis le texte. Ce qui pourrait donner quelque chose comme:

-
0    
1
2
3
4
5
10
13
19
99
102
Chair
Dog
Table
Windows

de cette façon vous n'avez pas à maintenir la colonne de tri dans le bon ordre que vous ajoutez des données. Vous pouvez également modifier votre ordre de tri selon ce que vous besoin.

SELECT 
    column_name 
FROM 
    table_name 
ORDER BY
    column_name REGEXP '^\d*[^\da-z&\.\' \-\"\!\@\#$\%\^\*\(\)\;\:\,\?\/\~\`\|\_\-]' DESC, 
    column_name + 0, 
    column_name;

j'ai essayé plusieurs solutions mais le fait est très simple:

SELECT test_column FROM test_table ORDER BY LENGTH(test_column) DESC, test_column DESC

/* 
Result 
--------
value_1
value_2
value_3
value_4
value_5
value_6
value_7
value_8
value_9
value_10
value_11
value_12
value_13
value_14
value_15
...
*/

si vous utilisez PHP, vous pouvez faire le tri naturel en php.

$keys = array();
$values = array();
foreach ($results as $index => $row) {
   $key = $row['name'].'__'.$index; // Add the index to create an unique key.
   $keys[] = $key;
   $values[$key] = $row; 
}
natsort($keys);
$sortedValues = array(); 
foreach($keys as $index) {
  $sortedValues[] = $values[$index]; 
}

J'espère que MySQL implémentera le tri naturel dans une version future, mais la demande de fonctionnalité (#1588) est ouverte depuis 2003, donc je ne retiendrai pas mon souffle.

une version simplifiée non-udf de la meilleure réponse de @plaix / Richard Toth/Luke Hoggett, qui ne fonctionne que pour le premier entier dans le domaine, est

SELECT name,
LEAST(
    IFNULL(NULLIF(LOCATE('0', name), 0), ~0),
    IFNULL(NULLIF(LOCATE('1', name), 0), ~0),
    IFNULL(NULLIF(LOCATE('2', name), 0), ~0),
    IFNULL(NULLIF(LOCATE('3', name), 0), ~0),
    IFNULL(NULLIF(LOCATE('4', name), 0), ~0),
    IFNULL(NULLIF(LOCATE('5', name), 0), ~0),
    IFNULL(NULLIF(LOCATE('6', name), 0), ~0),
    IFNULL(NULLIF(LOCATE('7', name), 0), ~0),
    IFNULL(NULLIF(LOCATE('8', name), 0), ~0),
    IFNULL(NULLIF(LOCATE('9', name), 0), ~0)
) AS first_int
FROM table
ORDER BY IF(first_int = ~0, name, CONCAT(
    SUBSTR(name, 1, first_int - 1),
    LPAD(CAST(SUBSTR(name, first_int) AS UNSIGNED), LENGTH(~0), '0'),
    SUBSTR(name, first_int + LENGTH(CAST(SUBSTR(name, first_int) AS UNSIGNED)))
)) ASC

il y a aussi natsort . Il est destiné à faire partie d'un Drupal plugin , mais il fonctionne bien autonome.

je sais que ce sujet est ancien mais je pense que j'ai trouvé un moyen de le faire:

SELECT * FROM `table` ORDER BY 
CONCAT(
  GREATEST(
    LOCATE('1', name),
    LOCATE('2', name),
    LOCATE('3', name),
    LOCATE('4', name),
    LOCATE('5', name),
    LOCATE('6', name),
    LOCATE('7', name),
    LOCATE('8', name),
    LOCATE('9', name)
   ),
   name
) ASC

la Ferraille, il a trié les suivantes mal réglé (C'est inutile lol):

Final Fantasy 1 Final Fantasy 2 Final Fantasy 5 Final Fantasy 7 Final Fantasy 7: Advent Children Final Fantasy 12 Final Fantasy 112 FF1 FF2