MySQL GROUP_CONCAT函数

Summary: in this tutorial, you will learn how to use the MySQL GROUP_CONCAT function to concatenate strings from a group of values with various options.

Introduction to MySQL GROUP_CONCAT function

The GROUP_CONCAT function concatenates strings from a group into one string with various options.

The following illustrates the GROUP_CONCAT function:

GROUP_CONCAT(DISTINCT expression ORDER BY {column_name | usinged_integer | expression} SEPARATOR sep);

Let’s examine the syntax of the GROUP_CONCAT function in more detail:

• You specify the DISTINCT clause to eliminate duplicate values in a group before combining values.

• The ORDER BY clause allows you to sort the values in ascending or descending order before concatenating values. The ORDER BY clause sort the values in ascending ( ASC) order by default. If you want to sort the values in the descending order, you need to specify the DESC explicitly.

• The SEPARATOR specifies a literal value inserted between values in the group. If you do not specify a separator, the GROUP_CONCAT function use a comma (,) as the default separator.

• The GROUP_CONCAT function ignores NULL values. It returns NULL if there was no matching row found or there were no non-NULL values. The GROUP_CONCAT function returns a binary or non-binary string, which depends on the arguments. The maximum length of the return string is 1024 by default. You can extend the returned value’s length by setting the group_concat_max_lensystem variable at SESSION or GLOBAL level.

MySQL GROUP_CONCAT Examples

Let’s take a look at the customers table in the sample database.

To get all countries where customers locate as a comma-separated string, you use the GROUP_CONCAT function as follows:

SELECT GROUP_CONCAT(country) FROM customers;

However, some customers locate in the same country. To remove the duplicate country’s names, you add the DISTINCT clause as the following query:

SELECT GROUP_CONCAT(DISTINCT country) FROM customers;

It is more readable if the country’s names are in ascending order. To sort the country’s name before concatenating, you use the ORDER BY clause as follows:

SELECT GROUP_CONCAT(DISTINCT country ORDER BY country) FROM customers;

To change the default separator of the returned string from a comma (,) to a semi-colon (;), you use the SEPARATORclause as the following query:

SELECT GROUP_CONCAT(DISTINCT country ORDER BY country SEPARATOR ';') FROM customers;

MySQL GROUP_CONCAT with CONCAT_WS function example

The CONCAT_WS concatenates strings with a specified separator. You can combine the GROUP_CONCAT function with the CONCAT_WS function to make a useful result.

For example, you can concatenate the last name and first name of each customer’s contact by using the CONCAT_WSfunction. The result is the contact’s full name. Then, you create a semicolon-separated string that contains all contact’s full names as the following query:

SELECT GROUP_CONCAT( CONCAT_WS(', ',contactLastName,contactFirstName) SEPARATOR ';') FROM customers;

Notice that GROUP_CONCAT function concatenates values from different rows, while the CONCAT_WS or CONCAT function joins two or more values.

MySQL GROUP_CONCAT function common mistakes

There are some common mistakes when using the GROUP_CONCAT function.

The GROUP_CONCAT function returns a single string. It means you cannot use it with IN operator e.g., within a subquery.

For example, the GROUP_CONCAT function returns the result of 1, 2, and 3 as '1, 2, 3' string. If you use this result with the IN operator, it would be IN ('1,2,3'), while the correct one must be IN (1,2,3), therefore the query may not return any result. For example, the following query will not work as desired.

SELECT id, name FROM table_name WHERE id IN GROUP_CONCAT(id) ;

The GROUP_CONCAT function is an aggregate function. To sort the values, you must use the ORDER BY clause inside the function, not in the ORDER BY after the FROM or WHERE clause in the SELECT statement. The following example demonstrates the incorrect usage of the ORDER BY clause in the context of using the GROUP_CONCAT function:

SELECT GROUP_CONCAT(DISTINCT country  SEPARATOR ';') FROM customers ORDER BY country;

The SELECT clause returns one string value so the ORDER BY clause does not take any effect in this statement.

MySQL GROUP_CONCAT applications

There are many cases where you can apply the GROUP_CONCAT function to produce useful results. The following list is some common examples of the using the GROUP_CONCAT function.

• Use GROUP_CONCAT function to make a comma-separated user’s roles such as ‘admin,author,editor’.

• Use GROUP_CONCAT function to return the comma-separated user’s hobbies e.g., ‘design,programming,reading’.

• Use GROUP_CONCAT function to build a tag for blog posts, articles or products e.g., ‘mysql, mysql aggregate function, mysql tutorial’.

In this tutorial, we have introduced you to MySQL GROUP_CONCAT function that concatenates non- NULL values from a group of strings into a string with various options.

原文链接：http://outofmemory.cn/mysql/function/mysql-group_concat