Природна сортування в mysql за допомогою умови order by, mysql

У цій статті ми розповімо про різні методи природної сортування MySQL за допомогою умови ORDER BY. Давайте почнемо урок з простих даних.

Припустимо, у нас є таблиця items. яка містить два стовпці: id і item_no. Щоб створити елементи таблиці ми використовуємо оператор CREATE TABLE:

Ми використовуємо оператор INSERT. щоб додати деякі дані в таблицю items:

Коли ми відбираємо дані і відсортуємо їх за ознакою item_no. ми отримаємо наступний результат:

Це не те, що ми очікували. Ми хотіли побачити результат наступним чином:

Це називається природною сортуванням. На жаль, MySQL не надає користувачам вбудований сценарій або функцію природної сортування. Оператор ORDER BY сортує рядки в лінійному порядку, тобто один символ за раз, починаючи з першого символу.

Щоб вирішити цю проблему, в першу чергу ми розіб'ємо стовпець item_no на два стовпці: prefix і suffix.

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

Запитавши сортування даних спочатку по числовому ознакою, а потім за алфавітом, ми отримуємо бажаний результат.

Недоліком цього рішення є те, що, перш ніж додавати або оновлювати дані, ми повинні ділити item_no на дві частини. Крім того, ми повинні об'єднати два стовпці в один, коли ми вибираємо дані.

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

У цьому запиті ми спочатку конвертуємо дані item_no в ціле число без дробів за допомогою функції CAST. А також використовуємо умова ORDER BY. щоб впорядкувати рядки спочатку по числовому ознакою, а потім за алфавітом.

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

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

Щоб цього досягти, ми можемо використовувати функцію LENGTH. Зверніть увагу, що функція LENGTH повертає довжину рядка.

Ідея полягає в тому, щоб впорядкувати дані item_no по довжині, а потім за значеннями граф при наступному запиті:

Як бачите, дані відсортовані природно.

У разі якщо у вашій ситуації все вище наведені рішення неприйнятні, вам доведеться виконати природну сортування за допомогою окремих додатків.

Деякі мови підтримують функцію природної сортування, наприклад, PHP підтримує функцію natsort () function. яка сортує масив з використанням алгоритмів природною сортування.

Переклад статті «MySQL Natural Sorting with ORDER BY clasuse» був підготовлений дружною командою проекту Сайтобудування від А до Я.