Thread: group_concat in MySQL
View Single Post
  #43 (permalink)  
Old 03-31-2008, 09:00 PM
Murali Murali is offline
D-Web Master
  
Join Date: Feb 2007
Location: India-Chennai.
Posts: 386
Murali is on a distinguished road
Send a message via AIM to Murali
Default Re: group_concat in MySQL
Quote:
Originally Posted by Murali View Post
Hi All,

Code:
CREATE TABLE DUMMY(ID INTEGER,X LONGTEXT);
INSERT INTO DUMMY VALUES (1,1);
COMMIT;
Code:
SELECT LENGTH('123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT')AS Len;
Output Length: 1457

Code:
SELECT GROUP_CONCAT('123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT') FROM DUMMY AS Result;
Output Result Length: 1024

But my String has the Length of 1457 and iam getting the length of just 1024.
What may be the reason?


Now i checked the Variable group_concat_max_len in my server side and i found that,

Code:
SELECT @@group_concat_max_len;
Code:
Output: 1024
So i really need to adjust my Group Concat Maximum Length to Get more Values in the String.. Here i do as per to Adjust the Length in the Session as,

Code:
SET SESSION group_concat_max_len:=4294967295; -- 4 GB size;
Now I tried the Same query,

Code:
SELECT GROUP_CONCAT('123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT') FROM DUMMY AS Result;
Output Result Length: 1457



Hope it is clear !!!!
group_concat_max_len is a system Variable which can be modified in every session using the syntax,
Code:
SET SESSION group_concat_max_len:=4294967295; -- 4 GB size;
When u need to make it fixed permanently then u can assign the value to the system variable in your MYSQL config file (my.cnf)

Just add as follows in the MYSQL config file,

Code:
group_concat_max_len:=4294967295;
Now u need not to set the variable value in every session and you can simply work with that once u have set permanently in the MYSQL config file.

__________________
-Murali..
Reply With Quote