Естественная сортировка в MySQL с помощью условия ORDER BY

В этой статье мы расскажем о различных методах естественной сортировки MySQL с помощью условия ORDER BY. Давайте начнем урок с простых данных.

Предположим, у нас есть таблица items, которая содержит два столбца: id и item_no. Чтобы создать элементы таблицы мы используем оператор CREATE TABLE:

CREATE TABLE if not exists items(
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_no VARCHAR(255) NOT NULL
);

Мы используем оператор INSERT, чтобы добавить некоторые данные в таблицу items:

INSERT INTO items(item_no)
VALUES ('1'),
       ('1C'),
       ('10Z'),
       ('2A'),
       ('2'),
       ('3C'),
       ('20D');

Когда мы отбираем данные и отсортируем их по признаку item_no, мы получим следующий результат:

SELECT item_no
FROM items
ORDER BY item_no;

Это не то, что мы ожидали. Мы хотели увидеть результат следующим образом:

Это называется естественной сортировкой. К сожалению, MySQL не предоставляет пользователям встроенный сценарий или функцию естественной сортировки. Оператор ORDER BY сортирует строки в линейном порядке, т.е. один символ за раз, начиная с первого символа.

Чтобы решить эту проблему, в первую очередь мы разобьем столбец item_no на два столбца: prefix и suffix.

Столбец prefix будет содержать цифровую часть item_no, а столбец suffix - буквенную. Это позволит нам отсортировать данные, используя эти два столбца, с помощью следующего запроса:

SELECT CONCAT(prefix,suffix)
FROM items
ORDER BY prefix, suffix

Запросив сортировку данных сначала по числовому признаку, а затем по алфавиту, мы получаем желаемый результат.

Недостатком этого решения является то, что, прежде чем добавлять или обновлять данные, мы должны делить item_no на две части. Кроме того, мы должны объединить два столбца в один, когда мы выбираем данные.

Если в item_no содержатся данные стандартного формата, вы можете использовать следующий запрос, чтобы выполнить естественную сортировку без изменения структуры таблицы.

SELECT item_no
FROM items
ORDER BY CAST(item_no AS  UNSIGNED), item_no;

В этом запросе мы сначала конвертируем данные item_no в целое число без дробей с помощью функции CAST. А также используем условие ORDER BY , чтобы отсортировать строки сначала по числовому признаку, а затем по алфавиту.

Давайте рассмотрим стандартный набор данных, с которыми нам часто приходится иметь дело:

TRUNCATE TABLE items;
 
INSERT INTO items(item_no)
VALUES('A-1'),
      ('A-2'),
      ('A-3'),
      ('A-4'),
      ('A-5'),
      ('A-10'),
      ('A-11'),
      ('A-20'),
      ('A-30');

Мы хотим, чтобы результат сортировки выглядел следующим образом:

Чтобы этого достичь, мы можем использовать функцию LENGTH. Обратите внимание, что функция LENGTH возвращает длину строки.

Идея состоит в том, чтобы отсортировать данные item_no по длине, а затем по значениям граф при следующем запросе:

SELECT item_no
FROM items
ORDER BY LENGTH(item_no),
         item_no;

Как видите, данные отсортированы естественно.

В случае если в вашей ситуации все выше приведенные решения неприменимы, вам придется выполнить естественную сортировку с помощью отдельных приложений.

Некоторые языки поддерживают функцию естественной сортировки, например, PHP поддерживает функцию natsort() function, которая сортирует массив с использованием алгоритмов естественной сортировки.