How to modify the length limit of group_concat in Mysql

In MySQL, there is a function called "group_concat". You may not find any problems in normal use. When processing big data, you will find that the content is intercepted. In fact, MYSQL has settings for this. The default length is 1024. If we need a larger length, we need to modify it manually.

The detailed instructions are as follows:

• After using group_concat, if limit is used in select, it will not work.
• There is a length limit when using group_concat to concatenate fields. You cannot concatenate as many fields as you want. But you can set it up.
• Using the group_concat_max_len system variable, you can set the maximum allowed length.
• The default separator is comma.

Modification method:

SET [SESSION | GLOBAL] group_concat_max_len = 10240;

The editable parameters are as follows:

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

1. Let’s take a simple example

select group_concat(f_a) from t_one group by f_b;

Perform group query by f_b and accumulate 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 character to be used as separator

3. Sorting

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 simpler, execute statements, and set the scope

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

5. Use with concat

By default, group_concat returns a BLOB object. You can use concat to return a string and add other data to the returned content.

Summarize

The above is what I introduced to you on how to modify the length limit of group_concat in Mysql. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!