Shulou(Shulou.com)06/01 Report--

In mysql, there is a function called "group_concat", which may not be found in normal use. When processing large data, it will be found that the content has been intercepted. In fact, MYSQL has internal settings for this. The default length is 1024. If we need to be larger, we need to manually modify it.

Details are as follows:

After using group_concat, select does not work if limit is used. There is a length limit when using group_concat to connect fields, not how many there are. But you can set it up. Using the group_concat_max_len system variable, you can set the maximum allowed length. The system default delimiter is comma

Modification method:

SET [SESSION | GLOBAL] group_concat_max_len = 10240;

Parameters that can be modified are as follows

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

1. Let's start with a simple example.

select group_concat(f_a) from t_one group by f_b;

Group query according to f_b and accumulate f_a in each group.

2. Modify default delimiters

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

separator is a keyword followed by the character to be separated

3. sort

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

4. Modify default character size

1)Add MySQL configuration file

group_concat_max_len = 102400 #Maximum length you want

2)It can be a little bit simple, execute statements, you can set the scope of action

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

5. and concat use

group_concat returns BLOB large objects by default. You can use concat to return strings. You can also add other data to the returned content.

summary

The above is how to modify the length limit of group_concat in Mysql introduced by Xiaobian. I hope it will help you. If you have any questions, please leave a message to me. Xiaobian will reply to you in time. Thank you very much for your support!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.