GROUP_CONCAT and String Aggregation
GROUP_CONCAT joins a group's values into one string with ORDER BY and SEPARATOR, and truncates at group_concat_max_len with only a warning.
GROUP_CONCAT([DISTINCT] expr [, expr ...] [ORDER BY expr [ASC|DESC]] [SEPARATOR str_val])GROUP_CONCAT() is the string aggregate: it concatenates the non-NULL values of a group into a single string. It is how you get a comma-separated list of tags, ids or names alongside the counts in a grouped query.
Syntax
GROUP_CONCAT([DISTINCT] expr [, expr ...]
[ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [, ...]]
[SEPARATOR str_val])The default separator is a comma; SEPARATOR '' removes it entirely. ORDER BY sorts the values inside the string and is not optional in practice — without it the order is whatever the execution plan produced, which can change between runs and between versions. DISTINCT removes duplicate values. If a group has no non-NULL values, the function returns NULL rather than an empty string, so wrap it in COALESCE when the caller expects a string.
The truncation that looks like data
The result is truncated at group_concat_max_len bytes, whose default is 1024. Truncation raises warning 1260, ER_CUT_VALUE_GROUP_CONCAT (Row N was cut by GROUP_CONCAT()) — a warning, not an error, so most clients and drivers discard it and hand your application a list that is quietly missing its tail. Raise it per session with SET SESSION group_concat_max_len = 65535;. The effective maximum is also bounded by max_allowed_packet, and the return type is TEXT/BLOB when the limit exceeds 512 bytes and VARCHAR/VARBINARY otherwise. Setting it very high invites out-of-memory errors.
Example
-- One row per article with its tags in a stable, readable order.
SET SESSION group_concat_max_len = 65535; -- default 1024 will cut long lists
SELECT
a.id,
a.title,
COUNT(t.id) AS tag_count,
COALESCE(
GROUP_CONCAT(DISTINCT t.name ORDER BY t.name SEPARATOR ', '),
'' -- NULL when the group has no tags
) AS tags
FROM articles AS a
LEFT JOIN article_tags AS at ON at.article_id = a.id
LEFT JOIN tags AS t ON t.id = at.tag_id
WHERE a.published_at IS NOT NULL
GROUP BY a.id, a.title
ORDER BY a.published_at DESC;When to use it
- Show each article's tags as one comma-separated column.
- Collapse a one-to-many join into a single row per parent for a CSV export.
- List the duplicate row ids found by a HAVING COUNT(*) > 1 query.
- Produce a short summary of the files or fields changed in each audit record.
More examples
Detecting truncation instead of shipping it
Truncation is invisible unless you look. Check SHOW WARNINGS during development, and in production prefer the length guard — it turns a silent data loss into a row you can alert on. Note LENGTH() counts bytes, which is the unit group_concat_max_len uses.
-- Run the aggregate, then immediately ask what MySQL thought of it.
SELECT order_id,
GROUP_CONCAT(sku ORDER BY sku SEPARATOR ',') AS skus
FROM order_items
GROUP BY order_id;
SHOW WARNINGS;
-- Level: Warning Code: 1260 Message: Row 3 was cut by GROUP_CONCAT()
-- Cheap guard you can leave in a report query: flag any group that is
-- suspiciously close to the limit.
SELECT order_id,
GROUP_CONCAT(sku ORDER BY sku) AS skus,
LENGTH(GROUP_CONCAT(sku ORDER BY sku)) >= @@session.group_concat_max_len
AS maybe_truncated
FROM order_items
GROUP BY order_id
HAVING maybe_truncated = 1;Building a key=value string and a JSON array
The first form is fine for display. The second is the one to use when an application parses the result: GROUP_CONCAT breaks the moment a value contains your separator, whereas JSON_ARRAYAGG and JSON_OBJECTAGG produce valid JSON and sidestep the 1024-byte limit.
-- Multiple expressions are concatenated per row before the separator is applied.
SELECT
survey_id,
GROUP_CONCAT(question_code, '=', answer
ORDER BY question_code SEPARATOR '&') AS query_string
FROM survey_answers
GROUP BY survey_id;
-- q1=yes&q2=no&q3=maybe
-- When the consumer is code rather than a human, aggregate to JSON instead:
-- JSON_ARRAYAGG has no group_concat_max_len truncation and escapes for you.
SELECT
survey_id,
JSON_ARRAYAGG(answer) AS answers,
JSON_OBJECTAGG(question_code, answer) AS answer_map
FROM survey_answers
GROUP BY survey_id;A one-line audit trail of what changed
Trimming the list yourself with SUBSTRING_INDEX is safer than letting group_concat_max_len do it: you choose the cut point, the count stays accurate, and the output says how much was hidden. Useful for changelog and activity-feed queries where a full list would be unreadable anyway.
-- Summarise each deploy's changed files, newest field first, capped for readability.
SELECT
d.id,
d.released_at,
COUNT(*) AS files_changed,
CONCAT(
SUBSTRING_INDEX(
GROUP_CONCAT(f.path ORDER BY f.path SEPARATOR ', '),
', ', 5), -- first five only
IF(COUNT(*) > 5, CONCAT(' (+', COUNT(*) - 5, ' more)'), '')
) AS summary
FROM deploys AS d
JOIN deploy_files AS f ON f.deploy_id = d.id
GROUP BY d.id, d.released_at
ORDER BY d.released_at DESC;
Discussion