WeBinstruments

Инструментарий для веб-мастеров

 

на сайте на всех сайтах для веб разработчиков везде
 
 Скрипты
  PHP скрипты   
  Flash скрипты   
  Javascript скрипты   

 Софт
  Инструменты SEO   
  Разработка сайтов   
  Серверное ПО   

 Документация
  MySQL   
  PHP   
  HTML   
  Протоколы и сервисы Internet   

 Статьи
  PHP   
  MySQL   
  Сервер   
  еще...   

 Услуги и сервисы
  Домены   
  Хостинги   
  Блоки для сайта   

 Новости


 Контакты
  icq: 158325531
  email:
  все контакты: здесь




Atom все поступления
Раздел Файлы
Раздел Услуги и сервисы
Раздел Новости
Раздел Статьи
комментарии



 Партнеры


Главная / Статьи / MySQL / GROUP_CONCAT( ) в Mysql.


GROUP_CONCAT( ) в Mysql.

04.10.2008
сайт автора: http://webi.ru
публикация данной статьи разрешена только со ссылкой на сайт автора статьи

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 комментарии


27.11.2008 Евгений
Не плохая заморочка



07.12.2008 Виола
Отличная возможность. Я знала о ней, но не знала, что там есть ограничение. Вот только непонятно, зачем преобразование в текст? Я получала как раз список ID и без преобразования.



07.12.2008 Вадим
Виола, наверное ваши ID хранились в текстовых полях.
Если поле в базе имеет числовой тип, то эта функция без преобразования не выведет ничего... Надо обязательно преобразовать в текст.



07.12.2008 Виола
Да нет, поле ID у меня было - int(11), автоинкрементное, первичный ключ. Возможно дело в том, что уже после выборки я делаю неявное преобразование в текст - цепляю результат к строке. Но в самой выборке ничего такого нет и она возвращает требуемый результат.



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



24.02.2009 Сергио
Виола дело говорит, у меня тоже Int выводит нормально. Версия 5.1



24.04.2009 Гумер
СПАСИБО!!!

Очень понадобилось.



16.06.2009 Нигина
У меня ошибка, хотя все правилно скопировала;)



18.10.2009 tyman8992
mysql 5.0.51b, Таблица InnoDB - конвертирование INT(11) обязательно, только что проверил.



21.10.2009 Артур
Спасибо.
Получилось вывести результат в формате, аналогичном сводной таблице в Excel.



21.12.2009 Семён
По ходу есть еще одна особенность.
Результат объединения не может быть больше 255. Это печально :(



21.12.2009 админ
255 это совсем мало, может все таки 1024?



19.01.2010
mможно объеденять несколько полей и вставлять разделитель
например вполне работает
select msisdn, group_concat(week,'=',charging order by week) from y2009 group by msisdn
380xxxxxxxxxx 40=8,41=4,44=8,45=8,46=4,47=1,50=8,51=9,52=4,53=1




09.03.2010 Дмитрий
А у меня не получается вот запрос

SELECT product.id,title, catid, GROUP_CONCAT(`idSpecific`) AS `specific`
FROM `product`
LEFT JOIN `specification_items` ON product.id = idProduct
WHERE catid =46 GROUP BY title

Результат specific [BLOB - 5 Bytes]



09.03.2010 админ
эта функция работает только со строками.
а в вашем случае скорее всего идет обработка числа.
нужно конвертировать данные так
CONVERT(GROUP_CONCAT(`idSpecific`) USING cp1251)



24.03.2010 Дмитрий
Огромное спасибо переконвертировал :)



25.03.2010 AJ
а можно какнибудь избежать вложенных селектов? если в запросе нужно вывести несколько сотен (тысяч) записей, это же будет столько же вложенных запросов.



26.03.2010 виктор
а разве через JOIN не получается избежать вложенных селектов?
хотя join тут наверное не поможет, так как тут в одном запросе выбирается много строк...
наверное без вложенного селекта это не сделать.






24.05.2010 USvER
Хорошая функция! Помогла избежать изобретению велосипеда и собственной сериализации.

to AJ, А тут как не крути а подзапрос обязателен. Вопрос лиш в том кто будет выполнять этот подзапрос - клиент или сервер. Использовать термин "подзапрос" конечно не совсем коректно, я не уверен но скорее всего на сервере произойдет только одна выборка подзапроса сразу для всего внешнего запроса, что невозможно реализовать на клиенте.
Как жаль что есть ограничение. Оно вообще бессмысленно и обоснованно только как мера защиты отчего-то(Переполнение буфера?).





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


1 2 3

Добавить свой комментарий


Ваше имя(* обязательно)


Текст сообщения(* обязательно)









 
 
  запомнить

 
Copyright © 2003-2024 WeBi Constructor
Rambler's Top100