MySQL: GROUP_CONCAT функция объединения строк группы в одну строку с различными разделителями
Эта функция работает примерно как CONCAT_WS(Объединяет строки с разделителем), только в отличии от CONCAT_WS способна объединить результаты выборки из таблицы
Эта функция работает примерно как 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().
Расширенный пример использования GROUP_CONCAT()
Создания таблиц:
CREATE TABLE `author` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `name` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', `fam` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', `birthday` DATE NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_unicode_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=3; //Заполняем таблицу: INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (1, 'Николай Николаевич', 'Носов', '2008-11-23'); INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (2, 'Артур Конан', 'Дойль', '1859-05-22'); //Таблица книг авторов: CREATE TABLE `books` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `id_author` INT(10) NULL DEFAULT NULL, `book` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', PRIMARY KEY (`id`), INDEX `id_author` (`id_author`) ) COLLATE='utf8_unicode_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=8; //Заполняем таблицу: INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (1, 1, 'Незнайка учится'); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (2, 1, 'Незнайка-путешественник'); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (3, 1, 'Винтик, Шпунтик и пылесос'); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (4, 2, 'Затерянный мир'); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (5, 2, 'Шерлок Холмс'); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (6, 2, 'Шерлок Холмс'); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (7, 2, NULL);
и выборка с группировкой GROUP_CONCAT():
mysql> SELECT a.id, a.name, a.fam, GROUP_CONCAT(DISTINCT b.book ORDER BY b.book ASC SEPARATOR ', ') AS books -> FROM `author` a -> LEFT JOIN `books` b ON a.id=b.id_author -> GROUP BY a.id; +----+--------------------+-------+---------------------------------------------------------------------+ | id | name | fam | books | +----+--------------------+-------+---------------------------------------------------------------------+ | 1 | Николай Николаевич | Носов | Винтик, Шпунтик и пылесос, Незнайка учится, Незнайка-путешественник | | 2 | Артур Конан | Дойль | Затерянный мир, Шерлок Холмс | +----+--------------------+-------+---------------------------------------------------------------------+
Распишем каждую команду:
Начну с того, что группировке поддается только поле отличные от NULL, т.е. записи с содержимыми типа NULL не будет в поле books.
DISTINCT — позволяет выбрать не повторяющиеся значения, т.е. книга с дублирующим названием будет откинута, для этого я ввел дважды название книги Шерлок Холмс;
ORDER BY col_name ASC | DESC — позволяет сортировать данные;
SEPARATOR — позволяет разделить данные через нужный вам делитель, по-умолчанию это запятая «,» (без кавычек)