Mysql statistical function Group_concat using trap analysis _mysql

This paper analyzes the traps in the use of MySQL statistic function group_concat. Share to everyone for your reference, specific as follows:

Recently in the use of MySQL to do some data preprocessing, often use the Group_concat function, such as a statement like the following

Copy Code code as follows:

Mysql>select Aid,group_concat (BID) from the TBL Group by aid limit 1;

SQL statements are relatively simple, grouped by aid, and the corresponding bid are strung together with commas. Such sentences may be used or may not be a problem, but if the bid is very much, you should be careful, such as the following message:

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

How could there be a warning, come out and see:

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

was Group_concat truncated my results, check the manual, the original group_concat has a maximum length limit, exceeding the maximum length will be truncated, you can use the following statement to obtain:

Mysql> SELECT @ @global. Group_concat_max_len;
+-------------------------------+
| @ @global. group_concat_max_len |
+-------------------------------+
|           1024   |
+-------------------------------+

1024 This is the general MySQL system default maximum length, if your bid string more than this will have problems, fortunately there are solutions:

1. In the MySQL configuration file, add

Group_concat_max_len = 102400 #你要的最大长度

2. It can be simpler to execute the statement:

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

The execution of the GROUP_CONCAT statement will not be a problem, and the manual also gives the Group_concat detailed usage, for example you will understand:

Copy Code code as follows:

Mysql>select Aid,group_concat (Bid order by bid separator ', ') as Bid_str to TBL Group by aid;

You can also sort and set delimiters, which are powerful.

More information about MySQL interested readers can view the site topics: "MySQL Log operation skills Daquan", "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL commonly used function large summary"

I hope this article will help you with the MySQL database meter.