GROUP_CONCAT( ) в Mysql.

GROUP_CONCAT( ) в Mysql.
Сегодня хочу рассказать об интересной функции в Mysql.
Как оказалось, знают ее очень не многие программисты, уж не знаю почему ей мало кто пользуется, но я опросил десятка два своих знакомых и меньше половины знали о GROUP_CONCAT.
Эта функция работает примерно как CONCAT_WS(Объединяет строки с разделителем), только в отличии от CONCAT_WS способна объеденить результаты выборки из таблицы.

Синтаксис
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])

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

CREATE TABLE `test` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(250) NOT NULL, # это имя человека
`pet` varchar(250) NOT NULL, # это домашнее животное
PRIMARY KEY (`id`)
);

Данные в табличке вот такие
маша - попугай
света - кошка
маша - хомяк
юля - собака
света - собака
маша - рыбки

Из этой таблички видно, что
у Маши есть попугай, хомяк и рыбки,
у Светы кошка и собака,
а у Юли только собака.
Так вот есть задача вывести всех людей из таблицы и чтобы возле каждого показывалось, какие животные у него есть.

Делаем вот такой запрос
SELECT `name`, GROUP_CONCAT(`pet`) as `pet`
FROM `test`
GROUP BY `name`

Результат получается такой
маша - попугай,хомяк,рыбки
света- кошка,собака
юля - собака

Получилось объединение всех животных каждого человека.
GROUP BY `name` в конце сгруппировывает одинаковые имена, можно сказать отбрасывает дубли.

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

SELECT DISTINCT `name` as `name1`,
(select GROUP_CONCAT(`pet`) as `pet` from `test` where `name` = `name1` )as `pet`
FROM `test`

Этот пример использует вложенный select для создания поля со списком животных...
DISTINCT отбрасывает одинаковые имена. Поле name переименовываем, чтобы можно было во вложенном запросе правильно обратиться.
Этот пример уже более похож на пример из жизни.
Выводятся имена, отбрасывая одинаковые, выдергивается список животных для каждого имени.
Все очень просто. Не нужно получать каждое имя, а затем выдергивать животных для каждого имени, все происходит одним запросом.

А теперь подробнее про эту хитрую GROUP_CONCAT.

Разделитель.
По умолчанию используется разделитель запятая.
Если есть необходимость, можно задать другой разделитель

SELECT DISTINCT `name` as `name1`,
(select GROUP_CONCAT(`pet` SEPARATOR '::') as `pet` from `test` where `name` = `name1` )as `pet`
FROM `test`

Сейчас животные будут разделены не запятой, а двойным двоеточием (::)

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

Несколько разочарований.

Ограничение 1024.
У этой функции есть ограничение на объем выводимых данных.
По умолчанию 1024 символа для каждого объединения - для каждой выводимой строки.
Если размер склееных данных больше, то он будет урезаться.
Чтобы расширить размер нужно выполнить команду SET group_concat_max_len =4096;
Если у вас есть привелегии, то вы расширите объем получаемых данных до 4096, можно и больше.
Но чаще всего на обычных хостингах таких привелегий нет.

Только текст.
Следущая особенность GROUP_CONCAT это работа только со строками.
Если вы захотите склеить числа, то у вас ничего не получится, нужно преобразовать число в текст.

Допустим вы хотите получить не животных, а список ID.
Обычный вариант работать не будет, нужно конвертировать число в текст, например вот так

SELECT DISTINCT `name` as `name1`,
(select CONVERT(GROUP_CONCAT(`id`) USING cp1251) from `test` where `name` = `name1` )as `pet_id`
FROM `test`

Вот такой вот обзор интересной функции GROUP_CONCAT().
Использовать ее лучше всего для информации, не стоит полагаться на то, что вы получите абсолютно все данные, так как данных может быть больше чем установлено в ограничении.
Например вы выводите на странице список товаров.
У каждого товара показывается цена, наличие, вес, краткое описание. И тут можно например для каждого товара показать еще в каких цветах имеется этот товар.
Так как на этой странице выводится много товаров, поэтому дополнительную информацию о товаре стоит показывать сжато, и вот тут и можно подсунуть информацию в каких цветах присутсвует товар. Вывести например 5 самых популярных цветов этого товара.
Совершенно очевидно, если выводятся 20 товаров на странице, то показывать для каждого товара информацию о цветах размером более 1024 символов вы никогда не будете, перегружать страницу нет смысла.
Вот в таких местах есть смысл использовать GROUP_CONCAT().

Комментарии

RSS комментарии

24.05.2010 USvER
Наверное неправильно выразился,
На сервере возможна оптимизация(в этом суть всех агрегатных функций).
Я точно не знаю насколько данная функция оптимизирована но она явно требует меньше ресурсов чем обработка данных в клиенте.

31.05.2010 Павел фром да Таганрог
Спасибо огромное за сию заметку !
В старой версии одного движка с которым мы работаем был атрибут представляющий строку склееную из нескольких значений другого атрибута..в новой версии его убрали...и функций для работы в старом стиле не оставили...
чуть голову не сломал...