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

Думаю, поэтому многие вещи отсортированы по дате выпуска.

Решением может быть создание другого столбца в вашей таблице для «SortKey». Это может быть очищенная версия заголовка, соответствующая созданному вами шаблону для удобной сортировки, или счетчику.

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

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

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

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

Такое решение можно было бы использовать для приведенного выше примера Final Fantasy, но оно не является особенно гибким и вряд ли будет полностью распространяться на набор данных, включая, скажем, «Warhammer 40,000» и «Джеймс Бонд 007», я боюсь .

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

Если вы посмотрите пост Джеффа, вы можете найти множество алгоритмов для любого языка, с которым вы, возможно, работаете. Сортировка для людей: естественный порядок сортировки

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

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

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

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

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

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

SELECT names FROM your_table ORDER BY games + 0 ASC

Имеет ли естественный вид, когда числа впереди, может работать и для середины.

Если вы используете 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 года, поэтому я бы не стал задерживать дыхание.

Я написал эту функцию для 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

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

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

Итак, хотя я знаю, что вы нашли удовлетворительный ответ, я какое-то время боролся с этой проблемой, и ранее мы определили, что это не может быть сделано достаточно хорошо в 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')

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

Также есть нацорт. Он предназначен быть частью плагин drupal, но отлично работает автономно.

Та же функция, что и @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, ".")

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

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

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

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

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

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

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

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

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

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. Я бы добавил это как комментарий к оригиналу, но у меня недостаточно репутации (пока)

Упрощенная не-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

Заказать:
0
1
2
10
23
101
205
1000
a
aac
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
...
*/

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

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

Многие другие ответы, которые я вижу здесь (и в повторяющихся вопросах), в основном работают только для данных, специально отформатированных очень, например. строка, целиком состоящая из числа или имеющая буквенный префикс фиксированной длины. В общем случае это не сработает.

Верно, что на самом деле нет никакого способа реализовать 100% общую nat-сортировку в MySQL, потому что для этого вам нужен В самом деле, модифицированный функция сравнения, который переключается между лексикографической сортировкой строк и числовой сортировкой, если / когда он встречает количество. Такой код может реализовать любой алгоритм, который вы можете пожелать, для распознавания и сравнения числовых частей в двух строках. К сожалению, функция сравнения в MySQL является внутренней по отношению к его коду и не может быть изменена пользователем.

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

Для простых целых чисел до некоторого максимального количества цифр очевидным решением будет просто заполнить их нулями слева, чтобы все они были фиксированной ширины. Это подход, используемый плагином Drupal и решениями @plalx / @RichardToth. (У @Christian есть другое и гораздо более сложное решение, но оно не дает никаких преимуществ, которые я вижу).

Как указывает @tye, вы можете улучшить это, добавив к каждому числу фиксированную длину цифры, а не просто заполняя его слева. Тем не менее, вы можете улучшить гораздо больше, даже с учетом ограничений, по сути, неудобного взлома. Тем не менее, похоже, что готовых решений нет!

Например, как насчет:

• Знаки плюс и минус? +10 против 10 против -10
• Десятичные? 8,2, 8,5, 1,006, 0,75
• Ведущие нули? 020, 030, 00000922
• Разделители тысяч? «1001 далматинец» vs «1001 далматинец»
• Номера версий? MariaDB v10.3.18 против MariaDB v10.3.3
• Очень длинные числа? 103 768 276 592 092 364 859 236 487 687 870 234 598,55

Расширяя метод @tye, я создал довольно компактную хранимую функцию NatSortKey (), которая преобразует произвольную строку в ключ nat-sort, обрабатывает все вышеперечисленные случаи, достаточно эффективна и сохраняет общую сортировку. порядок (никакие две разные строки не имеют одинаковых ключей сортировки). Второй параметр может использоваться для ограничения количества чисел, обрабатываемых в каждой строке (например, до первых 10 чисел, скажем), что может быть использовано для обеспечения соответствия выходных данных заданной длине.

ПРИМЕЧАНИЕ. Строку ключа сортировки, сгенерированную с заданным значением этого 2-го параметра, следует сортировать только по сравнению с другими строками, сгенерированными со значением такой же для параметра, иначе они могут сортироваться некорректно!

Вы можете использовать его непосредственно при заказе, например

SELECT myString FROM myTable ORDER BY NatSortKey(myString,0);  ### 0 means process all numbers - resulting sort key might be quite long for certain inputs

Но для эффективной сортировки больших таблиц лучше предварительно сохранить ключ сортировки в другом столбце (возможно, с индексом на нем):

INSERT INTO myTable (myString,myStringNSK) VALUES (@theStringValue,NatSortKey(@theStringValue,10)), ...
...
SELECT myString FROM myTable ORDER BY myStringNSK;

[В идеале это должно происходить автоматически, создав ключевой столбец как вычисляемый сохраненный столбец, используя что-то вроде:

CREATE TABLE myTable (
...
myString varchar(100),
myStringNSK varchar(150) AS (NatSortKey(myString,10)) STORED,
...
KEY (myStringNSK),
...);

Но пока ни MySQL, ни MariaDB не допускают хранимых функций в вычисляемых столбцах, так что, к сожалению, ты еще не можешь это сделать.]

Моя функция влияет только на сортировку чисел. Если вы хотите выполнить другие действия по нормализации сортировки, такие как удаление всех знаков препинания или обрезка пробелов с каждого конца, или замена последовательностей с несколькими пробелами на отдельные пробелы, вы можете либо расширить функцию, либо это можно сделать до или после NatSortKey(). применяется к вашим данным. (Я бы рекомендовал использовать для этой цели REGEXP_REPLACE()).

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

Его можно было бы улучшить другими способами; например, в настоящее время он сортирует отрицательные числа по абсолютному значению, поэтому -1 стоит перед -2, а не наоборот. Также нет способа указать порядок сортировки DESC для чисел при сохранении лексикографической сортировки ASC для текста. Обе эти проблемы можно исправить, приложив немного больше усилий; Я обновлю код, если / когда у меня будет время.

Есть много других деталей, о которых нужно знать - включая некоторые критические зависимости от chaset и сопоставления, которые вы используете, - но я поместил их все в блок комментариев в коде SQL. Пожалуйста, внимательно прочтите это, прежде чем использовать эту функцию самостоятельно!

Итак, вот код. Если вы обнаружите ошибку или у вас есть улучшение, о котором я не упоминал, сообщите мне об этом в комментариях!

delimiter $$
CREATE DEFINER=CURRENT_USER FUNCTION NatSortKey (s varchar(100), n int) RETURNS varchar(350) DETERMINISTIC
BEGIN
/****
  Converts numbers in the input string s into a format such that sorting results in a nat-sort.
  Numbers of up to 359 digits (before the decimal point, if one is present) are supported.  Sort results are undefined if the input string contains numbers longer than this.
  For n>0, only the first n numbers in the input string will be converted for nat-sort (so strings that differ only after the first n numbers will not nat-sort amongst themselves).
  Total sort-ordering is preserved, i.e. if s1!=s2, then NatSortKey(s1,n)!=NatSortKey(s2,n), for any given n.
  Numbers may contain ',' as a thousands separator, and '.' as a decimal point.  To reverse these (as appropriate for some European locales), the code would require modification.
  Numbers preceded by '+' sort with numbers not preceded with either a '+' or '-' sign.
  Negative numbers (preceded with '-') sort before positive numbers, but are sorted in order of ascending absolute value (so -7 sorts BEFORE -1001).
  Numbers with leading zeros sort after the same number with no (or fewer) leading zeros.
  Decimal-part-only numbers (like .75) are recognised, provided the decimal point is not immediately preceded by either another '.', or by a letter-type character.
  Numbers with thousand separators sort after the same number without them.
  Thousand separators are only recognised in numbers with no leading zeros that don't immediately follow a ',', and when they format the number correctly.
  (When not recognised as a thousand separator, a ',' will instead be treated as separating two distinct numbers).
  Version-number-like sequences consisting of 3 or more numbers separated by '.' are treated as distinct entities, and each component number will be nat-sorted.
  The entire entity will sort after any number beginning with the first component (so e.g. 10.2.1 sorts after both 10 and 10.995, but before 11)
  Note that The first number component in an entity like this is also permitted to contain thousand separators.

  To achieve this, numbers within the input string are prefixed and suffixed according to the following format:
  - The number is prefixed by a 2-digit base-36 number representing its length, excluding leading zeros.  If there is a decimal point, this length only includes the integer part of the number.
  - A 3-character suffix is appended after the number (after the decimals if present).
    - The first character is a space, or a '+' sign if the number was preceded by '+'.  Any preceding '+' sign is also removed from the front of the number.
    - This is followed by a 2-digit base-36 number that encodes the number of leading zeros and whether the number was expressed in comma-separated form (e.g. 1,000,000.25 vs 1000000.25)
    - The value of this 2-digit number is: (number of leading zeros)*2 + (1 if comma-separated, 0 otherwise)
  - For version number sequences, each component number has the prefix in front of it, and the separating dots are removed.
    Then there is a single suffix that consists of a ' ' or '+' character, followed by a pair base-36 digits for each number component in the sequence.

  e.g. here is how some simple sample strings get converted:
  'Foo055' --> 'Foo0255 02'
  'Absolute zero is around -273 centigrade' --> 'Absolute zero is around -03273 00 centigrade'
  'The ,000,000 prize' --> 'The 1000000 01 prize'
  '+99.74 degrees' --> '0299.74+00 degrees'
  'I have 0 apples' --> 'I have 00 02 apples'
  '.5 is the same value as 0000.5000' --> '00.5 00 is the same value as 00.5000 08'
  'MariaDB v10.3.0018' --> 'MariaDB v02100130218 000004'

  The restriction to numbers of up to 359 digits comes from the fact that the first character of the base-36 prefix MUST be a decimal digit, and so the highest permitted prefix value is '9Z' or 359 decimal.
  The code could be modified to handle longer numbers by increasing the size of (both) the prefix and suffix.
  A higher base could also be used (by replacing CONV() with a custom function), provided that the collation you are using sorts the "digits" of the base in the correct order, starting with 0123456789.
  However, while the maximum number length may be increased this way, note that the technique this function uses is NOT applicable where strings may contain numbers of unlimited length.

  The function definition does not specify the charset or collation to be used for string-type parameters or variables:  The default database charset & collation at the time the function is defined will be used.
  This is to make the function code more portable.  However, there are some important restrictions:

  - Collation is important here only when comparing (or storing) the output value from this function, but it MUST order the characters " +0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" in that order for the natural sort to work.
    This is true for most collations, but not all of them, e.g. in Lithuanian 'Y' comes before 'J' (according to Wikipedia).
    To adapt the function to work with such collations, replace CONV() in the function code with a custom function that emits "digits" above 9 that are characters ordered according to the collation in use.

  - For efficiency, the function code uses LENGTH() rather than CHAR_LENGTH() to measure the length of strings that consist only of digits 0-9, '.', and ',' characters.
    This works for any single-byte charset, as well as any charset that maps standard ASCII characters to single bytes (such as utf8 or utf8mb4).
    If using a charset that maps these characters to multiple bytes (such as, e.g. utf16 or utf32), you MUST replace all instances of LENGTH() in the function definition with CHAR_LENGTH()

  Length of the output:

  Each number converted adds 5 characters (2 prefix + 3 suffix) to the length of the string. n is the maximum count of numbers to convert;
  This parameter is provided as a means to limit the maximum output length (to input length + 5*n).
  If you do not require the total-ordering property, you could edit the code to use suffixes of 1 character (space or plus) only; this would reduce the maximum output length for any given n.
  Since a string of length L has at most ((L+1) DIV 2) individual numbers in it (every 2nd character a digit), for n<=0 the maximum output length is (inputlength + 5*((inputlength+1) DIV 2))
  So for the current input length of 100, the maximum output length is 350.
  If changing the input length, the output length must be modified according to the above formula.  The DECLARE statements for x,y,r, and suf must also be modified, as the code comments indicate.
****/
  DECLARE x,y varchar(100);            # need to be same length as input s
  DECLARE r varchar(350) DEFAULT '';   # return value:  needs to be same length as return type
  DECLARE suf varchar(101);   # suffix for a number or version string. Must be (((inputlength+1) DIV 2)*2 + 1) chars to support version strings (e.g. '1.2.33.5'), though it's usually just 3 chars. (Max version string e.g. 1.2. ... .5 has ((length of input + 1) DIV 2) numeric components)
  DECLARE i,j,k int UNSIGNED;
  IF n<=0 THEN SET n := -1; END IF;   # n<=0 means "process all numbers"
  LOOP
    SET i := REGEXP_INSTR(s,'\d');   # find position of next digit
    IF i=0 OR n=0 THEN RETURN CONCAT(r,s); END IF;   # no more numbers to process -> we're done
    SET n := n-1, suf := ' ';
    IF i>1 THEN
      IF SUBSTRING(s,i-1,1)='.' AND (i=2 OR SUBSTRING(s,i-2,1) RLIKE '[^.\p{L}\p{N}\p{M}\x{608}\x{200C}\x{200D}\x{2100}-\x{214F}\x{24B6}-\x{24E9}\x{1F130}-\x{1F149}\x{1F150}-\x{1F169}\x{1F170}-\x{1F189}]') AND (SUBSTRING(s,i) NOT RLIKE '^\d++\.\d') THEN SET i:=i-1; END IF;   # Allow decimal number (but not version string) to begin with a '.', provided preceding char is neither another '.', nor a member of the unicode character classes: "Alphabetic", "Letter", "Block=Letterlike Symbols" "Number", "Mark", "Join_Control"
      IF i>1 AND SUBSTRING(s,i-1,1)='+' THEN SET suf := '+', j := i-1; ELSE SET j := i; END IF;   # move any preceding '+' into the suffix, so equal numbers with and without preceding "+" signs sort together
      SET r := CONCAT(r,SUBSTRING(s,1,j-1)); SET s = SUBSTRING(s,i);   # add everything before the number to r and strip it from the start of s; preceding '+' is dropped (not included in either r or s)
    END IF;
    SET x := REGEXP_SUBSTR(s,IF(SUBSTRING(s,1,1) IN ('0','.') OR (SUBSTRING(r,-1)=',' AND suf=' '),'^\d*+(?:\.\d++)*','^(?:[1-9]\d{0,2}(?:,\d{3}(?!\d))++|\d++)(?:\.\d++)*+'));   # capture the number + following decimals (including multiple consecutive '.<digits>' sequences)
    SET s := SUBSTRING(s,LENGTH(x)+1);   # NOTE: LENGTH() can be safely used instead of CHAR_LENGTH() here & below PROVIDED we're using a charset that represents digits, ',' and '.' characters using single bytes (e.g. latin1, utf8)
    SET i := INSTR(x,'.');
    IF i=0 THEN SET y := ''; ELSE SET y := SUBSTRING(x,i); SET x := SUBSTRING(x,1,i-1); END IF;   # move any following decimals into y
    SET i := LENGTH(x);
    SET x := REPLACE(x,',','');
    SET j := LENGTH(x);
    SET x := TRIM(LEADING '0' FROM x);   # strip leading zeros
    SET k := LENGTH(x);
    SET suf := CONCAT(suf,LPAD(CONV(LEAST((j-k)*2,1294) + IF(i=j,0,1),10,36),2,'0'));   # (j-k)*2 + IF(i=j,0,1) = (count of leading zeros)*2 + (1 if there are thousands-separators, 0 otherwise)  Note the first term is bounded to <= base-36 'ZY' as it must fit within 2 characters
    SET i := LOCATE('.',y,2);
    IF i=0 THEN
      SET r := CONCAT(r,LPAD(CONV(LEAST(k,359),10,36),2,'0'),x,y,suf);   # k = count of digits in number, bounded to be <= '9Z' base-36
    ELSE   # encode a version number (like 3.12.707, etc)
      SET r := CONCAT(r,LPAD(CONV(LEAST(k,359),10,36),2,'0'),x);   # k = count of digits in number, bounded to be <= '9Z' base-36
      WHILE LENGTH(y)>0 AND n!=0 DO
        IF i=0 THEN SET x := SUBSTRING(y,2); SET y := ''; ELSE SET x := SUBSTRING(y,2,i-2); SET y := SUBSTRING(y,i); SET i := LOCATE('.',y,2); END IF;
        SET j := LENGTH(x);
        SET x := TRIM(LEADING '0' FROM x);   # strip leading zeros
        SET k := LENGTH(x);
        SET r := CONCAT(r,LPAD(CONV(LEAST(k,359),10,36),2,'0'),x);   # k = count of digits in number, bounded to be <= '9Z' base-36
        SET suf := CONCAT(suf,LPAD(CONV(LEAST((j-k)*2,1294),10,36),2,'0'));   # (j-k)*2 = (count of leading zeros)*2, bounded to fit within 2 base-36 digits
        SET n := n-1;
      END WHILE;
      SET r := CONCAT(r,y,suf);
    END IF;
  END LOOP;
END
$$
delimiter ;

Вот простой вариант, если в названиях есть только номер версии:

ORDER BY CAST(REGEXP_REPLACE(title, "[a-zA-Z]+", "") AS INT)';

В противном случае вы можете использовать простой SQL, если используете шаблон (этот шаблон использует # перед версией):

create table titles(title);

insert into titles (title) values 
('Final Fantasy'),
('Final Fantasy #03'),
('Final Fantasy #11'),
('Final Fantasy #10'),
('Final Fantasy #2'),
('Bond 007 ##2'),
('Final Fantasy #01'),
('Bond 007'),
('Final Fantasy #11}');

select REGEXP_REPLACE(title, "#([0-9]+)", "\1") as title from titles
ORDER BY REGEXP_REPLACE(title, "#[0-9]+", ""),
CAST(REGEXP_REPLACE(title, ".*#([0-9]+).*", "\1") AS INT);     
+-------------------+
| title             |
+-------------------+
| Bond 007          |
| Bond 007 #2       |
| Final Fantasy     |
| Final Fantasy 01  |
| Final Fantasy 2   |
| Final Fantasy 03  |
| Final Fantasy 10  |
| Final Fantasy 11  |
| Final Fantasy 11} |
+-------------------+
8 rows in set, 2 warnings (0.001 sec)

При необходимости вы можете использовать другие шаблоны. Например, если у вас есть фильм «Я №1» и «Я №1, часть 2», то, возможно, оберните версию, например "Последняя фантазия {11}"