What is the maximum allowance for group_concat_max_len in MySQL?

25
votes

I am using a group_concat to concatenate a lot of rows into one.

I set group concat to 10000 using:

SET group_concat_max_len = 10000;

But even then, my output cells remain incomplete and end with ...

I tried setting group_concat_max_len = 20000 and even that didn't help.

I also tried setting group_concat_max_len to 99999999. It still doesn't complete my output text. And I checked one of the group concat stops at Length = 230 characters and then gives ...

Is there any other way?

2
mysqlmysql-workbenchgroup-concat
dev.mysql.com/doc/refman/5.6/en/… 18446744073709551615 – Mihai
@Mihai, why not post that as answer? – Rahul
@Rahul Eh,just a quick google search,I`m more interested in learning something rather than break the point bank. – Mihai
USe SET SESSION group_concat_max_len =.. before any query. – Mihai
Yeah, I posted my answer about 1 minute after Mihai commented. If I had wanted to post just a link with no descriptive text, I probably would have been simultaneous. I don't care that much about the points either, but if questions are answered in comments alone, they always remain in the "unanswered" queue. People who don't post real answers are breaking StackOverflow's intended usage. – Bill Karwin

2 Answers

39
votes

Check out this link: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_group_concat_max_len

All the MySQL configuration variables are documented on that page, with details like minimum, maximum, default value, whether you can set them globally or per-session, whether you can change them on a running instance or does it require a restart, and other description of usage.

The maximum value for group_concat_max_len is 18446744073709551615.

The group-concat string does not end with "..." If you try to group too much text, it just gets truncated. So I wonder if the problem is not with MySQL's settings, but with the display of your cells.

16
votes

For 32bit systems, the maximum value is 4294967295

For 64 bit systems, the maximum value is 18446744073709551615.

You can set the variable for your current session using 

SET SESSION group_concat_max_len=4294967295;

To set the variable forever use

SET GLOBAL group_concat_max_len=4294967295;

(see http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len)