How to modify the length limit of group_concat in Mysql

In mysql, there is a function called "group_concat", which may not find problems when used normally. When dealing with big data, it will be found that the content is intercepted. In fact, there is a setting for this in MYSQL, and the default length is 1024. If we need to be larger, we need to modify it manually

Details are as follows:

After using group_concat, select does not work if limit is used. When connecting fields with group_concat, there is a length limit, not as many as there are. But you can set 1. Using the group_concat_max_len system variable, you can set the maximum length allowed. The system default separator is a comma

Modification method:

SET [SESSION | GLOBAL] group_concat_max_len = 10240;

The parameters that can be modified are as follows

GROUP_CONCAT accumulates the value of a 1 field by specified characters. The default separator is comma, and the length of characters that can be accumulated is 1024 bytes.

1. Start with a simple example

select group_concat(f_a) from t_one group by f_b;

Group queries by f_b, adding up f_a in each group.

2. Modify the default separator

select group_concat(f_a separator '_') from t_one group by f_b;

separator is a keyword followed by the characters to be separated

3. Sort

select group_concat(f_a order by f_a separator '_') from t_one group by f_b;

4. Modify the default character size

1) Add to the MySQL configuration file

group_concat_max_len = 102400 # The maximum length you want 

2) It can be simple, execute statements, and set the scope of action

 SET GLOBAL group_concat_max_len=102400;
 SET SESSION group_concat_max_len=102400;

5. Use with concat

group_concat returns BLOB large objects by default. concat can be used to return strings, and other data can be added in the returned content.

Summarize