MySQL and GROUP_CONCAT() maximum length – Dev

The best answers to the question “MySQL and GROUP_CONCAT() maximum length” in the category Dev.

QUESTION:

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:

The correct parameter to set the maximum length is:

SET @@group_concat_max_len = value_numeric;

value_numeric must be > 1024; by default the group_concat_max_len value is 1024.

ANSWER:

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.

ANSWER:

You can try this

SET GLOBAL group_concat_max_len = 1000000;

ANSWER:

Include this setting in xampp my.ini configuration file:

[mysqld]
group_concat_max_len = 1000000

Then restart xampp mysql