Tutorial :MySQL and GROUP_CONCAT() maximum length



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?


Solution:1

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.


Solution:2

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.


Solution:3

Include this setting in xampp my.ini configuration file:

[mysqld]  group_concat_max_len = 1000000  

Then restart xampp mysql


Solution:4

You can try this

SET GLOBAL group_concat_max_len = 1000000;  


Solution:5

The correct syntax is mysql> SET @@global.group_concat_max_len = integer;
If you do not have the privileges to do this on the server where your database resides then use a query like:
mySQL="SET @@session.group_concat_max_len = 10000;"or a different value.
Next line:
SET objRS = objConn.Execute(mySQL)  your variables may be different.
then
mySQL="SELECT GROUP_CONCAT(......);" etc
I use the last version since I do not have the privileges to change the default value of 1024 globally (using cPanel).
Hope this helps.


Solution:6

CREATE TABLE some_table (    field1 int(11) NOT NULL AUTO_INCREMENT,    field2 varchar(10) NOT NULL,    field3 varchar(10) NOT NULL,    PRIMARY KEY (`field1`)  );    INSERT INTO `some_table` (field1, field2, field3) VALUES  (1, 'text one', 'foo'),  (2, 'text two', 'bar'),  (3, 'text three', 'data'),  (4, 'text four', 'magic');  

This query is a bit strange but it does not need another query to initialize the variable; and it can be embedded in a more complex query. It returns all the 'field2's separated by a semicolon.

SELECT result  FROM   (SELECT @result := '',                 (SELECT result                  FROM   (SELECT @result := CONCAT_WS(';', @result, field2) AS result,                                 LENGTH(@result)                            AS blength                          FROM   some_table                          ORDER  BY blength DESC                          LIMIT  1) AS sub1) AS result) AS sub2;   

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »