MySQL statistical function GROUP_CONCAT uses trap analysis, mysqlgroup_concat

MySQL statistical function GROUP_CONCAT uses trap analysis, mysqlgroup_concat

This article analyzes the traps used by the MySQL statistical function GROUP_CONCAT. We will share this with you for your reference. The details are as follows:

Recently, MySQL is used to pre-process some data. The group_concat function is often used, for example, similar to the following statement.
Copy codeThe Code is as follows: mysql> select aid, group_concat (bid) from tbl group by aid limit 1;

The SQL statement is relatively simple. It groups data by aid and concatenates bids with commas. You may have used such sentences and won't have any problems. But if there are many bids, you should be careful, such as the following prompt:

Query OK, XXX rows affected, 1 warning (3 min 45.12 sec)

How can we get a warning? Let's take a look:

mysql> show warnings;+---------+------+-----------------------------------------+| Level  | Code | Message                 |+---------+------+-----------------------------------------+| Warning | 1260 | 1 line(s) were cut by GROUP_CONCAT()  |+---------+------+-----------------------------------------+

GROUP_CONCAT truncated my results and checked the manual. It turns out that GROUP_CONCAT has a maximum length limit. If the maximum length is exceeded, it will be truncated. You can get it through the following statement:

mysql> SELECT @@global.group_concat_max_len;+-------------------------------+| @@global.group_concat_max_len |+-------------------------------+|           1024   |+-------------------------------+

1024 is the default maximum length of the MySQL system. If your bid string is larger than this, the problem may occur. Fortunately, there is a solution:

1. Add the following in the MySQL configuration file:

Group_concat_max_len = 102400 # maximum length you want

2. You can simply execute the statement:

mysql> SET GLOBAL group_concat_max_len=102400;Query OK, 0 rows affected (0.01 sec)

If you execute the group_concat statement again, no problem will occur. In addition, the detailed usage of group_concat is provided in the Manual. You will understand the following example:
Copy codeThe Code is as follows: mysql> select aid, group_concat (bid order by bid separator ',') as bid_str from tbl group by aid;
You can also sort and set Separators with powerful functions.