Discovering MySQL GROUP_CONCAT

 in MySQL by

A relatively quick post this week, but I discovered something new in MySQL (as in new to me). A common query one might need to run is to get a list of rows from one table and it's related rows in a connected table.  For example, given these tables:

Let's say I want to get a list of books along with their related authors.  Now, you can do a standard query, such as:

SELECT title, firstname, lastname
FROM mylibrary_books b INNER JOIN mylibrary_books_to_authors bta ON b.bookid = bta.books_bookid
INNER JOIN mylibrary_authors a ON bta.authors_authorid = a.authorid
ORDER BY title

Which gives us results like so:

But for books with multiple authors, you have repeated lines, as you can see with A Day in the Life of Japan, which has two authors.  Usually, to display this more cleanly, so we have just one line per author, we'd have to do it in an application using something like PHP or ColdFusion to loop the results.  You could either do a query of titles and title ids and then look and get authors, then loop those, or use something like ColdFusion's group functions.  But either way, the presumption was that I'd have to write up a quick script to do such a query.

Then this week, needing a similar style query that just needed to dump to an Excel sheet, I discovered a function I had not heard of in MySQL – GROUP_CONCAT, introduced in version X.  This function let's you "get the concatenated values of expression combinations" as a string. It is similar to CONCAT, except it works on grouped rows.  It makes more sense with an example though.  So let's change our query to this:

SELECT title, GROUP_CONCAT(lastname SEPARATOR ', ') AS authorName
FROM mylibrary_books b INNER JOIN mylibrary_books_to_authors bta ON b.bookid = bta.books_bookid
INNER JOIN mylibrary_authors a ON bta.authors_authorid = a.authorid
GROUP BY bookid
ORDER BY title

Now A Day in the Life of Japan only has one row, but we only have the writers' last names. For the original query I needed, this was enough since it was just a single field in the other table I needed.  But for this example, it would be nice to have the full name.  So can we do something like this…

SELECT title, GROUP_CONCAT(CONCAT(firstname, " ", lastname) SEPARATOR ', ') AS authorName
FROM mylibrary_books b INNER JOIN mylibrary_books_to_authors bta ON b.bookid = bta.books_bookid
INNER JOIN mylibrary_authors a ON bta.authors_authorid = a.authorid
GROUP BY bookid
ORDER BY title

Yes, yes we can! 😀

So there ya go…titles with a readable list of characters!  So if you, like me, didn't know about this option, now you too have another tool in your querying toolbox that can help push some work to the DB where it makes sense to do so.

There is one caveat though – by default GROUP_CONCAT's resulting string is limited to 1,024 characters in length.  And if you exceed this, it silently failed and truncates the value.   For the use case we needed it for, it's fine as we would never exceed it, but if you will, you can increase it using the group_concat_max_len settings.  However, it will still be constrained by the max_allowed_packet variable, which defaults to 1GB.