GROUP_CONCAT is very useful when you are doing a GROUP BY, and want to capture the values from other columns. For example:

SELECT country, GROUP_CONCAT(city SEPARATOR ',') AS city_arr_str
FROM myTable
GROUP BY country
ORDER BY country

Then in say PHP, you can read the city names for each returned row:

foreach ($rows as $r){
  $country = $r["country"];
  $city_arr_str = $r["city_arr_str"];
  // parse the city names
  $city_arr = explode(",", $city_arr_str);
  // do your stuff...
}

Everything is fine and all, until later on your database gets much bigger.

Imagine now your db table gets lots of duplicates, and each country has more than 1000 cities. When you run the same query and code, you will notice something seems wrong… It turns out there is a length limit to what GROUP_CONCAT returns. The length is 1024 characters.

To change that limit, go to your information_schema db, search for GROUP_CONCAT_MAX_LEN in the GLOBAL_VARIABLES table, then change the default value 1024 to something higher. Below is the sql query.

SET [GLOBAL | SESSION] group_concat_max_len = 1024;

But if you don’t have the permission to make the above change, or you just want a quick fix, below is a way to get rid of the duplicates hopefully the returned string is shorter than 1024 characters.

SELECT country, GROUP_CONCAT(DISTINCT city ORDER BY city SEPARATOR ',') AS city_arr_str
FROM myTable
GROUP BY country
ORDER BY country

I threw in the “ORDER BY” so we don’t need to do a sort again in PHP. Anyhow, that’s the idea. Happy programming! 😉