За чистый и ясный код!

Статьи на тему программирования под веб, используя PHP, MySQL, Jquery и многое другое

Работа с функцией GROUP_CONCAT()

Июнь15

Всем привет!

Хочу рассказать о работу с функцией 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%.

Статья просмотренна 176383 раз, зашло посетителей 58122

рубрика: MySQL

43 комментария в “Работа с функцией GROUP_CONCAT()”

  1. Avatar
    rame0 пишет:

    Спасибо, познавательно!


  2. Avatar
    Snowcore пишет:

    Действительно полезно. Не знал об этой функции…


  3. Avatar
    maxnag пишет:

    Написал топик-ссылку на Хабр, лучше бы я этого не делал, там блин люди-гуру сидят и они уже забыли когда были зелеными и нифига не знали, понаставили кучу минусов, опустили ниже плинтуса… Наверное не тот ресурс выбрал для размещения ссылки.


  4. Avatar
    adw0rd пишет:

    Да, там лучше с подобными постами не появляться… 🙂
    Меня там вообще забанили за подкол в камменте, в том году


  5. Avatar
    maxnag пишет:

    Статьи там интересные, но я даже не статью, и топик-ссылку разместил… Короче ну эту тусовку, те люди забыли какими они были когда начинали….


  6. Avatar
    @iFeofilaktov пишет:

    Это ресурс для снобов. Статьи оттуда зачастую не имеют пользы кроме создания обобщенного представления о предмете.


  7. Avatar
    maxnag пишет:

    Вы не правы, кучу раз пришлось применять разные ф-ции SQL в том числе и эту.


  8. Avatar
    maxnag пишет:

    Большое спасибо! В практике не приходилось юзать такие типы полей!


  9. Avatar
    Renta пишет:

    Спасибо, очень доходчиво всё объяснили. Я подобную задачу решал с помощью php (рекурсия -> массив -> implode), но sql-решение в разы элегантнее.
    Вы не проверяли производительность таких запросов на проектах с высокой посещаемостью и большой базой?


  10. Avatar
    maxnag пишет:

    Пока не приходилось проверять на большой БД, хотя именно под неё и пришлось использовать данную ф-цию. У меня запрос на объединение около 10 таблиц, с кучей условий, группировкой и сортировкой. Сам запрос выполнятся за 0.6 сек (он реально тяжелый, поэтому для него норма, еще это на моём компе), когда я убираю группировку и использование этой ф-ции, я порчу результат запроса, но и выигрываю в 0.1 сек! Я думаю лучше потерять тут, чем делать то, что ты описал.


  11. Avatar
    Семёныч пишет:

    Спасибо, за описание! Очень долго гуглил внятного решения не мог найти.


  12. Avatar
    Antonio пишет:

    Как раз разбирался с этой функцией — запрос делал выборку и со временем страница сломалась, так как проект подрос, добавил выполнение SET group_concat_max_len = 4048; и заработало


  13. Avatar
    maxnag пишет:

    Рад, что статья помогла!


  14. Avatar
    Premoobjems пишет:

    Отличная новость! Уже спешу к вам!


  15. Avatar
    bondyara пишет:

    "Помниться", "храниться"… Ухо режет.


  16. Avatar
    maxnag пишет:

    Спасибо, исправлю, но ведь суть статьи не в этом ведь…


  17. Avatar
    favorite пишет:

    А как можно вывести в php в виде таблицы эти данные,в примере "А мне бы получить записи авторов, т.е. две записи, в нашем случае, и чтобы в поле books были все книги автора…",там,где данные берутся из нескольких таблиц?Вывести так,как показано в примере?


  18. Avatar
    maxnag пишет:

    Ну как, вы получаете асоциативный массив, и с помощью цикла по значениям строите таблицу, обычное знаение HTML, MyQSL и PHP. И самое главное МОЗГИ, это настолько базовый вопрос, вам наверняка его на лекциях рассказывали.


  19. Avatar
    axe79 пишет:

    Спасибо, оч доступно. Помогло.

    з.ы. to "bondyara" доступно и понятно а ухи не "орехи" переживут))


  20. Avatar
    maxnag пишет:

    грешу, русский всегда знал на 3 (((


  21. Avatar
    I пишет:

    Спасибо за статью.

    Благодаря ей быстро нагуглила ответ. 🙂


  22. Avatar
    maxnag пишет:

    Пожалуйста


  23. Avatar
    @olegggI пишет:

    Спасибо!
    Статья навела порядок еще в понимании оператора GROUP BY


  24. Avatar
    maxnag пишет:

    Рад, что помог


  25. Avatar
    Вадим пишет:

    Спасибо! Ваша статья отправная точка в решении моей проблемы.
    Подскажите пожалуйста, как можно усовершенствовать запрос для получения идентификаторов к каждой книге? Конечно можно точно также сгруппировать идентификаторы и в скрипте уже обрабатывать, но хотелось бы более лучший способ 🙂 Возможно с использованием алиасов?


  26. Avatar
    Kate пишет:

    Спасибо! То, что нужно было! А то нашелся в гугле какой-то жуткий вариант, а у вас все ясно и просто!


  27. Avatar
    Victoria пишет:

    большое спасибо! пример отличный, очень доходчиво объясняете


  28. Avatar
    maxnag пишет:

    И вам спасибо!


  29. Avatar
    alexey пишет:

    Спасибо, иногда видимо может пригодиться (искал вовсе не это).
    по поводу производительности: кажется в результатах все видно 0.00sec против 0.05sec — в лучшем случае в 10раз (0.006 округлится до 0.01)


  30. Avatar
    maxnag пишет:

    Не за что, но я бы не судил о производительности на таком малом объеме данных (всего 7 строк) возможно мой комп был чем-то занят и придержал работу мускуля, если вам нужен реальный тест, нужно его сделать на большом объеме данных.


  31. Avatar
    alexey пишет:

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


  32. Avatar
    Роман пишет:

    супер!! спасибо


  33. Avatar
    maxnag пишет:

    Не за что!


  34. Avatar
    PavelDAS пишет:

    Спасибо за статью.

    Не знал как такое реализовать, приходилось использовать кучу переменных для сравнивания "одинаков ли id прошлой записи" 🙂

    Буду переписывать на нормальный код!


  35. Avatar
    maxnag пишет:

    Рад, что помог.


  36. Avatar
    Alex пишет:

    очень помогла статья, спасибо тебе, добрый человек)


  37. Avatar
    maxnag пишет:

    Welcome!


  38. Avatar
    йцуке пишет:

    а зачем там left join? оно ведь и без этого работает


  39. Avatar
    Евгений пишет:

    Но если книг у автора почему то нет, а вывести его надо?….=)


  40. Avatar
    optimlab пишет:

    Большое спасибо за подробную и познавательную статью! Очень помогла.
    Хотелось бы еще добавить что в PhpMyadmin запрос увеличения лимита надо делать вместе с основным запросом. Иначе лимит не увеличится.
    Типа:
    SET SESSION group_concat_max_len = 16384;
    UPDATE ….


  41. Avatar
    maxnag пишет:

    Пожалуйста. Есть же глобальная установка переменных (до следующей перезагрузки сервиса), а есть локальная — только на пачку запросов, а вот если в конфиг добавите — будет вечно!


  42. Avatar
    Александр пишет:

    Спасибо тебе, кто бы ты ни был! Эта функция решит много проблем с которыми раньше сталкивался.
    Благодарен за твою работу!


  43. Avatar
    maxnag пишет:

    Привет! Рад что помог!


не публикуется

пример

Оставить комментарий или два:

  

Облако тегов

cli csv dump events form Kohana locale models MySQL mysqldump orm PHP tools trigger validate газ газовый счетчик итоги кеширование переменные

Облако тегов плагина WP Cumulus для WordPress требует для просмотра Flash Player 9 или выше.

Я на твиттере!

  • у твиттера тоже бывают перерывы...

Календарь

Июнь 2010
Пн Вт Ср Чт Пт Сб Вс
« Май   Июл »
 123456
78910111213
14151617181920
21222324252627
282930  

Сейчас на сайте