MySQL column length maxes out at 1026 characters

user_21647 0
Viewed 399

My query in MySQL uses group_concat to convert values in multiple rows into a single column.

SELECT
    user_id,
    group_concat(case when event = 'BORROWED' then book_id end 
                 separator ' ') as borrowed_books
FROM library_events

The problem that I encounter is that the ad-hoc column borrowed_books into which the values are concatenated gets maxed out at 1026 characters. I have also seen that the string in the column gets truncated after 1026 characters.

How can I set/increase this value for the ad-hoc column?

mysql
1 Answers

Before calling the SELECT query, you can set the maximum value for group_concat_max_len to the maximum possible value, for this particular session:

SET SESSION group_concat_max_len = @@max_allowed_packet;

From Docs:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

user_24693080
Related