group_concat in MySQL

Murali · #41 10-29-2007, 02:33 AM

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 !!!!

Gopisoft · #42 11-08-2007, 10:00 PM

Hi,

CONCAT() and CONCAT_WS() function

CONCAT() Function

Code:

Syntax
CONCAT(str1,str2,...)

This function eeturns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string.

CONCAT() returns NULL if any argument is NULL.

Example:

Code:

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');        
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

CONCAT_WS() Function

Code:

Syntax
CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() function for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

Note: CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

Code:

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
        -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
        -> 'First name,Last Name'

Thanks
R.Gopi.

Murali · #43 03-31-2008, 08:00 PM

Quote:

Originally Posted by Murali

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.

levelup · #44 09-29-2008, 05:56 AM

Thanks for all your post, I have the same problem and now it has been sorted

prabhurangan · #45 12-17-2008, 08:41 PM

Hi Priyan,

Im a new member. Your post was really helpfull to me. However i need to increase the max_allowed_packet and group_concat_max_len size in LINUX. But i changed the settings in /etc/my.cnf' file but the size is not changing. Again if i check the value it shows the default values. Also i would like to know, the configuration file depends on our RAM size and looks for it.. Like we have 'small', 'medium', 'large' and ;'heavy' in this which file we have to change.

But i added the below given lines in my.cnf file in WINDOWS it is working fine.

Please help out to solve this issue.

Lines i added in 'my.cnf' file:

# Set max allowed packet size to 2 MB
max_allowed_packet=2M

# Set group concat max length to 1 MB
group_concat_max_len=1M

Regards,
Prabhu Rangan.

hkp819 · #46 02-09-2009, 07:17 PM

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows:

Code:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

Code:

mysql> SELECT student_name,
    ->     GROUP_CONCAT(test_score)
    ->     FROM student
    ->     GROUP BY student_name;

or

Code:

mysql> SELECT student_name,
    ->     GROUP_CONCAT(DISTINCT test_score
    ->               ORDER BY test_score DESC SEPARATOR ' ')
    ->     FROM student
    ->     GROUP BY student_name;

In MySQL, you can get the concatenated values of expression combinations. You can eliminate duplicate values by using DISTINCT. If you want to sort values in the result, you should use ORDER BY clause. To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword. SEPARATOR is followed by the string value that should be inserted between values of result. The default is a comma (“,”). You can eliminate the separator altogether by specifying SEPARATOR ''.

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

Code:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Beginning with MySQL 5.0.19, the type returned by GROUP_CONCAT() is always VARCHAR unless group_concat_max_len is greater than 512, in which case, it returns a BLOB. (Previously, it returned a BLOB with group_concat_max_len greater than 512 only if the query included an ORDER BY clause.)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
MySQL	lldcrb328	PHP Programming	2	02-15-2009 09:45 PM
I Can Help With MYSQL	theseokit	Database Support	9	01-21-2009 11:01 PM
php-mysql	lekshmy	PHP Programming	0	01-21-2009 10:58 PM
Why to use MySQL	Amisha_Sharma	Database Support	1	01-07-2009 07:56 PM
multiple group_concat problem	fncll	Database Support	4	11-30-2007 12:06 AM