Resumo : neste tutorial você aprenderá como usar a GROUP_CONCAT()
função MySQL para concatenar strings de um grupo com diversas opções.
Introdução à função MySQL GROUP_CONCAT()
A função MySQL GROUP_CONCAT()
é uma função agregada que concatena strings de um grupo em uma única string com várias opções.
O seguinte mostra a sintaxe da GROUP_CONCAT()
função:
GROUP_CONCAT(
DISTINCT expression
ORDER BY expression
SEPARATOR sep
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
O exemplo a seguir demonstra como a GROUP_CONCAT()
função funciona.
CREATE TABLE t (
v CHAR
);
INSERT INTO t(v) VALUES('A'),('B'),('C'),('B');
SELECT
GROUP_CONCAT(DISTINCT v
ORDER BY v ASC
SEPARATOR ';')
FROM
t;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A DISTINCT
cláusula permite eliminar valores duplicados no grupo antes de concatená-los.
A ORDER BY
cláusula permite classificar os valores em ordem crescente ou decrescente antes de concatenar. Por padrão, ele classifica os valores em ordem crescente. Se quiser classificar os valores em ordem decrescente, você precisará especificar explicitamente a DESC
opção.
Especifica SEPARATOR
um valor literal inserido entre valores no grupo. Se você não especificar um separador, a GROUP_CONCAT
função usará uma vírgula ( ,
) como separador padrão.
A GROUP_CONCAT
função ignora NULL
valores. Ele retorna NULL
se não houver linhas correspondentes ou se todos os argumentos forem NULL
valores.
A GROUP_CONCAT
função retorna uma string binária ou não binária, que depende dos argumentos.
Por padrão, o comprimento máximo da string de retorno é 1024. Se precisar de mais do que isso, você pode estender o comprimento máximo definindo a group_concat_max_len
variável do sistema no nível SESSION
ou GLOBAL
.
Exemplos de funções MySQL GROUP_CONCAT()
Vamos dar uma olhada na customers
tabela no banco de dados de exemplo :
Para obter todos os países onde os clientes estão localizados como uma string separada por vírgula, use a GROUP_CONCAT()
função da seguinte maneira:
SELECT
GROUP_CONCAT(country)
FROM
customers;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
No entanto, alguns clientes estão localizados no mesmo país. Para remover os nomes dos países duplicados, adicione a DISTINCT
cláusula como a seguinte consulta:
SELECT
GROUP_CONCAT(DISTINCT country)
FROM
customers;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
É mais legível se os nomes dos países estiverem em ordem crescente. Para classificar o nome do país antes de concatenar, use a ORDER BY
cláusula a seguir:
SELECT
GROUP_CONCAT(DISTINCT country
ORDER BY country)
FROM
customers;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Para alterar o separador padrão da string retornada de vírgula (,) para ponto e vírgula (;), use a SEPARATOR
cláusula como a seguinte consulta:
SELECT
GROUP_CONCAT(DISTINCT country
ORDER BY country
SEPARATOR ';')
FROM
customers;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Ótimo! agora você sabe como a GROUP_CONCAT()
função funciona. Vamos colocar isso em um exemplo prático.
Cada cliente tem um ou mais representantes de vendas. Em outras palavras, cada vendedor é responsável por um ou mais clientes. Para descobrir quem é responsável por quais clientes, você usa a cláusula inner join da seguinte forma:
SELECT
employeeNumber,
firstname,
lastname,
customername
FROM
employees
INNER JOIN
customers ON customers.salesRepEmployeeNumber = employees.employeeNumber
ORDER BY
firstname,
lastname;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Agora podemos agrupar o resultado definido pelo número do funcionário e concatenar todos os funcionários que estão a seu cargo utilizando a GROUP_CONCAT()
função da seguinte forma:
SELECT
employeeNumber,
firstName,
lastName,
GROUP_CONCAT(DISTINCT customername
ORDER BY customerName)
FROM
employees
INNER JOIN
customers ON customers.salesRepEmployeeNumber = employeeNumber
GROUP BY employeeNumber
ORDER BY firstName , lastname;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
O conjunto de resultados é muito mais fácil de ler.
Usando GROUP_CONCAT() com exemplo de função CONCAT_WS()
Às vezes, a GROUP_CONCAT
função pode ser combinada com a função CONCAT_WS para tornar o resultado da consulta mais útil.
Por exemplo, para fazer uma lista de valores de clientes separados por ponto e vírgula:
- Primeiro, você concatena o sobrenome e o nome do contato de cada cliente usando a
CONCAT_WS()
função. O resultado é o nome completo do contato. - Então, você usa a
GROUP_CONCAT()
função para fazer a lista.
A consulta a seguir cria uma lista de valores de clientes separados por ponto e vírgula.
SELECT
GROUP_CONCAT(
CONCAT_WS(', ', contactLastName, contactFirstName)
SEPARATOR ';')
FROM
customers;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Observe que GROUP_CONCAT()
a função concatena valores de string em linhas diferentes, enquanto a função CONCAT_WS()
ou CONCAT()
concatena dois ou mais valores de string em colunas diferentes.
Função GROUP_CONCAT: erros comuns
A GROUP_CONCAT()
função retorna uma única string, não uma lista de valores. Isso significa que você não pode usar o resultado da GROUP_CONCAT()
função para o operador IN , por exemplo, dentro de uma subconsulta .
Por exemplo, a GROUP_CONCAT()
função retorna o resultado dos valores: 1
2
e 3
como a string ‘1,2,3’.
Se você fornecer esse resultado ao IN
operador, a consulta não funcionará. Portanto, a consulta pode não retornar nenhum resultado. Por exemplo, a consulta a seguir não funcionará conforme desejado.
Porque o IN
operador aceita uma lista de valores, por exemplo, (1,2,3), não uma string que consiste em uma lista de valores (‘1,2,3’). Como resultado, a consulta a seguir não funcionará conforme o esperado.
SELECT
id, name
FROM
table_name
WHERE
id IN GROUP_CONCAT(id);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como a GROUP_CONCAT
função é uma função agregada , para classificar os valores, você deve usar a ORDER BY
cláusula dentro da função, não na ORDER BY
instrução SELECT .
O exemplo a seguir demonstra o uso incorreto da ORDER BY
cláusula no contexto de uso da GROUP_CONCAT
função:
SELECT
GROUP_CONCAT(DISTINCT country
SEPARATOR ';')
FROM
customers
ORDER BY country;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A SELECT
cláusula retorna um valor de string para que a ORDER BY
cláusula não tenha nenhum efeito nesta instrução.
Aplicativos de função MySQL GROUP_CONCAT()
Existem muitos casos em que você pode aplicar a GROUP_CONCAT()
função para produzir resultados úteis. A lista a seguir contém alguns exemplos comuns de uso da GROUP_CONCAT()
função.
- Crie funções de usuário separadas por vírgula, como ‘administrador, autor, editor’.
- Produza os hobbies do usuário separados por vírgula, por exemplo, ‘design, programação, leitura’.
- Crie tags para postagens de blog, artigos ou produtos, por exemplo, ‘mysql, função agregada mysql, tutorial mysql’.
Neste tutorial, você aprendeu como usar a GROUP_CONCAT()
função MySQL para concatenar não- NULL
valores de um grupo de strings em uma única string.