Работа с функцией GROUP_CONCAT()
Всем привет!
Хочу рассказать о работу с функцией GROUP_CONCAT.
Помнится меня на собеседовании в одну компанию спросили, как получить данные при группировке в строку…. ответа я не знал, конечно в тот же вечер я все узнал и понял как это легко))
Недавно мне надо было написать запрос как раз с использованием этой функции, и решил написать об этом статью.
И так, представим две таблицы, author(авторы) и books(книги авторов).
Заполним данные, ниже представлены две эти таблицы:
Таблица авторов: 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);
Сделаем выборки :
mysql> select * from author; +----+-------------------------------------+------------+------------+ | id | name | fam | birthday | +----+-------------------------------------+------------+------------+ | 1 | Николай Николаевич | Носов | 2008-11-23 | | 2 | Артур Конан | Дойль | 1859-05-22 | +----+-------------------------------------+------------+------------+ 2 rows in set (0.00 sec) mysql> select * from books; +----+-----------+------------------------------------------------+ | id | id_author | book | +----+-----------+------------------------------------------------+ | 1 | 1 | Незнайка учится | | 2 | 1 | Незнайка-путешественник | | 3 | 1 | Винтик, Шпунтик и пылесос | | 4 | 2 | Затерянный мир | | 5 | 2 | Шерлок Холмс | | 6 | 2 | Шерлок Холмс | | 7 | 2 | NULL | +----+-----------+------------------------------------------------+ 7 rows in set (0.00 sec)
Обратите внимание, во второй выборке я специально добавил дублирующееся поле (Шерлок Холмс) и также поле с NULL записью, для чего это я сделал объясню позже.
Теперь нам надо выбрать всех авторов и их книги, если я напишу запрос
SELECT a.id, a.name, a.fam, b.book as books FROM `author` a LEFT JOIN `books` b ON a.id=b.id_author;
В итоге мы получил все книги по всем авторам:
+----+-----------------------+----------+----------------------------+ | id | name | fam | books | +----+-----------------------+----------+----------------------------+ | 1 | Николай Николаевич | Носов | Незнайка учится | | 1 | Николай Николаевич | Носов | Незнайка-путешественник | | 1 | Николай Николаевич | Носов | Винтик, Шпунтик и пылесос | | 2 | Артур Конан | Дойль | Затерянный мир | | 2 | Артур Конан | Дойль | Шерлок Холмс | | 2 | Артур Конан | Дойль | Шерлок Холмс | | 2 | Артур Конан | Дойль | NULL | +----+-----------------------+----------+----------------------------+ 7 rows in set (0.00 sec)
А мне бы получить записи авторов, т.е. две записи, в нашем случае, и чтобы в поле books были все книги автора…
Это можно сделать применив как раз ф-цию GROUP_CONCAT().
Схема ф-ции:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
Для того, чтобы расписать каждую из команд, я напишу пример:
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 | Артур Конан | Дойль | Затерянный мир, Шерлок Холмс |
+----+--------------------+-------+---------------------------------------------------------------------+
2 rows in set (0.05 sec)
Как видно после использование этой ф-ции я получил то, что хотел — дву строчки и в поле books все книги автора.
Теперь распишем каждую команду.
Начну с того, что группировке поддается только поле отличные от NULL, т.е. записи с содержимыми типа NULL не будет в поле books.
DISTINCT — позволяет выбрать не повторяющиеся значения, т.е. книга с дублирующим названием будет откинута, для этого я ввел дважды название книги Шерлок Холмс;
ORDER BY col_name ASC | DESC — позволяет сортировать данные;
SEPARATOR — позволяет разделить данные через нужный вам делитель, по-умолчанию это запятая «,» (без кавычек)
Также эта функция относится к групповым функциям, поэтому использование её без оператора GROUP BY не желательно, иначе в нашем примере получим одну строку с первым автором в таблице и всеми книгами которые есть в таблице книг.
Есть еще одно ограничение. Кол-во символов которые будут отображены в сгруппированном поле по-умолчанию 1024, если надо больше, то использую переменную group_concat_max_len устанавливаем своё значение:
синатксис: SET [GLOBAL | SESSION] group_concat_max_len = val; пример: SET group_concat_max_len = 2048;
Всем спасибо, в пользу тебе %username%.
Статья просмотренна 267079 раз, зашло посетителей 96295
Спасибо, познавательно!
Действительно полезно. Не знал об этой функции…
Написал топик-ссылку на Хабр, лучше бы я этого не делал, там блин люди-гуру сидят и они уже забыли когда были зелеными и нифига не знали, понаставили кучу минусов, опустили ниже плинтуса… Наверное не тот ресурс выбрал для размещения ссылки.
Да, там лучше с подобными постами не появляться… 🙂
Меня там вообще забанили за подкол в камменте, в том году
Статьи там интересные, но я даже не статью, и топик-ссылку разместил… Короче ну эту тусовку, те люди забыли какими они были когда начинали….
Это ресурс для снобов. Статьи оттуда зачастую не имеют пользы кроме создания обобщенного представления о предмете.
Вы не правы, кучу раз пришлось применять разные ф-ции SQL в том числе и эту.
Большое спасибо! В практике не приходилось юзать такие типы полей!
Спасибо, очень доходчиво всё объяснили. Я подобную задачу решал с помощью php (рекурсия -> массив -> implode), но sql-решение в разы элегантнее.
Вы не проверяли производительность таких запросов на проектах с высокой посещаемостью и большой базой?
Пока не приходилось проверять на большой БД, хотя именно под неё и пришлось использовать данную ф-цию. У меня запрос на объединение около 10 таблиц, с кучей условий, группировкой и сортировкой. Сам запрос выполнятся за 0.6 сек (он реально тяжелый, поэтому для него норма, еще это на моём компе), когда я убираю группировку и использование этой ф-ции, я порчу результат запроса, но и выигрываю в 0.1 сек! Я думаю лучше потерять тут, чем делать то, что ты описал.
Спасибо, за описание! Очень долго гуглил внятного решения не мог найти.
Как раз разбирался с этой функцией — запрос делал выборку и со временем страница сломалась, так как проект подрос, добавил выполнение SET group_concat_max_len = 4048; и заработало
Рад, что статья помогла!
Отличная новость! Уже спешу к вам!
"Помниться", "храниться"… Ухо режет.
Спасибо, исправлю, но ведь суть статьи не в этом ведь…
А как можно вывести в php в виде таблицы эти данные,в примере "А мне бы получить записи авторов, т.е. две записи, в нашем случае, и чтобы в поле books были все книги автора…",там,где данные берутся из нескольких таблиц?Вывести так,как показано в примере?
Ну как, вы получаете асоциативный массив, и с помощью цикла по значениям строите таблицу, обычное знаение HTML, MyQSL и PHP. И самое главное МОЗГИ, это настолько базовый вопрос, вам наверняка его на лекциях рассказывали.
Спасибо, оч доступно. Помогло.
з.ы. to "bondyara" доступно и понятно а ухи не "орехи" переживут))
грешу, русский всегда знал на 3 (((
Спасибо за статью.
Благодаря ей быстро нагуглила ответ. 🙂
Пожалуйста
Спасибо!
Статья навела порядок еще в понимании оператора GROUP BY
Рад, что помог
Спасибо! Ваша статья отправная точка в решении моей проблемы.
Подскажите пожалуйста, как можно усовершенствовать запрос для получения идентификаторов к каждой книге? Конечно можно точно также сгруппировать идентификаторы и в скрипте уже обрабатывать, но хотелось бы более лучший способ 🙂 Возможно с использованием алиасов?
Спасибо! То, что нужно было! А то нашелся в гугле какой-то жуткий вариант, а у вас все ясно и просто!
большое спасибо! пример отличный, очень доходчиво объясняете
И вам спасибо!
Спасибо, иногда видимо может пригодиться (искал вовсе не это).
по поводу производительности: кажется в результатах все видно 0.00sec против 0.05sec — в лучшем случае в 10раз (0.006 округлится до 0.01)
Не за что, но я бы не судил о производительности на таком малом объеме данных (всего 7 строк) возможно мой комп был чем-то занят и придержал работу мускуля, если вам нужен реальный тест, нужно его сделать на большом объеме данных.
согласен, что бывает всякое, даже скорее всего так и было т.к. разница слишком огромная для довольно простых операций. И тем не менее в нагруженных проектах и на часто используемых страницах я бы этот метод скорее всего использовать не стал (зачем грузить сервер бд зазря)
супер!! спасибо
Не за что!
Спасибо за статью.
Не знал как такое реализовать, приходилось использовать кучу переменных для сравнивания "одинаков ли id прошлой записи" 🙂
Буду переписывать на нормальный код!
Рад, что помог.
очень помогла статья, спасибо тебе, добрый человек)
Welcome!
а зачем там left join? оно ведь и без этого работает
Но если книг у автора почему то нет, а вывести его надо?….=)
Большое спасибо за подробную и познавательную статью! Очень помогла.
Хотелось бы еще добавить что в PhpMyadmin запрос увеличения лимита надо делать вместе с основным запросом. Иначе лимит не увеличится.
Типа:
SET SESSION group_concat_max_len = 16384;
UPDATE ….
Пожалуйста. Есть же глобальная установка переменных (до следующей перезагрузки сервиса), а есть локальная — только на пачку запросов, а вот если в конфиг добавите — будет вечно!
Спасибо тебе, кто бы ты ни был! Эта функция решит много проблем с которыми раньше сталкивался.
Благодарен за твою работу!
Привет! Рад что помог!
Огромное спасибо Вы только что спасили меня вот от такого монстра:
SELECT t1.id as ID, t1.post_name as URL, t1.post_title as NAME, t2.meta_value as BEGIN, t1.post_content as DETAIL, t3.meta_value as END, t4.meta_value as LOGO, t4.meta_value as CNT_ACTIONS, t4.meta_value as COST_ACTION, t7.meta_value as LANG, t8.meta_value as VENUE
FROM wp_posts t1
LEFT JOIN wp_postmeta t2 ON t1.id = t2.post_id AND t2.meta_key = '_EventStartDate'
LEFT JOIN wp_postmeta t3 ON t1.id = t3.post_id AND t3.meta_key = '_EventEndDate'
LEFT JOIN wp_postmeta t4 ON t1.id = t4.post_id AND t4.meta_key = '_thumbnail_id'
LEFT JOIN wp_postmeta t5 ON t1.id = t5.post_id AND t5.meta_key = 'cnt_actions'
LEFT JOIN wp_postmeta t6 ON t1.id = t6.post_id AND t6.meta_key = '_EventCost'
LEFT JOIN wp_postmeta t7 ON t1.id = t7.post_id AND t7.meta_key = 'language'
LEFT JOIN wp_postmeta t8 ON t1.id = t8.post_id AND t8.meta_key = '_EventVenueID'
WHERE t1.post_status = 'publish'
AND t1.post_type='tribe_events'
I’ve been browsing online more than 10 hours today
for window 7 starter snpc oa download & Работа с функцией
GROUP_CONCAT(), yet I never found any interesting article like yours.
It is pretty worth enough for me. In my opinion, if all website owners and bloggers
made good content as you did, the internet will be much more useful than ever before.
Дорогой Автор Maxnag! Спасибо тебе огромное. Благодаря твоей статье решил задачу экспортаимпорта на своем сайте.
Можно вопрос? Прошу еще помочь с таким вопросом.
В моем случае у меня не книги а имена файлов. И мне нужно перед началом каждого файла ( у тебя books) добавить урл. Если взять твой пример то в финале столбец books должен содержать:
http://www.mydomain/Винтик, http://www.mydomain/Шпунтик, http://www.mydomain/незнайка
Не подскажешь как добавить строку "www.mydomain/" в начале?
Привет! Спасибо за каммент!
вместо этого GROUP_CONCAT(DISTINCT b.book…. попробуй так GROUP_CONCAT(DISTINCT CONCAT('www.mydomain/', b.book)….
спасибо
Спасибо!