Detailed explanation of MySQL string concatenation function GROUP_CONCAT

In the previous article, I wrote a cross-table update. When I saw the SQL I wrote, I felt like a fool and wrote about cross-table updates. A year passed quickly, and the two employees馮大and馮二who joined later in the article also had to face the ruthless KPI assessment. They did a good job, with performance scores of 4 and 5 respectively.

New demands are coming, coming quietly! ! ! The leader wants to see who is under each performance, and at the same time requires that the names of these people be concatenated into a string with commas, that is, to get the following result:

How do you concatenate strings for a specified column in the result set? The protagonist shines brightly✨

GROUP_CONCAT(expr)

In the official MySQL documentation, this function is placed in the aggregate function section. If you want to group and concatenate by a specified field, you must use it with the keyword GROUP BY .

definition

The function returns a string result that is the concatenation of非NULL值​​by grouping. If there is no non-NULL value, returns NULL. The complete syntax is as follows:

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

What? This syntax looks too complicated. Don’t worry. I will use examples to explain and verify it one by one.

Use Cases

First complete the requirements at the beginning of the article:

SELECT performance, GROUP_CONCAT(employee_name) AS employees
FROM employees
GROUP BY performance;

Zou is the result:

At this point, the requirements given by the leader have been completed😜

Sir, please stay, the dishes you ordered have not been served yet...

We are an international team, our hometowns are all over the world

The leader wants to care about his employees and wants to check where the hometowns of all the employees in the company are. Employees may come from the same place, so to remove duplicates from the result set, the DISTINCT keyword comes in handy.

SELECT GROUP_CONCAT(DISTINCT home_town)
FROM employees;

Let’s look at the results:

The care of leaders is everywhere...

The copywriting needs to be changed. The leader's care is四海五湖, so ORDER BY keyword comes in handy.

SELECT GROUP_CONCAT(DISTINCT home_town ORDER BY home_town DESC) AS 'Leadership Care Area'
FROM employees;

-- No one uses variables like me, and it's Chinese. I think you are crazy. 

Here you can see that the default separator for the GROUP_CONCAT function is a comma , The boss is unhappy, and the comma is not a good expression. You need to use ❕ to show the strong care. SEPARATOR keyword comes in handy.

The default separator between grouped values ​​is a comma (,). To specify the delimiter explicitly, use the SEPARATOR keyword followed by the delimiter you want to set. To completely eliminate the separator, just write '' after SEPARATOR keyword.

SELECT GROUP_CONCAT(DISTINCT home_town ORDER BY home_town DESC SEPARATOR '!') AS 'Leadership Care Region'
FROM employees;

SELECT GROUP_CONCAT(DISTINCT home_town SEPARATOR '') AS 'Leadership Care Area'
FROM employees;

This is quite caring, right? Think about it carefully! ! !

The leader's caring ability is also limited. The default maximum length of the concatenated string is 1024 characters. You can view the current limit through the following statement:

show variables like 'group_concat_max_len';

The ability of a leader is volatile, so we can set this value flexibly.

SET [GLOBAL | SESSION] group_concat_max_len = val;

SESSION: effective in the current session GLOBAL: effective globally

After this statement is executed, it will take effect until MySQL is restarted. Once MySQL is restarted, the default value will be restored.

Sometimes GROUP_CONCAT() needs to be used with CONCAT_WS() to bring out a little more power. Here is a simple example:

Separate the consumer's first and last name with a comma, and then with a ;

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

Here is the CONCAT_WS() function usage, it is very simple, please check it out for yourself...

Note ⚠️

The GROUP_CONCAT() function returns a single string, not a list of values. This means that we cannot use the result of the GROUP_CONCAT() function in an IN operator, for example, in a subquery, like this:

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

Summarize

In many cases, we can use the GROUP_CONCAT() function to produce useful results, and we can also combine it with other functions to achieve greater power.