How to change the limit of MySQL GROUP_CONCAT function

The MySQL GROUP_CONCAT() allows you to concatenate the values from a table column as one column.

For example, suppose you have a books table with the following data:

+----+----------------------+
| id | title                |
+----+----------------------+
|  1 | The Three Musketeers |
|  2 | Stardust             |
|  3 | The Hobbit           |
|  4 | The Alchemist        |
+----+----------------------+

You can join the values of the title column as one string using the function as follows:

SELECT GROUP_CONCAT(title) FROM books;

The returned result set will be as shown below:

+--------------------------------------------------------+
| GROUP_CONCAT(title)                                    |
+--------------------------------------------------------+
| The Three Musketeers,Stardust,The Hobbit,The Alchemist |
+--------------------------------------------------------+

The GROUP_CONCAT() function has a default length of 1024 characters, which is controlled by the global variable group_concat_max_len.

You can see this limit by using the following query:

SHOW VARIABLES LIKE 'group_concat_max_len';

--   +----------------------+-------+
--   | Variable_name        | Value |
--   +----------------------+-------+
--   | group_concat_max_len | 1024  |
--   +----------------------+-------+

If the joined values length is greater than the group_concat_max_len value, then the result string will be truncated.

You can change the max length limit of the GROUP_CONCAT() function with the SET [GLOBAL / SESSION] statement as shown below:

SET SESSION group_concat_max_len = 5;

The SESSION keyword will change the group_concat_max_len value only for the current session. If you use the GLOBAL keyword, then the variable value will be changed permanently for future sessions.

This means you need to log out and then log in again for the SET GLOBAL change to take effect.

Once changed, the next GROUP_CONCAT() function call will adhere to the new limit as you can see below:

SELECT GROUP_CONCAT(title) FROM books;

--   +---------------------+
--   | GROUP_CONCAT(title) |
--   +---------------------+
--   | The T               |
--   +---------------------+

Alternatively, you can also limit the number of rows joined together by creating a subquery that uses the LIMIT clause.

Back to the books table, you can concatenate only the first three rows using the following SQL query:

SELECT GROUP_CONCAT(title)
  FROM (SELECT title FROM books LIMIT 3) AS titles;

With the subquery above, only the first 3 rows of the title column will be concatenated as shown below:

+------------------------------------------+
| GROUP_CONCAT(title)                      |
+------------------------------------------+
| The Three Musketeers,Stardust,The Hobbit |
+------------------------------------------+

Now you’ve learned how to change the limit of the GROUP_CONCAT() function in MySQL database server. Nice work! 👍