The technique described in this card has an important caveat: The result of GROUP_CONCAT is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. This will cause horrible, data-destroying bugs in production. For this reason you should probably not use GROUP_CONCAT ever. At least you must set the value of group_concat_max_len to an insanely high value on every database server your application runs on.
Like MAX or COUNT,
GROUP_CONCAT
Show archive.org snapshot
is a MySQL aggregation function you can use whenever your query contains a GROUP BY. You can use it to retrieve a comma-separated list of all values in a given column within each group.
For example, you have a table posts:
| id | user_id | title |
+----+---------+-----------------+
| 8 | 5 | How to rock |
+----+---------------------------+
| 9 | 5 | Shoes are weird |
+----+---------------------------+
| 10 | 5 | Best song |
+----+---------------------------+
| 11 | 6 | Sunny day |
+----+---------------------------+
| 12 | 6 | Greetings |
+----+---------------------------+
For each user_id we would like a comma-separated list of post IDs. GROUP_CONCAT lets you say this:
SELECT user_id, GROUP_CONCAT(id) FROM posts GROUP BY user_id;
The result set looks like this:
| user_id | GROUP_CONCAT(id) |
+---------+------------------+
| 5 | 8,9,10 |
+----------------------------+
| 6 | 11,12 |
+----+-----------------------+
Using GROUP_CONCAT with joined tables
GROUP_CONCAT works with joins as well.
Let's say we have a table courses:
| id | name |
+----+---------------+
| 1 | Ruby 101 |
+----+---------------+
| 2 | TDD for Poets |
+----+---------------+
We also have a second table bookings:
| id | course_id |
+----+-----------+
| 7 | 1 |
+----+-----------+
| 8 | 1 |
+----+-----------+
| 9 | 1 |
+----+-----------+
| 10 | 2 |
+----+-----------+
| 11 | 2 |
+----+-----------+
For each course we would like a comma-separated list of booking IDs. GROUP_CONCAT lets you say this:
SELECT courses.name, GROUP_CONCAT(bookings.id) FROM bookings INNER JOIN courses ON courses.id == bookings.course_id GROUP BY bookings.course_id;
The result set looks like this:
| courses.name | GROUP_CONCAT(bookings.id) |
+---------------+---------------------------+
| Ruby 101 | 7,8,9 |
+---------------+---------------------------+
| TDD for Poets | 10,11 |
+---------------+---------------------------+
