How to modify the length limit of the group_conca function in mysql

The content of this article is about how to modify the length limit of the group_conca function in mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

In mysql, there is a function called "group_concat". You may not find the problem during normal use. When processing big data, you will find that the content has been intercepted.
In fact, MYSQL has internal control over this It is set. The default unset length is 1024. If we need it to be larger, we need to modify it manually.

The details are as follows:

After using group_concat, if you use it in select limit does not work.

When using group_concat to connect fields, there is a length limit, not how many connections there are. But you can set it up.

Using the group_concat_max_len system variable, you can set the maximum length allowed.

The system default separator is comma

Modification method:

SET [SESSION | GLOBAL] group_concat_max_len = 10240

The parameters that can be modified are as follows
GROUP_CONCAT changes the value of a certain field according to the specified Characters are accumulated. The system default separator is comma, and the length of characters that can be accumulated is 1024 bytes.

1. Let’s take a simple example first

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 characters to be separated

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

 group_concat_max_len = 102400 #你要的最大长度

2 to the MySQL configuration file. It can be simpler. Execute the statement and set it. Scope

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

5. Use with concat
group_concat returns a BLOB large object by default. You can use concat to return a string, and you can also add other data to the returned content.

Related recommendations:

Usage of count() in large mysql tables and optimization of count() in mysql

Unlimited in mysql Extremely classified code implementation

The above is the detailed content of How to modify the length limit of the group_conca function in mysql. For more information, please follow other related articles on the PHP Chinese website!