How to modify the length limit of the Group_conca function in MySQL

This article brings the content is about MySQL in the Group_conca function of how to modify its length limit, there is a certain reference value, the need for friends can refer to, I hope to help you.

In MySQL, there is a function called "Group_concat", the usual use may not find the problem, in the processing of big data, you will find that the content is intercepted,
In fact, MySQL internal to this is set, the default is not set the length is 1024, if we need to be larger, we need to manually modify

Detailed description is as follows:

After using Group_concat, the Select has no effect if the limit is used.

There is a length limit for connecting fields with Group_concat, not how many are connected. But you can set it up a bit.

Using the Group_concat_max_len system variable, you can set the maximum allowable length.

The system default delimiter is a comma

How to modify:

SET [SESSION | GLOBAL] Group_concat_max_len = 10240

The parameters that can be modified are as follows
Group_concat adds the value of a field to a specified number of characters, the system default delimiter is a comma, and the character length that can be summed is 1024 bytes.

1. Give a simple example first

Select Group_concat (f_a) from T_one Group by F_b;

Group queries by F_b, accumulating the f_a in each group.

2. Modify the default delimiter

Select Group_concat (f_a  Separator  ' _ ') from T_one Group by F_b;

Separator is a keyword followed by the character to be delimited

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) in the MySQL configuration file, add

Group_concat_max_len = 102400 #你要的最大长度

2) can be simple, execute the statement, you can set the scope

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

5, and Concat use
Group_concat default is to return a blob large object, you can use Concat, return the string, but also in the returned content, in addition to other data.