Understanding and Setting group_concat_max

If you’ve ever used MySQL’s GROUP_CONCAT() function and found that your results were mysteriously cut off, you’ve likely run into the group_concat_max_len setting. This system variable controls how much data MySQL is allowed to return when aggregating strings — and the default value is often much smaller than people expect.

This post explains what group_concat_max_len does, why it matters, how to change it, and how to choose an appropriate value.

What Is group_concat_max_len?

group_concat_max_len defines the maximum length (in bytes) of the string returned by the GROUP_CONCAT() function.

GROUP_CONCAT() is commonly used to:

Combine multiple row values into a single delimited string
Generate comma-separated lists
Build dynamic SQL
Summarize metadata (tables, columns, permissions, etc.)

When the result of GROUP_CONCAT() exceeds group_concat_max_len, MySQL silently truncates the output.

That silent truncation is what makes this variable especially dangerous — queries still succeed, but the data may be incomplete.

The Default Value (and Why It’s a Problem)

The default value of group_concat_max_len is:

1024

That’s just 1 KB.

In real-world usage, this is often too small for:

Aggregating long strings
Concatenating many rows
Metadata queries
Schema introspection
Reporting queries

Because truncation happens quietly, you may not notice the problem until downstream logic breaks or data appears incomplete.

Why You Should Set It Explicitly

You should consider setting group_concat_max_len whenever:

You rely on GROUP_CONCAT() for correctness (not just display)
You concatenate more than a handful of rows
You generate SQL, JSON, or structured output
You query information schema or metadata tables

Explicitly setting the value:

Prevents silent data loss
Makes query behavior predictable
Avoids surprises across environments (dev vs prod)

How to Set group_concat_max_len

Check the Current Value

SHOW VARIABLES LIKE ‘group_concat_max_len’;

Set It for the Current Session (Most Common)

This affects only the current connection:

SET SESSION group_concat_max_len = 1000000;

This is usually the safest and most flexible approach, especially for applications or reporting jobs.

Set It Globally

This affects all new connections:

SET GLOBAL group_concat_max_len = 1000000;

⚠️ Notes:

Requires appropriate privileges
Existing sessions are not affected
The change does not persist after a restart unless added to configuration

Persist It in MySQL Configuration

To make the change permanent, add it to your MySQL configuration file:

[mysqld]

group_concat_max_len = 1000000

Restart MySQL for the change to take effect.

Recommended Values

There’s no universal “right” value, but here are practical guidelines:

Use Case	Recommended Value
Light reporting	10,000 – 50,000
Metadata queries	100,000 – 500,000
Large aggregations	1,000,000
Heavy string generation	Several million (with caution)

Things to keep in mind:

The value is in bytes, not characters
Very large values can increase memory usage
Setting it extremely high without need offers little benefit

For most applications, 1,000,000 (1 MB) is a safe and commonly used value.

Best Practices

Always set it explicitly when using GROUP_CONCAT() for critical logic
Prefer session-level settings unless all workloads require it
Avoid relying on defaults
Monitor queries that generate very large concatenated strings

Final Thoughts

group_concat_max_len is a small setting with an outsized impact. Because MySQL truncates GROUP_CONCAT() output without warning, leaving this variable at its default can lead to subtle and hard-to-diagnose issues.

A simple SET SESSION group_concat_max_len = … at the right time can save hours of debugging later — and ensure your queries return exactly what you expect.