Секреты сортировки в MySQL

Просмотров: 8552Комментарии: 2
ПрограммированиеPHP-MySQL
Секреты сортировки в MySQL

Многие из нас знают как делается сортировка в MySQL. Достаточно в конце запроса добавить ORDER BY с нужными параметрами. В качестве параметров пишутся имена столбцов через запятую, затем указывается ее направление по возрастанию (ASC) или по убыванию (DESC). Но не многие знают некоторые секреты этой сортировки.

Пример такого запроса:

SELECT * FROM page ORDER BY title ASC, date DESC

в данном случает данные из таблицы page будут отсортированы по полю title в возрастающем порядке, при этом если значения совпадут, то эти записи будут отсортированы по полю date по убыванию.

Сортировка строк по значениям чисел

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

В MaxSite, да и в других CMS метаданные страницы хранятся в строковом виде. В нашем случае значение рейтинга хранится в метаданных.

Наш запрос выглядит так:

SELECT * FROM page 
LEFT JOIN meta ON meta.meta_id_obj = page.page_id 
WHERE (meta_key = 'pagerate_value' AND meta_value <> '')

Если выполнить простую сортировку, то результаты будут катастрофичными, так как '11' будет меньше '4', сравнение в строках идет по символьно.

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

SELECT * FROM meta ORDER BY (meta_value+0)

Также есть и обратная операция, но выглядит она сложнее:

SELECT LEFT(int_value, 20) AS str_value FROM page ORDER BY str_value

Функция left возвращает строку, содержащую первые 20 символов из строки, указанной в первом параметре.

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

Непосредственно в MaxSite CMS для выполнения такой сортировки, в массив $par перед запросом страниц, добавим следующее:

'order'=>'meta_value',
'order_asc'=>'desc',
'function_add_custom_sql' => 'ranking',

затем ниже опишем функцию ranking

function ranking() {
   $CI = &get_instance();
   $CI->db->join('meta', 'meta.meta_id_obj = page.page_id');
   $where = "(meta_key = 'pagerate_value' AND meta_value <> '')";
   $CI->db->where($where);
}

Сортировка с учетом регистра символов

По умолчанию сортировка для строк выполняется без учета регистра. В повседневных задачах вроде бы и не нужна другая сортировка, так как заглавная бувка "А" идет после строчной "я".

Но если все таки такая необходимость понадобилась, то делается это добавлением оператора BINARY:

SELECT * FROM page ORDER BY BINARY title

Сортировка по длине строк

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

SELECT * FROM page ORDER BY CHAR_LENGTH(title)

Сортировка по заданному списку

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

Для этого используется функция FIELD:

SELECT * FROM page ORDER BY FIELD(season, "весна","лето","осень","зима")

Комментариев: 2 RSS

1 Дмитрий 13-09-2013 02:32

Спасибо за "ORDER BY FIELD"!

Перекопал всю документацию по MySQL, обшарил весь google, только здесь нашел то заветное, что так долго искал!

Огромное спасибо, выручили )

2 Леонид 29-02-2016 21:21

А мне пригодилась сортировка числовых значений)) Большое спасибо за полезный материал!

Оставьте комментарий!

grin LOL cheese smile wink smirk rolleyes confused surprised big surprise tongue laugh tongue rolleye tongue wink raspberry blank stare long face ohh grrr gulp oh oh downer red face sick shut eye hmmm mad angry zipper kiss shock cool smile cool smirk cool grin cool hmm cool mad cool cheese vampire snake excaim question

Используйте нормальные имена. Ваш комментарий будет опубликован после проверки.

     

  

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

(обязательно)