MySQL GROUP_CONCAT 函数详解,一行代码搞定行转列拼接!

MySQL GROUP_CONCAT 函数详解,一行代码搞定行转列拼接!

做报表的时候,经常遇到这种需求:同一个部门下面有多个人,你想把人名拼成一个逗号分隔的字符串MySQL GROUP_CONCAT 函数详解,一行代码搞定行转列拼接!,一行搞定。以前你可能用子查询或者代码里循环拼,其实 MySQL 自带一个函数就能干这活——。

下面把 的语法、参数、常见用法和踩坑点挨个过一遍,看完直接上手用。

一、 是什么?

是 MySQL 的一个聚合函数,和 COUNT、SUM 是同一类。区别在于:COUNT 数数,SUM 求和mysql nvarchar max, 把分组内的值拼成一个字符串。

说白了就是:同一组的数据,拼成一串。

执行流程可以参考下图:

二、语法结构

先看完整的语法:

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

语法结构图:

四个部分:

三、准备测试数据

后面的例子都用这组数据,建表语句和初始化数据如下:

CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(30) NOT NULL,
    salary DECIMAL(10,2)
);
INSERT INTO employee (name, department, salary) VALUES
('张三', '技术部', 15000),
('李四', '技术部', 18000),
('王五', '销售部', 12000),
('赵六', '技术部', 15000),
('钱七', '销售部', 16000),
('孙八', '人事部', 13000),
('周九', '销售部', 14000),
('吴十', '人事部', 11000);

表里的数据:

四、基础用法1. 最简单的拼接

按部门分组,把每组的姓名拼起来:

SELECT 
    department,
    GROUP_CONCAT(name) AS names
FROM employee
GROUP BY department;

结果:

默认用逗号分隔,没毛病。

2. 自定义分隔符

用 | 分隔:

SELECT 
    department,
    GROUP_CONCAT(name SEPARATOR ' | ') AS names
FROM employee
GROUP BY department;

结果:

3. 组内排序

按薪资从高到低排列后再拼接名字:

SELECT 
    department,
    GROUP_CONCAT(name ORDER BY salary DESC) AS names
FROM employee
GROUP BY department;

结果:

技术部里李四薪资最高(18000),所以排最前面。

4. 去重拼接

薪资 15000 在技术部出现了两次(张三和赵六),用 去掉重复的薪资值:

SELECT 
    department,
    GROUP_CONCAT(DISTINCT salary) AS salaries
FROM employee
GROUP BY department;

结果:

技术部只剩两个薪资值,重复的 15000 被去掉了。

五、进阶用法1. 拼接多个列

用 拼出 “姓名:薪资” 的格式,再用 把组内结果串起来:

SELECT 
    department,
    GROUP_CONCAT(
        CONCAT(name, ':', salary) 
        ORDER BY salary DESC 
        SEPARATOR ' | '
    ) AS name_salary
FROM employee
GROUP BY department;

结果:

技术部

李四:18000 | 张三:15000 | 赵六:15000

人事部

孙八:13000 | 吴十:11000

销售部

钱七:16000 | 周九:14000 | 王五:12000

2. 不带 GROUP BY 的用法

不写 GROUP BY 时,整张表就是一个组:

SELECT GROUP_CONCAT(name ORDER BY id SEPARATOR '-') AS all_names
FROM employee
WHERE salary > 14000;

结果:

3. 和其他聚合函数搭配

同时统计每组的姓名、人数和平均薪资:

SELECT 
    department,
    GROUP_CONCAT(name ORDER BY salary DESC) AS names,
    COUNT(*) AS headcount,
    ROUND(AVG(salary), 2) AS avg_salary
FROM employee
GROUP BY department;

结果:

4. 统计每个部门有多少种不同的薪资

SELECT 
    department,
    GROUP_CONCAT(DISTINCT salary ORDER BY salary ASC) AS salary_list,
    COUNT(DISTINCT salary) AS salary_count
FROM employee
GROUP BY department;

结果:

六、实战案例案例1:标签聚合

内容管理系统里,一篇文章有多个标签。通常用中间表关联:

-- 文章表
CREATE TABLE article (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL
);
-- 标签表
CREATE TABLE tag (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);
-- 文章-标签关联表
CREATE TABLE article_tag (
    article_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (article_id, tag_id)
);
INSERT INTO article (title) VALUES 
('MySQL入门'), ('Java并发编程'), ('Redis实战');
INSERT INTO tag (name) VALUES 
('数据库'), ('MySQL'), ('Java'), ('并发'), ('缓存'), ('NoSQL');
INSERT INTO article_tag VALUES 
(1,1), (1,2), (2,3), (2,4), (3,2), (3,5), (3,6);

用 一次查出每篇文章的所有标签:

SELECT 
    a.title,
    GROUP_CONCAT(t.name ORDER BY t.id SEPARATOR ', ') AS tags
FROM article a
LEFT JOIN article_tag at ON a.id = at.article_id
LEFT JOIN tag t ON at.tag_id = t.id
GROUP BY a.id, a.title;

结果:

不用在代码里循环查标签再拼字符串了。

案例2:学生成绩报表

把每个学生的各科成绩拼成一行展示:

CREATE TABLE score (
    student_name VARCHAR(50),
    subject VARCHAR(30),
    score INT
);
INSERT INTO score VALUES
('张三', '语文', 92), ('张三', '数学', 88), ('张三', '英语', 76),
('李四', '语文', 85), ('李四', '数学', 95), ('李四', '英语', 90),
('王五', '语文', 78), ('王五', '数学', 82), ('王五', '英语', 88);

SELECT 
    student_name,
    GROUP_CONCAT(
        CONCAT(subject, ':', score) 
        ORDER BY subject 
        SEPARATOR '  '
    ) AS score_detail,
    SUM(score) AS total,
    ROUND(AVG(score), 1) AS avg_score
FROM score
GROUP BY student_name
ORDER BY total DESC;

结果:

案例3:查找某值所在的所有分组

找出薪资为 15000 的员工分布在哪些部门:

SELECT 
    GROUP_CONCAT(DISTINCT department ORDER BY department) AS depts_with_15000
FROM employee
WHERE salary = 15000;

结果:

七、踩坑指南1. 结果被截断(最常见的问题)

的结果有长度限制,由系统变量 控制。

默认值只有 1024 字节。如果你的拼接结果超过这个长度,超出的部分会被静默截断——不会报错,就是丢了。

-- 查看当前限制
SHOW VARIABLES LIKE 'group_concat_max_len';

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 1024  |
+----------------------+-------+

修改方法:

-- 临时修改,重启后失效
SET SESSION group_concat_max_len = 10240;
-- 全局修改,重启后仍然生效
SET GLOBAL group_concat_max_len = 10240;

如果希望永久生效,在 f 中添加:

[mysqld]
group_concat_max_len = 10240

建议: 生产环境根据业务需要适当调大mysql nvarchar max,一般设个 10K~100K 够用了。不要设太大MySQL GROUP_CONCAT 函数详解,一行代码搞定行转列拼接!,否则拼接大量数据会消耗内存。

2. 和 GROUP BY 配合时的坑

如果 中有非聚合列没出现在 GROUP BY 里, 模式下会报错:

-- 这个在严格模式下会报错
SELECT 
    department, 
    name,
    GROUP_CONCAT(salary) 
FROM employee
GROUP BY department;

报错信息: #2 of list is not in GROUP BY and 'name'…

name 列不在 GROUP BY 中,也不是聚合函数,严格模式不允许。要么把 name 加到 GROUP BY,要么去掉 中的 name。

3. NULL 值处理

会自动忽略 NULL 值:

INSERT INTO employee (name, department, salary) VALUES 
('测试', '技术部', NULL);
SELECT 
    department,
    GROUP_CONCAT(salary) AS salaries
FROM employee
WHERE department = '技术部'
GROUP BY department;

结果:

那条 为 NULL 的记录被忽略了,不会在拼接结果里出现 “null” 字符串。

如果整组都是 NULL,结果也是 NULL:

SELECT GROUP_CONCAT(salary) AS result
FROM employee
WHERE salary IS NULL;

结果为 NULL。

4. 内存消耗

在执行时会分配内存缓冲区。如果拼接的数据量很大,会消耗不少内存,甚至触发临时表写到磁盘。

用 看到 Extra 列出现 Using 时就要注意了。

5. 字符集问题

分隔符和拼接列的字符集要一致,否则可能出现乱码。尤其是多表 JOIN 时,不同表的字符集可能不同。可以用 () 函数统一字符集:

GROUP_CONCAT(CONVERT(name USING utf8mb4))

八、MySQL 8.0 的变化

MySQL 8.0 对 有一些改进:

可以当窗口函数用了,不用 GROUP BY 也能按分区拼接:

SELECT 
    name,
    department,
    salary,
    GROUP_CONCAT(name ORDER BY salary DESC) 
        OVER (PARTITION BY department) AS dept_names
FROM employee;

这会逐行返回所在部门的全部姓名拼接,每行都带完整结果。

优化器改进:8.0 处理 + ORDER BY 时排序效率比 5.7 好。九、对比其他方案

方案

优点

缺点

一条 SQL 搞定,性能好

有长度限制,复杂拼接语法繁琐

代码层循环拼接

灵活,没有长度限制

多次查询,代码量大

存储过程拼接

服务器端执行

维护成本高,调试麻烦

(8.0)

输出 JSON 格式

不能自定义分隔符

大多数场景下 够用了mysql nvarchar max,输出格式有特殊要求时再考虑 或代码层处理。

十、总结

的几个要点:

写报表要把多行数据拼成一个字段、做标签聚合、查成绩明细, 都能一条 SQL 搞定。记住调大 ,踩坑概率能降一大半。

文中所有 SQL 均在 MySQL 8.0 环境下测试通过。

公告:
1. 本站所有资源来源于用户上传和网络,如有侵权请联系站长!
2. 分享目的仅供大家学习和交流,您必须在下载后24小时内删除!
3. 不得使用于非法商业用途,不得违反国家法律。否则后果自负!
4. 本站提供的源码、模板、插件等等其他资源,都不包含技术服务请大家谅解!
5. 如有链接无法下载、失效或广告,请联系站长处理!
6. 本站不售卖代码,资源标价只是站长收集整理的辛苦费!如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。
7. 站长QQ号码 2205675299

资源库 - 资源分享下载网 » MySQL GROUP_CONCAT 函数详解,一行代码搞定行转列拼接!

常见问题FAQ

关于资源售价和售后服务的说明?
本站所有资源的标价均为本站收集资源的辛苦费,不代表资源本身的价值。软件是高智慧高价值的商品,不可能是白菜价。本站资源标价只是赞助费用,收取的赞助费仅用来维持本站的日常运营!毕竟收集整理资料需要投入云计算资源和站长大量的精力。
代码有没有售后服务和技术支持?
由于代码的运行具有不可预见性,本站不保证代码完整可运行,不提供技术支持和售后服务。 本站原创代码都是站长自己开发的,可以有偿提供技术支持服务。 网站里标明【亲测】的代码都是站长亲测过的,其他的代码由于精力有限,没有一一测试,不能保证代码就一定能够使用,更没有技术支持服务,下载前请自行斟酌。
有没有搭建服务?
由于搭建服务比较费时费力,所以本站除了原创代码外均不提供搭建服务。本站分享代码纯属兴趣爱好,不以盈利为目的,请勿咨询有没有搭建服务,谢谢理解。
链接地址失效了怎么办?
请带上资源链接地址联系客服,工作时间内我们看到后将第一时间回复。
关于解压密码
本站资源一般都没有加密,如果发现需要解压密码的,那么就输入 hao.35dc.com 试试。

发表评论

资源库,由老程序员细心甄别、精心筛选,只为提供优质的源码资源

关于我们 联系我们