The power of MySQL GROUP_CONCAT

MySQL GROUP_CONCATIn the very early days of Percona, Vadim wrote very nice post about MySQL GROUP_CONCAT (GROUP_CONCAT).

But I want to show you a bit more about it.

When is MySQL GROUP_CONCAT useful? Usually while working with Support customers I recommend it when you have aggregation of many-to-many info. It makes the view simpler and more beautiful and it doesn’t need much effort to make it work.

The following are some simple examples.

This is a test table:

Without grouping info the only way you can check things is:

But it looks much better and easier to read with GROUP_CONCAT:

Easy? Let’s go to production usage and some “real” examples 🙂

Assume you have 4 Support Engineers who were working with 6 Customers this week on 15 issues.

As it usually happens: everyone (sure, except those who are on vacation :)) worked on everything with everybody.

How you would represent it?

Here is my way:

Create test tables:

  • engineers (id, name, surname, URL) – list of engineers
  • customers (id, company name, URL) – list of customers
  • issues (id, customer_id, description) – list of issues assigned to customers
  • workflow (id, engineer_id, issue_id) – list of actions: issues and engineers who worked on them