Usage of the group_concat function in mysql

I. Syntax
 

GROUP_CONCAT ([DISTINCT] expr [, expr...] [order by {unsigned_integer | col_name | expr} [ASC | DESC] [, col_name...] [SEPARATOR str_val])

 
It can also be simply understood
 

Group_concat ([DISTINCT] field to be connected [Order by asc/DESC sorting field] [Separator 'delimiter'])

 
II. Length settings of the group_concat connection field
 
1. View Settings on the server
 

Mysql> show variables like '% group_concat % ';
+ ---------------------- + ------- +
| Variable_name | Value |
+ ---------------------- + ------- +
| Group_concat_max_len| 1024 |
+ ---------------------- + ------- +
1 row in set (0.00 sec)

The value of the preceding settings indicates that the current default length is 1 kB.
 
2. Change the parameter value
Method 1: modify the parameters in the configuration file and add group_concat_max_len = 10240.

Method 2: implement in session, global or current session
 

Set global group_concat_max_len = 10240;
Set session group_concat_max_len = 10240;

 
III. Instance of group_concat function in mysql
 
1. Instance data
 

Select * from aa;

+ ------ +
| Id | name |
+ ------ +
| 1 | 10 |
| 1 | 20 |
| 1 | 20 |
| 2 | 20 |
| 3 | 200 |
| 3 | 500 |
+ ------ +
6 rows in set (0.00 sec)

 
2. Group by id. Print the value of the name field in one row and separate them with commas (, by default)
 

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)

 
3. Group by id and print the value of the name field in one row, separated by semicolons
 
 
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)

 
4. Group by id and print the value of the redundant name field in one row, separated by commas (,).
 
 
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)

 
5. Group by id. Print the value of the name field in one row and separate them by commas (,). Sort the values by name in reverse order.
 
 
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)

Example

The data result I want to query is:

We can establish an internal connection:

Select s. id, s. name, srt. typeId, stt. name as TypeName from scenics as s
Inner join (
Select * from scenics_type
) Srt on srt. scenicsId = s. id
Inner join (
SELECT * from scenicstype where deleted = 0
) Stt on stt. id = srt. typeId

The result is:

Obviously, we need to group and merge the data.

Select s. id, s. name, srt. typeId, stt. name as TypeName from scenics as s
Inner join (
Select * from scenics_type
) Srt on srt. scenicsId = s. id
Inner join (
SELECT * from scenicstype where deleted = 0
) Stt on stt. id = srt. typeId
Group by (s. id)
 

The result is:

However, TypeName is used to accumulate the expected results.

MySQL provides us with a good way to achieve this goal. Group_concat

Select s. id, s. name, srt. typeId, group_concat (stt. name SEPARATOR ',') as TypeName from scenics as s
Inner join (
Select * from scenics_type
) Srt on srt. scenicsId = s. id
Inner join (
SELECT * from scenicstype where deleted = 0
) Stt on stt. id = srt. typeId
Group by (s. id)

The result is as follows:

OK!
 
IV. Considerations for using group_concat in mysql
 
1. Connection trap of the int field
If the joined field is of the int type, it must be converted to char and then combined. Otherwise, the returned result is not a string separated by commas (,), but byte [].
 
-- For example, id is an integer.

Select group_concat (id) from t_ip return byte []

Select group_concat (CAST (id as char) from t_dep returns comma-separated strings

Select group_concat (Convert (id, char) from t_dep returns comma-separated strings

 
2. The length of group_concat is limited.

When using group_concat to connect a field, there is a limit on the length, not the number of connections. However, this length can be set. You can use the group_concat_max_len system variable. You can set the maximum length allowed.