Сортування в MySQL: кілька рідко використовуваних можливостей
- Сортування з урахуванням типу даних
- Сортування тексту з урахуванням регістру
- Сортування за фрагментом рядка
- Сортування записів по заданому списку значень
- Сортування рядків по їх довжині
- висновок
Про сортування даних за допомогою SQL запитів, думаю, знають всі web розробники.
Параметри задавати теж нескладно. Просто перераховуєте через кому стовпці за якими потрібно виконати сортування і вказуєте її напрямок (по зростанню (ASC) чи спадний (DESC)).
Тобто запит, виконує сортування може виглядати приблизно так.
SELECT * FROM articles ORDER BY a_title ASC, a_date DESCВ цьому випадку записи з таблиці articles будуть відсортовані в порядку зростання по полю a_title, а для записів у яких збігаються значення в поле a_title - по полю a_date в порядку спадання.
Саме в такій формі використовуються можливості сортування в більшості випадків. Але зустрічаються ситуації, в яких потрібно використовувати більш складні правила.
Сортування з урахуванням типу даних
Уявіть, що у вас є дві таблиці. Перша містить якісь записи (статті, новини, товари і т.п.), а друга - метадані для цих записів. Метадані можуть містити будь-яку інформацію, наприклад, рейтинг новини або колір товара.
При цьому таблиці матимуть приблизно таку структуру.
Подібні рішення використовуються в багатьох CMS, тому що автори не знають які саме метадані буде використовувати розробник сайту і надають йому можливість створювати їх в необмеженій кількості.
Але що станеться якщо ми спробуємо виконати сортування по метаданих?
Таку сортування можна виконати за допомогою наступного запиту
SELECT * FROM articles AS a LEFT JOIN metadata AS m ON a.a_id = m.m_article_id WHERE m.m_name = "color"Цей запит виведе всі записи з таблиці articles для яких створено мета-поле color.
Очевидно, що тип поля m_value повинен бути текстовим, тому що заздалегідь невідомо що буде зберігатися в метаданих.
При цьому числові дані будуть відсортовані неправильно. Справа в тому, що ORDER BY порівнює значення з урахуванням типу поля і, наприклад, при сортуванні по зростанню ви отримаєте наступний ряд значень: «1», «10», «2», «3» і т.д.
Тобто при виконанні такого запиту потрібно вказати, що поле m_value необхідно вважати числовим. Робиться це в такий спосіб.
SELECT * FROM metadata ORDER BY (m_value + 0)Зворотна операція (сортування числового поля за правилами текстового) записується трохи складніше.
SELECT left (a_id, 20) AS id_str FROM articles ORDER BY id_strФункція left повертає рядок, що містить перші N символів з рядка, зазначеної в першому параметрі. Кількість символів (N) задається в другому параметрі. В даному випадку будуть вибрані перші 20 символів (досить щоб перетворити 8-байтное ціле число в рядок). Тобто движок MySQL виконає сортування по рядку, отриманої з значення числового поля.
Хочу попередити, що не дивлячись на те, що дані методи можуть бути зручні в ряді ситуацій, їх використання призводить до зниження швидкості виконання SQL запитів. Тому зловживати ними не варто. З іншого боку, сортування за допомогою PHP (Або будь-якого іншого мови) також займе якийсь час.
Сортування тексту з урахуванням регістру
Для текстових полів сортування виконується без урахування регістру. У більшості випадків це правильно, тому що в таблиці символів прописна «А» йде після малої «я». Тобто відсортовані з урахуванням регістра рядка будуть розташовані в наступному порядку.
1. «Мова запитів»
2. «Мова програмування»
3. «Мова Запитів»
Але якщо все-таки сортування потрібно виконати з урахуванням регістра, просто додайте оператор BINARY перед ім'ям поля.
SELECT * FROM articles ORDER BY BINARY a_titleСортування за фрагментом рядка
Якщо вам доводиться використовувати цей прийом, то, швидше за все, ваша база даних не відповідає перовой нормальній формі. Тому, подумайте, може краще переглянути її структуру?
Але, в будь-якому випадку, ви можете використовувати функцію SUBSTRING_INDEX для вибору підрядка і подальшого сортування. наприклад,
SELECT SUBSTRING_INDEX (a_title, '', -1) AS at FROM wp_5_posts ORDER BY atСортування записів по заданому списку значень
Уявіть, що у вас є записи, які якимось чином пов'язані з порою року. І ви зберігаєте цю інформацію в одному з стовпців таблиці.
Очевидно, що, використовуючи стандартний варіант сортування (за алфавітом), розташувати сезони порядку «весна», «літо», «осінь», «зима» не вийде. Природно, можна кожному пори року присвоїти свій код, але є й інший варіант рішення - використовувати функцію FIELD .
SELECT * FROM articles ORDER BY FIELD (a_season, "весна", "літо", "осінь", "зима")Ця функція шукає значення, вказане в першому параметрі, серед значень, перерахованих в інших параметрах, і повертає його порядковий номер. При виконанні запиту в перший параметр функції FIELD будуть передаватися значення з поля a_season і, таким чином, записи будуть відсортовані в заданому нами порядку.
Сортування рядків по їх довжині
Ця можливість використовується досить рідко, тому що зазвичай довжина рядка смислового навантаження не несе. Проте, в деяких випадках може стати в нагоді.
SELECT * FROM articles ORDER BY CHAR_LENGTH (a_title)Як бачите, «фокус» полягає у використанні функції CHAR_LENGTH, яка визначає кількість символів в рядку.
висновок
Думаю, дивлячись на останні кілька рецептів, ви розумієте, що подібним чином можна використовувати практично всі функції MySQL. Мені буде цікаво, якщо ви поділитеся прикладами зі своєї практики 😉
Цікаве в Інтернеті
замовлення весільних фотоальбомів з доставкою по РФ і СНД
Але що станеться якщо ми спробуємо виконати сортування по метаданих?Тому, подумайте, може краще переглянути її структуру?