MySQL and GROUP_CONCAT() maximum length

mysql group-concat
ZeWaren picture ZeWaren · Apr 2, 2010 · Viewed 267.7k times · Source

I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters.

I'm very well aware that I can change the param group_concat_max_len to increase this limit:

SET SESSION group_concat_max_len = 1000000;

However, on the server I'm using, I can't change any param. Not by using the preceding query and not by editing any configuration file.

So my question is: Is there any other way to get the output of a multiple row query into a single string?

Answer

keatkeat picture keatkeat · Aug 21, 2013 
SET SESSION group_concat_max_len = 1000000;

is a temporary, session-scope, setting. It only applies to the current session You should use it like this. 

SET SESSION group_concat_max_len = 1000000;
select group_concat(column) from table group by column

You can do this even in sharing hosting, but when you use an other session, you need to repeat the SET SESSION command.

Related questions

Can I concatenate multiple MySQL rows into one field?

Using MySQL, I can do something like: SELECT hobbies FROM peoples_hobbies WHERE person_id = 5; My Output: shopping fishing coding but instead I just want 1 row, 1 col: Expected Output: shopping, fishing, coding The reason is that I'm selecting multiple values …

Read More
How to use GROUP_CONCAT in a CONCAT in MySQL

If I have a table with the following data in MySQL: id Name Value 1 A 4 1 A 5 1 B 8 2 C 9 how do I get it into the following format? id Column 1 A:4,5,B:8 2 C:9 I think I have to use GROUP_CONCAT. …

Read More
GROUP_CONCAT comma separator - MySQL

I have a query where I am using GROUP_CONCAT and a custom separator as my results may contain commas: '----' This all works well, however it is still comma separated, so my output is: Result A----,Result B----,Result …

Read More