Сортировка в MySQL: несколько редко используемых возможностей

Владимир | | MySQL, Web разработка.

mysql order by

О сортировке данных с помощью SQL запросов, думаю, знают все web разработчики.

Достаточно указать в запросе предложение ORDER BY с нужными параметрами и вы получите желаемый результат.

Параметры задавать тоже несложно. Просто перечисляете через запятую столбцы по которым нужно выполнить сортировку и указываете её направление (по возрастанию (ASC) или по убыванию (DESC)).

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

SELECT * FROM articles ORDER BY a_title ASC, a_date DESC

В этом случае записи из таблицы articles будут отсортированы в возрастающем порядке по полю a_title, а для записей у которых совпадают значения в поле a_title – по полю a_date в убывающем порядке.

Именно в такой форме используются возможности сортировки в большинстве случаев. Но встречаются ситуации, в которых нужно использовать более сложные правила.

Сортировка с учетом типа данных

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

При этом таблицы будут иметь приблизительно следующую структуру.

db_structure

Подобные решения используются во многих 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. Мне будет интересно, если вы поделитесь примерами из своей практики 😉

Интересное в Интернете

Заказ свадебных фотоальбомов с доставкой по РФ и СНГ

  • Только следует учитывать, что если к значениям применяются какие-то операции, то индексы не используются.

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

      • Безусловно, сделать малоэффективную выборку из БД все же лучше, чем выбирать все и обрабатывать в скрипте.
        Конкретнее по примерам.
        Необходимость «сортировки с учетом типа» — плохо спроектированная база.
        С учетом регистра — что тут делать, мне непонятно. Можно, конечно, хранить копию поля с приведенным регистром, но это тоже нехорошо…
        «По фрагменту строки» — как и написано, руки оторвать за необходимость такого.
        «По списку» — тут же так и просится enum.
        «По длине» — опять же, не очень понятно. Разве что хранить еще и длину отдельно?..

        В общем случае, недостаток применения функций к полям в условиях выборки/сортировки — то, что движку БД придется проходить по всей таблице (или по всем записям, выбранным другими условиями).
        То есть если, скажем, для запроса WHERE `a` = 1+1 можно использовать индекс по `a`, и понадобится лишь проход по индексу (~логарифмическое от размера таблицы время), то WHERE `a` — 1 = 1 требует прохода по всей таблице (~линейное от размера таблицы время).
        Суть в том, что если таблица относительно небольшая (в пределах нескольких сотен записей), то и пройтись по всей таблице для запроса в принципе не очень страшно. А вот если таблица большая…

        • >> Необходимость «сортировки с учетом типа» — плохо спроектированная база.

          Не всегда. Заранее может быть не известно какие данные будут храниться в БД. Кроме того, в той же таблице метаданных данные с одним ключом могут быть текстовыми, с другим — числовыми.

          >> «По фрагменту строки» — как и написано, руки оторвать за необходимость такого.

          Бывают ситуации когда нужно дорабатывать систему, написанную N лет назад. Требования новые, а структура БД — старая и изменить её очень проблематично, т.к. придется переписать кучу кода.

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

        • >> «По списку» — тут же так и просится enum.
          ну правильно, имеем поле типа ENUM(«весна», «лето», «осень», «зима»)
          если просто сделать сортировку, то сначала будет весна, потом зима и т.д.
          Выходит все-равно нужно делать через FIELD()

  • Если надо результат сформировать в зависимости от порядка следования параметров в WHERE IN (…) при использовании GROUP_CONCAT:

    SELECT GROUP_CONCAT(id ORDER BY FIELD(slug,'odin','tree','six','five') SEPARATOR '/' ) as path
    FROM `catalog_categories`
    WHERE slug IN ('odin','tree','six','five');

    Тут ( http://glebov-gin.blogspot.com/2011/03/mysql-groupconcat-where-in.html ) немного подробнее (чтобы не писать весь свой пост в коммент).

  • В постгре можно индексы так строить. Типа если часто гоняешь запросы вроде SELECT * FROM articles ORDER BY CHAR_LENGTH(a_title) то можно построить индекс по CHAR_LENGTH(a_title) и бегать будет гораздо шустрее

    • Одна из вещей, за которые мне нравится Postgre.

  • Очень интересная статья!
    Скажите, а в какой программе Вы структуру таблицы сделали?

  • BTX

    Спасибо.
    Была задача учитывать поле из таблицы картинок, выводить позиции вначале те, что с изображениями, и потом уже по номеру артикула, но так как названия у картинок все разные, то сортировка относительно артикулов не производилась, так что ORDER BY (f.foto+0) DESC, p.code DESC очень мне помогло =)

  • Даша

    Спасибо большое, хорошая статья — я нашла то, что искала))

  • Ярослав

    Благодарю за статью!

  • Андрей

    Спасибо именно то что искал

  • Kolelan

    Спасибо большое! ORDER BY («string» + 0) Очень помогло. Это так же работает с фреймвоком YII с объектом CDbCriteria. Т.е. можно написать $criteria->order = «(condition +0)»;

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