Естественная сортировка в MySQL

вот быстрое решение:

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

только что нашел это:

SELECT names FROM your_table ORDER BY games + 0 ASC

делает естественную сортировку, когда числа находятся на фронте, может работать и для середины.

та же функция, что и у @plalx, но переписана на 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
;;

использование:

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

MySQL не разрешает такого рода "естественную сортировку", поэтому, похоже, лучший способ получить то, что вам нужно,-это разделить ваши данные, настроенные так, как вы описали выше (отдельное поле id и т. д.), Или в противном случае выполнить сортировку на основе элемента без заголовка, индексированного элемента в вашей БД (дата, вставленный идентификатор в БД и т. д.).

имея БД сделать сортировку для вас почти всегда будет быстрее, чем чтение больших массивов данных в языке программирования и сортировки там, поэтому, если у вас есть какой-либо контроль над всей схемой БД здесь, то посмотрите на добавление легко отсортированных полей, как описано выше, это сэкономит вам много хлопот и обслуживания в долгосрочной перспективе.

запросы на добавление "естественной сортировки" приходят время от времени на ошибки MySQL и форумы, и многие решения вращаются вокруг удаления определенных частей ваших данных и литья их для ORDER BY часть запроса, например,

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

такого рода решение может быть почти сделано для работы над вашим примером Final Fantasy выше, но не особенно гибко и вряд ли будет распространяться чисто на набор данных, включая, скажем, "Warhammer 40,000" и "James Bond 007", я боюсь.

Я написал эту функцию для MSSQL 2000 некоторое время назад:

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

Итак, хотя я знаю, что вы нашли удовлетворительный ответ, я некоторое время боролся с этой проблемой, и мы ранее определили, что это не может быть сделано достаточно хорошо в SQL, и нам придется использовать javascript в массиве JSON.

вот как я решил это просто с помощью SQL. Надеюсь, это полезно для других:

у меня были такие данные, как:

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

Я на самом деле не" бросал " вещи, хотя я полагаю, что это также может иметь работал.

Я сначала заменил части, которые были неизменными в данных, в данном случае "сцена", а затем сделал LPAD для выравнивания вещей. Это, кажется, позволяет довольно хорошо для Альфа-строк правильно сортировать, а также пронумерованные.

мой ORDER BY пункт выглядит так:

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

очевидно, что это не помогает с исходной проблемой, которая не была настолько однородной , но я думаю, что это, вероятно, будет работать для многих других связанных с этим проблем, поэтому потушите ее там.

1. добавить ключ сортировки (ранг) в таблице. ORDER BY rank

2. используйте столбец "Дата выпуска". ORDER BY release_date

3. при извлечении данных из SQL, сделайте ваш объект сделать сортировку, например, при извлечении в набор, сделать его набор деревьев, и сделать вашу модель данных реализовать сопоставимый и ввести естественный алгоритм сортировки здесь (вставка сортировки будет достаточно, если вы используете язык без коллекций) , как вы будете читать строки из SQL один за другим, как вы создаете свою модель и вставить его в коллекцию)

относительно лучшего ответа от Ричарда Тота https://stackoverflow.com/a/12257917/4052357

следите за кодированными строками UTF8, которые содержат 2byte (или более) символов и чисел, например

12 南新宿

использование MySQL LENGTH() на udf_NaturalSortFormat функция вернет длину байта строки и будет неправильной, вместо этого используйте CHAR_LENGTH() который вернет правильную длину символа.

в моем случае с помощью LENGTH() вызванные запросы никогда завершите и приведите к 100% использованию процессора для 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. Я бы добавил Это как комментарий к оригиналу, но у меня недостаточно репутации (пока)

другой вариант-сделать сортировку в памяти после извлечения данных из mysql. Хотя это не будет лучшим вариантом с точки зрения производительности, если вы не сортируете огромные списки, вы должны быть в порядке.

Если вы посмотрите на сообщение Джеффа, вы можете найти множество алгоритмов для любого языка, с которым вы можете работать. http://www.codinghorror.com/blog/archives/001018.html

Если вы не хотите изобретать велосипед или иметь головную боль с большим количеством кода, который не работает, просто используйте Drupal Natural Sort ... Просто запустите SQL, который поставляется zipped (MySQL или Postgre), и все. При создании запроса, просто заказать с помощью:

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

добавьте поле для "ключа сортировки", которое имеет все строки цифр с нулевым заполнением до фиксированной длины, а затем сортируйте по этому полю.

Если у вас могут быть длинные строки цифр, другой метод состоит в том, чтобы добавить количество цифр (фиксированная ширина, нулевая подкладка) к каждой строке цифр. Например, если у вас не будет более 99 цифр подряд, то для "Super Blast 10 Ultra" ключом сортировки будет "Super Blast 0210 Ultra".

вы также можете создать динамическим способом "столбец сортировки":

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

таким образом, вы можете создавать группы для сортировки.

в моем запросе, я хотел '-' перед всем, то цифры, то текст. Что может привести к чему-то вроде :

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

таким образом, вам не нужно поддерживать столбец сортировки в правильном порядке при добавлении данных. Вы также можете изменить порядок сортировки в зависимости от того, что вам нужно.

по порядку:
0
1
2
10
23
101
205
1000
а
ААК
b
casdsadsa
css

используйте этот запрос:

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

Я пробовал несколько решений, но на самом деле это очень просто:

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

Если вы используете PHP, вы можете сделать естественную сортировку в 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]; 
}

Я надеюсь, что MySQL будет реализовывать естественную сортировку в будущей версии, но запрос (#1588) открыт с 2003 года, поэтому я бы не задерживал дыхание.

упрощенная версия без udf лучшего ответа @plaix / Richard Toth/Luke Hoggett, которая работает только для первого целого числа в поле, является

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

есть natsort. Он предназначен, чтобы быть частью плагин для Drupal, но он отлично работает автономно.

Я знаю, что эта тема древняя, но я думаю, что я нашел способ сделать это:

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

лом, что, он отсортировал следующий набор неправильно (это бесполезно lol):

Final Fantasy 1 Final Fantasy 2 Final Fantasy 5 Final Fantasy 7 Последняя Фантазия 7: Пришествие Детей Final Fantasy 12 Final Fantasy 112 FF1 FF2