본문으로 건너뛰기

MySQL GROUP_CONCAT() Function

The `GROUP_CONCAT()` function in MySQL concatenates values from multiple rows into a single string, grouped by a specified column. This function is particularly useful for creating comma-separated lists from grouped results.

Usage

`GROUP_CONCAT()` is used when you need to aggregate string data from multiple rows into a single row. It is typically used alongside the `GROUP BY` clause to provide meaningful grouped results. If `GROUP BY` is omitted, `GROUP_CONCAT()` will concatenate all rows into a single string across the entire dataset.

sql
GROUP_CONCAT([DISTINCT] expression [ORDER BY expression ASC|DESC] [SEPARATOR 'separator'])

In this syntax, `expression` represents the column or value to be concatenated, and `SEPARATOR` defines the delimiter between concatenated values. The default separator is a comma.

Examples

1. Basic Concatenation

sql
SELECT GROUP_CONCAT(first_name) 
FROM employees;

This example concatenates all `first_name` values from the `employees` table into a single string, separated by commas.

2. Grouped Concatenation

sql
SELECT department, GROUP_CONCAT(last_name) 
FROM employees
GROUP BY department;

Here, the `GROUP_CONCAT()` function concatenates `last_name` values for each `department`, generating a comma-separated list of last names per department.

3. Ordered and Custom Separator

sql
SELECT department, GROUP_CONCAT(last_name ORDER BY last_name ASC SEPARATOR '; ')
FROM employees
GROUP BY department;

This example not only groups last names by department but also orders them alphabetically within each group and uses a semicolon and space as the separator.

Tips and Best Practices

  • Limit output length. Use `SET SESSION group_concat_max_len = value;` to adjust the maximum length of the result string to avoid truncation.
  • Use DISTINCT wisely. Apply `DISTINCT` within `GROUP_CONCAT()` if you need to eliminate duplicate values in concatenated results.
  • Select appropriate separators. Choose a separator that doesn’t conflict with the data content to ensure clarity and consistency.
  • Combine with ORDER BY. Utilize `ORDER BY` within `GROUP_CONCAT()` to control the order of concatenated values, enhancing readability.
  • Ensure proper grouping. Always use `GROUP BY` with `GROUP_CONCAT()` to correctly aggregate values by the desired category.
  • Consider performance. Be aware of potential performance implications when using `GROUP_CONCAT()` on large datasets, as it may increase processing time and resource usage.