Sortowanie naturalne w MySQL

Oto szybkie rozwiązanie:

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

Just found this:

SELECT names FROM your_table ORDER BY games + 0 ASC

Robi sortowanie naturalne, gdy liczby są z przodu, może działać również dla środka.

Ta sama funkcja, którą napisał @plalx, ale przepisana do 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
;;

Użycie:

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

MySQL nie pozwala na tego rodzaju "naturalne sortowanie", więc wygląda na to, że najlepszym sposobem, aby uzyskać to, czego szukasz, jest podzielenie konfiguracji danych zgodnie z opisem powyżej (oddzielne pole id, itp.), lub w przeciwnym razie, wykonaj sortowanie oparte na elemencie nie-title, zindeksowanym elemencie w db (data, wstawione id w db, itp.).

Sortowanie przez db jest prawie zawsze szybsze niż odczytywanie dużych zbiorów danych do wybranego języka programowania i sortowanie ich tam, jeśli więc masz jakąkolwiek kontrolę nad schematem db tutaj, spójrz na dodawanie łatwo posortowanych pól, jak opisano powyżej, zaoszczędzi ci to wiele kłopotów i konserwacji na dłuższą metę.

Prośby o dodanie "sortowania naturalnego" pojawiają się od czasu do czasu na błędy MySQL i forach dyskusyjnych , a wiele rozwiązań kręci się wokół usuwania określonych części danych i odlewania ich dla ORDER BY części zapytania, np.]}

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

Takie rozwiązanie może po prostu o być wykonane do pracy na przykład Final Fantasy powyżej, ale nie jest szczególnie elastyczny i mało prawdopodobne, aby rozszerzyć czysto do zbioru danych, w tym, powiedzmy, "Warhammer 40,000" i "James Bond 007" obawiam się.

Napisałem tę funkcję dla MSSQL 2000 jakiś czas temu:

/**
 * 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

Tak więc, chociaż wiem, że znalazłeś satysfakcjonującą odpowiedź, zmagałem się z tym problemem przez jakiś czas, a my wcześniej stwierdziliśmy, że nie można tego zrobić rozsądnie dobrze w SQL i będziemy musieli użyć javascript na tablicy JSON.

Oto jak rozwiązałem to za pomocą SQL. Mam nadzieję, że jest to pomocne dla innych:

Miałem dane takie jak:

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

Właściwie nie" rzucałem " rzeczy, choć przypuszczam, że to również mogło zadziałać.

I first replaced części, które były niezmienne w danych, w tym przypadku " scena ", a następnie zrobił LPAD wyrównać rzeczy. Wydaje się, że pozwala to dość dobrze na sortowanie ciągów Alfa, jak również ponumerowanych.

Moja ORDER BY klauzula wygląda następująco:

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

Oczywiście nie pomaga to w oryginalnym problemie, który nie był tak jednolity - ale wyobrażam sobie, że prawdopodobnie zadziała to w przypadku wielu innych powiązanych problemów, więc umieszczanie go tam.

1. Dodaj klucz sortowania (Rank) w tabeli. ORDER BY rank

2. Użyj kolumny "Data Wydania". ORDER BY release_date

3. Podczas ekstrakcji danych z SQL, upewnij się, że obiekt wykonuje sortowanie, na przykład, jeśli rozpakowujesz do zestawu, uczyń go drzewem i spraw, aby twój model danych zaimplementował porównywalny i wdrożył algorytm sortowania naturalnego (sortowanie wstawiania wystarczy, jeśli używasz języka bez kolekcji), ponieważ będziesz czytać wiersze z SQL jeden po drugim, gdy utworzysz swój własny zestaw danych. model i wstaw go do kolekcji)

Odnośnie najlepszej odpowiedzi Richarda Totha https://stackoverflow.com/a/12257917/4052357

Uważaj na zakodowane w UTF8 ciągi znaków, które zawierają 2byte (lub więcej) znaków i cyfr, np.

12 南新宿

Użycie funkcji MySQL LENGTH() w udf_NaturalSortFormat zwróci długość bajtów łańcucha i będzie niepoprawne, zamiast tego użyj CHAR_LENGTH(), która zwróci prawidłową długość znaków.

W moim przypadku użycie LENGTH() spowodowało, że zapytania nigdy się nie skończyły i spowodowało 100% wykorzystanie procesora dla 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. dodałbym to jako komentarz do oryginału, ale nie mam wystarczającej reputacji (jeszcze)

Inną opcją jest sortowanie w pamięci po pobraniu danych z mysql. Chociaż nie będzie to najlepsza opcja z punktu widzenia wydajności, jeśli nie sortujesz dużych list, powinieneś być w porządku.

Jeśli spojrzysz na post Jeffa, znajdziesz mnóstwo algorytmów do tego, z jakim językiem możesz pracować. http://www.codinghorror.com/blog/archives/001018.html

Jeśli nie chcesz odkrywać koła na nowo lub masz ból głowy z dużą ilością kodu, który nie działa, po prostu użyj Drupal Natural Sort ... Po prostu uruchom SQL, który jest zipped (MySQL lub Postgre), i to wszystko. Przy składaniu zapytania, po prostu zamów za pomocą:

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

Dodaj pole dla "sort key", które ma wszystkie ciągi cyfr zerowo wyściełane do ustalonej długości, a następnie posortuj na tym polu.

Jeśli możesz mieć długie ciągi cyfr, inną metodą jest dodanie liczby cyfr (o stałej szerokości, wyściełane zero) do każdego ciągu cyfr. Na przykład, jeśli w wierszu nie będzie więcej niż 99 cyfr, to dla "Super Blast 10 Ultra" kluczem sortowania będzie "Super Blast 0210 Ultra".

Można również utworzyć w dynamiczny sposób "sort column":

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

W ten sposób można tworzyć grupy do sortowania.

W moim zapytaniu, chciałem ' - ' przed wszystkim, potem liczby, potem tekst. Co może skutkować czymś w rodzaju:

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

W ten sposób nie musisz utrzymywać kolumny sortowania w prawidłowej kolejności podczas dodawania danych. Możesz również zmienić kolejność sortowania w zależności od tego, czego potrzebujesz.

Aby zamówić:
0
1
2
10
23
101
205
1000
a
aac
b
casdsadsa
css

Użyj tego zapytania:

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

Próbowałem kilku rozwiązań, ale w rzeczywistości jest to bardzo proste:

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
...
*/

Jeśli używasz PHP, możesz zrobić sortowanie naturalne w 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]; 
}

Mam nadzieję, że MySQL zaimplementuje sortowanie naturalne w przyszłej wersji, ale feature request (#1588) jest otwarty od 2003 roku, więc nie wstrzymałbym oddechu.

Uproszczona wersja nie-udf najlepszej odpowiedzi @plaix / Richard Toth / Luke Hoggett, która działa tylko dla pierwszej liczby całkowitej w polu, to

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

Istnieje również natsort . Ma być częścią wtyczki drupal, ale działa dobrze samodzielnie.

Wiem, że ten temat jest starożytny, ale chyba znalazłem na to sposób:

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

Złom, który posortował nieprawidłowo następujący zestaw (jest bezużyteczny 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