Deep understanding of the group_concat function in MySQL

This article introduces how to use the group_concat function in MySQL through examples, such as select group_concat (name ).
Group_concat function in MySQL
The complete syntax is as follows:
Group_concat ([DISTINCT] field to be connected [Order by asc/DESC sorting field] [Separator 'delimiter'])
Basic Query
Mysql> select * from aa;
+ ------ +
| Id | name |
+ ------ +
| 1 | 10 |
| 1 | 20 |
| 1 | 20 |
| 2 | 20 |
| 3 | 200 |
| 3 | 500 |
+ ------ +
6 rows in set (0.00 sec)
Group by id and print the value of the name field in one row, separated by commas (, default)
Mysql> select id, group_concat (name) from aa group by id;
+ ------ + -------------------- +
| Id | group_concat (name) |
+ ------ + -------------------- +
| 1 | 10, 20, 20 |
| 2 | 20 |
| 3 | 200,500 |
+ ------ + -------------------- +
3 rows in set (0.00 sec)
Group by id, print the value of name field in one row, separated by semicolons
Mysql> select id, group_concat (name separator ';') from aa group by id;
+ ------ + ------------------------------------ +
| Id | group_concat (name separator ';') |
+ ------ + ------------------------------------ +
| 1 | 10; 20; 20 |
| 2 | 20 |
| 3 | 200, 500 |
+ ------ + ------------------------------------ +
3 rows in set (0.00 sec)
Group by id and print the value of the redundant name field in one row,
Separated by commas
Mysql> select id, group_concat (distinct name) from aa group by id;
+ ------ + ----------------------------- +
| Id | group_concat (distinct name) |
+ ------ + ----------------------------- +
| 1 | 10, 20 |
| 2 | 20 |
| 3 | 200,500 |
+ ------ + ----------------------------- +
3 rows in set (0.00 sec)
Group by id, print the value of the name field in one row, and separate the values with commas (,). Sort the values by name in reverse order.
Mysql> select id, group_concat (name order by name desc) from aa group by id;
+ ------ + --------------------------------------- +
| Id | group_concat (name order by name desc) |
+ ------ + --------------------------------------- +
| 1 | 20, 20, 10 |
| 2 | 20 |
| 3 | 500,200 |
+ ------ + --------------------------------------- +
3 rows in set (0.00 sec)
Using group_concat_max_len system variables, you can set the maximum allowed length. The syntax for this operation in the program is as follows, where val is an unsigned integer:
SET [SESSION | GLOBAL] group_concat_max_len = val;
If the maximum length has been set, the result is ended with the maximum length.
Increase the environment variable group_concat_max_len. The default value is 1024. I set the session-level environment variable to 2048 (if not enough, increase the value ). Solve this problem