MySQL GROUP_CONCAT Truncated? Check group_concat_max_len

MySQL’s GROUP_CONCAT() combines non-NULL values from each group into a string. If the output is cut off, check the function’s byte limit first. For concise syntax and return-value details, see the MySQL GROUP_CONCAT() reference.

Diagnose truncated output

group_concat_max_len sets the maximum result length in bytes. In MySQL 8.4, its default is 1024 bytes; max_allowed_packet can impose a lower effective limit.

Check the current session limit and packet cap:

SELECT @@SESSION.group_concat_max_len, @@GLOBAL.max_allowed_packet;

If the result is cut, run SHOW WARNINGS immediately after the GROUP_CONCAT() query to inspect its diagnostics. MySQL reports a truncated row as Row N was cut by GROUP_CONCAT() (error code 1260). If the full result is required, estimate its size and raise the limit for the current connection:

SET SESSION group_concat_max_len = 4096;

Use a limit appropriate for the expected output. Setting it excessively high can increase memory use or cause out-of-memory errors. See the MySQL group_concat_max_len system variable.

Understanding Basic Syntax

The foundation of GROUP_CONCAT() is straightforward, though it offers several customization options. At its simplest, the function takes a column name and concatenates its values:

GROUP_CONCAT(column_name)

Let’s see it in action with a basic example. Suppose we have a product_tags table that links products to their descriptive tags:

SELECT
    product_id,
    GROUP_CONCAT(tag_name) AS tags
FROM product_tags
GROUP BY product_id;

This query would transform multiple rows of tags per product into a single row with all tags concatenated together, defaulting to comma separation.

Customizing Separators and Ordering

One of GROUP_CONCAT()’s most useful features is the ability to specify your own separator using the SEPARATOR keyword:

GROUP_CONCAT(column_name SEPARATOR '|')

For example, to create a pipe-delimited list:

SELECT
    department_id,
    GROUP_CONCAT(employee_name SEPARATOR ' | ') AS team_members
FROM employees
GROUP BY department_id;

You can also control the order of concatenated elements with ORDER BY:

SELECT
    order_id,
    GROUP_CONCAT(product_name ORDER BY product_name ASC SEPARATOR ', ') AS products
FROM order_items
GROUP BY order_id;

This ensures your concatenated strings follow a predictable, organized pattern rather than random ordering.

Handling Distinct Values and Nulls

Duplicate values in your concatenated results? GROUP_CONCAT() offers a DISTINCT option to eliminate repeats:

SELECT
    customer_id,
    GROUP_CONCAT(DISTINCT product_category) AS unique_categories
FROM purchases
GROUP BY customer_id;

GROUP_CONCAT() skips NULL values; if a group contains no non-NULL values, it returns NULL. If you need to represent nulls explicitly in the output, consider using COALESCE():

SELECT
    project_id,
    GROUP_CONCAT(COALESCE(team_member, 'Unassigned')) AS team
FROM project_assignments
GROUP BY project_id;

Advanced Grouping Techniques

GROUP_CONCAT() truly shines when combined with other SQL features. Here are some powerful patterns:

Combining with CONCAT():

SELECT
    author_id,
    GROUP_CONCAT(CONCAT(book_title, ' (', publish_year, ')') SEPARATOR '; ') AS bibliography
FROM books
GROUP BY author_id;

Using with CASE expressions:

SELECT
    department,
    GROUP_CONCAT(
        CASE WHEN salary > 100000 THEN CONCAT(employee_name, '*')
        ELSE employee_name
        END
    ) AS staff_list
FROM employees
GROUP BY department;

Combine aggregates in two query levels:

SELECT
    country,
    GROUP_CONCAT(
        CONCAT(city, ': ', neighborhoods)
        ORDER BY city
        SEPARATOR ' | '
    ) AS locations
FROM (
    SELECT
        country,
        city,
        GROUP_CONCAT(DISTINCT neighborhood ORDER BY neighborhood SEPARATOR ', ') AS neighborhoods
    FROM offices
    GROUP BY country, city
) AS city_neighborhoods
GROUP BY country;

Aggregate each city’s neighborhoods in the inner query, then concatenate those city-level results by country. MySQL does not allow one aggregate function to be nested directly inside another aggregate in the same query block.

Performance considerations

Large groups, DISTINCT, and ORDER BY can increase the work and memory needed by GROUP_CONCAT(). Keep the result limit close to what the application needs instead of setting an unnecessarily large global limit.

Real-World Application Examples

Let’s explore some practical scenarios where GROUP_CONCAT() solves common problems:

Generating email lists:

SELECT
    'Marketing Team' AS recipient_group,
    GROUP_CONCAT(email SEPARATOR '; ') AS address_list
FROM employees
WHERE department = 'Marketing';

Returning JSON arrays:

SELECT
    user_id,
    JSON_ARRAYAGG(product_name) AS purchase_history
FROM user_orders
GROUP BY user_id;

Use a JSON aggregate instead of constructing JSON text with string concatenation; the server handles JSON quoting and escaping. MySQL does not guarantee the order of elements returned by JSON_ARRAYAGG(). See the MySQL aggregate function reference.

Conclusion

MySQL’s GROUP_CONCAT() function is like a Swiss Army knife for string aggregation - simple enough for basic concatenation but packed with features for sophisticated string manipulation. Whether you’re building comma-separated lists, assembling report data, or creating complex string outputs, this function eliminates the need for cumbersome application-side processing.

GROUP_CONCAT() is useful when a grouped query needs a delimited string. Set group_concat_max_len based on the expected output, check diagnostics for truncation, and use JSON aggregates when the result should be JSON. For grouping syntax, see MySQL GROUP BY.

The next time you find yourself writing application code to loop through rows and concatenate values, consider whether GROUP_CONCAT() could do the job more efficiently right in your SQL query.