How to Use the MySQL group_concat Function

The MySQL group_concat function is a typical string connection function. The following describes the syntax of MySQL group_concat. It is helpful for you to learn MySQL group_concat function.

MySQL group_concat Function
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)  
  
  
 
 
 

 

Use of MySQL concat Functions

MySQL primary key design principles

Advantages of MySQL partitioning

Restrictions on primary key constraints in Mysql partition tables

Example of defining MySQL transactions