MySQL GROUP_CONCAT() maximum length

Here in this tutorial, you will learn how you can use the MySQL GROUP_CONCAT() function to concatenate strings from a group with various options. There are many cases where you can apply the GROUP_CONCAT() function to produce useful results.

In MySql, GROUP_CONCAT() is used to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters.

The GROUP_CONCAT() function in MySQL is used for concatenating data from multiple rows into one single field. This is an aggregate (GROUP BY) function that returns a String value, if the group contains at least one non-NULL value. Otherwise, it returns NULL.

So, You should first check the character length that GROUP_CONCAT supports in your mysql instance using below code:

SHOW VARIABLES LIKE '%group_concat%';

And You see that default character length GROUP_CONCAT supports : 1024

Use of various clauses inside GROUP_CONCAT() function:

• Dinstinct: This eliminates repetition of values from the result.
• Order By: This sort the values of group in specific order and then concatenate them.
• Separator: By default, values of group are separated by (, ) operator. In order to change this separator value, Separator clause is used followed by a string literal. It is given as Separator ‘str_value’.

Read Also: SQl MATCH AGAINST find results with only more than 3 characters in the result

If 1024 character length is fine for you requirement then there is nothing to worry. But if not, you have to modify the GROUP_CONCAT character length as per your requirement as below:

SET SESSION group_concat_max_len = 1000000;

This a temporary session-scope setting. This only applies to the current session and You should use it like this.

SET SESSION group_concat_max_len = 1000000;
SELECT group_concat(`field`) FROM TABLE_NAME GROUP BY `field`;

You can also do this in sharing hosting, but when you use an other session then you need to repeat the SET SESSION command. Now, you have learned how you can use the MySQL GROUP_CONCAT() function to concatenate non-NULL values of a group of strings into a single string.