Trouble with GROUP_CONCAT and Longtext in MySQL

mysql group-concat truncated longtext
Iwasakabukiman picture Iwasakabukiman · Feb 9, 2009 · Viewed 13.4k times · Source

The SQL...

UPDATE Threads t 
SET t.Content = (
    SELECT GROUP_CONCAT(a.Content ORDER BY a.PageID SEPARATOR '<!-- pagebreak -->') 
    FROM MSarticlepages a
    WHERE a.ArticleID = t.MSthreadID GROUP BY a.ArticleID
)

As you can see it takes all of an article's pages (which are each stored as longtext in separate rows) and GROUP_CONCATs them into a single longtext row. The problem is the results are only so many characters and then it gets completely truncated, losing about 90% of the contents. Does CONCAT not handle longtext very well or is there something else I am doing wrong?

Answer

David Grant picture David Grant · Feb 9, 2009

According to the MySQL manual, the maximum length of GROUP_CONCAT is defined by the group_concat_max_len system variable, which defaults to 1024.

This value can be increased, by using the following command:

SET group_concat_max_len = <int>

It should be noted, however, that the value of group_concat_max_len is itself limited by the value of another system variable, max_allowed_packet, which defaults to 1,048,576.

This value can be increased to a maximum of 1,073,741,824, using the same syntax:

SET max_allowed_packet = <int>

Related questions

Can I concatenate multiple MySQL rows into one field?

Using MySQL, I can do something like: SELECT hobbies FROM peoples_hobbies WHERE person_id = 5; My Output: shopping fishing coding but instead I just want 1 row, 1 col: Expected Output: shopping, fishing, coding The reason is that I'm selecting multiple values …

Read More
How to use GROUP_CONCAT in a CONCAT in MySQL

If I have a table with the following data in MySQL: id Name Value 1 A 4 1 A 5 1 B 8 2 C 9 how do I get it into the following format? id Column 1 A:4,5,B:8 2 C:9 I think I have to use GROUP_CONCAT. …

Read More
MySQL and GROUP_CONCAT() maximum length

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_…

Read More