MySQL多行合并 - GROUP_CONCAT 详细教程

卡拉先生
发布于 2020年09月04日 | 上次编辑:2020年09月05日

header

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

如果你在尝试构建 MySQL 数据库上的搜索功能,可以尝试卡拉搜索,无需配置维护即可实现毫秒级搜索,同时后台可轻松控制排序算法让运营和产品可以轻松调整,降低开发负担

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

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

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

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

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

重庆, 13811112222
成都, 13985336666|13988887777

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

如果需要跟着本教程执行 SQL 的话,我们假设你已经安装好 MySQL 且有权限执行命令。如果没有的话请参考MySQL 安装教程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

如何在 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教程' 等等

尝试卡拉搜索

数据库的查询功能主要是为了快速查询已定义好的数据,如果需要全文索引,请继续关注之后我们的全文索引教程。但如果你刚好在尝试搭建数据库上的全文索引或 APP 内搜索功能的话,可以尝试卡拉搜索。几行代码就可以接入 MySQL 数据库中,极大程度免去搭建和维护复杂搜索系统的烦恼

推荐扩展阅读

CentOS 如何安装 MySQL

MySQL 中如何创建新用户并授权

ElasticSearch 中文配置教程

本文参考:

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

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

友情链接更新日志© 2020, 卡拉搜索, Built with ❤️ in San Francisco + Beijing

京ICP备15049164号-3