MySQL多行合并 - GROUP_CONCAT 详细教程

A kitten
蒋川
B 端数据开发,卡拉云联合创始人
最近更新 2022年02月09日

MySQL多行合并 - GROUP_CONCAT 详细教程

有时我们希望将某一列的多行数据合并到一起,用某些分隔符隔开(比如逗号或者分号)。这样子的字段合并,会需要用到 GROUP_CONCAT 函数。本文就详细介绍如何用 GROUP_CONCAT 函数在 MySQL 中实现多行合并。

MySQL 中什么时候需要多行合并

对于使用场景的问题,我们用一个例子来说明。

假设你现在有一个表,其中有用户名,手机号和地区三个字段,即

user_name, phone, location
a1, 13985336666, 成都
a2, 13811112222, 重庆
a3, 13988887777, 成都

现在你需要给所有在成都的用户发一条提醒短信。你的短信服务商需要你把手机号组合成 phone1|phone2|phone3 的形式,也就是把电话号码用竖线隔开。所以这时你现在需要把数据选出来,格式为

重庆, 13811112222
成都, 13985336666|13988887777

这样的场景下,我们就可以使用 GROUP_CONCAT,将选出来的多个数组合并到一起,并指定一个分隔符。

如果需要跟着本教程执行 SQL 的话,我们假设你已经安装好 MySQL 且有权限执行命令。

什么是 GROUP_CONCAT

MySQL 的 GROUP_CONCAT() 函数是一个聚合函数,它将一个集合中的字符串用你指定的分隔符,连接成一个单一的字符串。

下面展示 GROUP_CONCAT()函数的语法

GROUP_CONCAT(
    DISTINCT expression
    ORDER BY expression
    SEPARATOR sep
);

使用 GROUP_CONCAT 的例子

假设我们有一个用户表如下

用户表

可以看到用户的信息中(列中)有用户的电话,名字,城市,国家等信息。那么,如果我们需要将所有用户的国家信息列合并起来,并用逗号隔开的话,就可以用以下命令:

SELECT 
    GROUP_CONCAT(country)
FROM
    customers;

运行后的结果为

Australia,Australia,Australia,Australia,Australia,Austria,Austria,Belgium,Belgium,Canada,Canada,Canada,Denmark,Denmark,Finland,Finland,Finland,France,France,France,France,France,France,France,France,France,France,France,France,Germany,Germany,Germany,Germany,Germany,Germany,Germany,Germany,Germany,Germany,Germany,Germany,Germany,Hong Kong,Ireland,Ireland,Israel,Italy,Italy,Italy,Italy,Japan,Japan,Netherlands,New Zealand,New Zealand,New Zealand,New Zealand,Norway,Norway  ,Norway  ,Philippines,Poland,Portugal,Portugal,Russia,Singapore,Singapore,Singapore,South Africa,Spain,Spain,Spain,Spain,Spain,Spain,Spain,Sweden,Sweden,Switzerland,Switzerland,Switzerland,UK,UK,UK,UK,UK,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA,USA

请注意,这里并没有使用 GROUP 先把数据聚合,也就是说 GROUP_CONCAT 其实是可以对全表直接使用的。

可以看到有些用户来自同一个国家,比如我们有很多来自 Australia 澳大利亚的用户。如果需要将重复的国家去掉,那么可以用

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

在 DISTINCT 选项中指定 country 列。这样一来结果就变成了

Australia,Austria,Belgium,Canada,Denmark,Finland,France,Germany,Hong Kong,Ireland,Israel,Italy,Japan,Netherlands,New Zealand,Norway,Philippines,Poland,Portugal,Russia,Singapore,South Africa,Spain,Sweden,Switzerland,UK,USA

注意这里还顺手把国家按字符序排了个序,方便阅读。

GROUP_CONCAT 默认的连接符是逗号,如果需要换一个连接符号,比如用竖线的话,可以用

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

执行的结果变为:

Australia|Austria|Belgium|Canada|Denmark|Finland|France|Germany|Hong Kong|Ireland|Israel|Italy|Japan|Netherlands|New Zealand|Norway|Philippines|Poland|Portugal|Russia|Singapore|South Africa|Spain|Sweden|Switzerland|UK|USA

这里推荐一下可与 MySQL 搭配使用的卡拉云。卡拉云是一套低代码开发工具,可接入常见的数据库(MySQL、MongoDB等)及 RESTful API, 你无需懂会任何前端技术,只要会写 SQL,就可以快速搭建属于你自己的数据库管理工具。>> 深入了解卡拉云 <<

连接数据库后需要开发后台系统?

试试卡拉云,拖拽组件连接 API 和数据库直接生成后台系统,两个月的工期降低至1天

如何在 GROUP BY 后配合使用 GROUP_CONCAT

回到文首的问题,怎样将一个地区的用户 GROUP 起来,然后把他们的电话号码连起来方便发短信呢?很简单,我们将 GROUP BY 和 GROUP_CONCAT 配合使用即可。即:我们先 GROUP BY,然后在聚集集合中使用 GROUP_CONCAT 如下


SELECT country,
         GROUP_CONCAT(phone, "|")
FROM customers
GROUP BY  country;

请注意,这里我们按 country 先 group by,然后用竖线把电话号码连了起来。输出为:

GROUP_CONCAT 结果

使用 GROUP_CONCAT 的常见错误

GROUP_CONCAT 函数返回的是一个单一的字符串,而不是一个值的列表,这意味着你不能将 GROUP_CONCAT 函数的结果用于 IN 操作符,特别是在子查询中使用。

比如说,GROUP_CONCAT 函数以 '1,2,3' 字符串的形式返回值:1 2和3的结果。

如果你把这个结果提供给IN操作符,那么查询就无法进行,因此,查询可能悄悄地不会返回任何结果。以下是 错误 的查询语法,不要将 GROUP_CONCAT 用在 IN 中!

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

GROUP_CONCAT 其它场景

假设你的用户都有用户角色,admin/user 之类,你希望得到所有用户角色以逗号分隔的列表,如'admin, author, editor'。

将用户的爱好,如'设计、编程、阅读'等标签组合起来。

为博客文章、文章或产品创建标签,例如,'mysql、mysql聚合函数、mysql教程' 等等

尝试卡拉云

如果你正在数据库基础上搭建自己的后台工具,可以试试卡拉云。只要你会写 SQL,完全不用懂前端,简单拖拽,即生成表格、搜索框和按钮组件,你也可以快速把这些组件连接到你的数据库上,快速搭建属于你自己的后台管理工具。数月的开发工作量,使用卡拉云后可缩减至数天。

本文参考:

https://www.mysqltutorial.org/mysql-group_concat/

https://www.cnblogs.com/zelzzz/p/7496884.html