MySQL高级函数之：`GROUP_CONCAT()`：其在分组聚合时的`ORDER BY`和`SEPARATOR`用法。

MySQL 高级函数 GROUP_CONCAT() 深入解析：分组聚合中的排序与分隔

大家好，今天我们来深入探讨 MySQL 中一个非常实用的高级函数：GROUP_CONCAT()。这个函数在处理分组聚合时，能够将每个分组内的多个值连接成一个字符串，极大地简化了某些复杂查询。 今天的重点是 GROUP_CONCAT() 在分组聚合时的 ORDER BY 和 SEPARATOR 用法，通过实际案例和代码演示，帮助大家理解它们的具体应用和优势。

GROUP_CONCAT() 基础回顾

首先，我们快速回顾一下 GROUP_CONCAT() 的基本语法和功能。它的基本语法如下：

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

• expr: 需要连接的表达式，可以是列名、函数、常量等。
• DISTINCT: 可选，用于去除重复值。
• ORDER BY: 可选，用于指定连接值的排序方式。
• SEPARATOR: 可选，用于指定连接值之间的分隔符，默认为逗号 (,)。

GROUP_CONCAT() 的核心功能是将每个分组内的 expr 连接成一个字符串。如果没有 GROUP BY 子句，它会将整个表的所有 expr 连接成一个字符串。

ORDER BY 子句：控制连接值的顺序

ORDER BY 子句允许我们在连接分组内的值时，控制它们的排列顺序。这在需要按照特定规则展示数据时非常有用。

示例：按照学生年龄排序连接姓名

假设我们有一个名为 students 的表，包含学生的姓名 (name)、年龄 (age) 和班级 (class) 信息。

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    class VARCHAR(20) NOT NULL
);

INSERT INTO students (name, age, class) VALUES
('Alice', 18, 'A'),
('Bob', 17, 'A'),
('Charlie', 19, 'B'),
('David', 18, 'B'),
('Eve', 17, 'A'),
('Frank', 19, 'B'),
('Grace', 18, 'A');

现在，我们想要按班级分组，并按年龄升序连接每个班级的学生姓名。可以使用以下 SQL 语句：

SELECT
    class,
    GROUP_CONCAT(name ORDER BY age ASC) AS students_by_age
FROM
    students
GROUP BY
    class;

执行结果如下：

class	students_by_age
A	Bob,Eve,Alice,Grace
B	David,Charlie,Frank

可以看到，每个班级的学生姓名按照年龄从小到大排列。如果我们想按照年龄降序排列，只需将 ORDER BY 子句改为 ORDER BY age DESC。

SELECT
    class,
    GROUP_CONCAT(name ORDER BY age DESC) AS students_by_age
FROM
    students
GROUP BY
    class;

执行结果如下：

class	students_by_age
A	Grace,Alice,Bob,Eve
B	Frank,Charlie,David

多字段排序

ORDER BY 子句也支持多字段排序。例如，我们想先按照年龄排序，如果年龄相同，则按照姓名字母顺序排序。

SELECT
    class,
    GROUP_CONCAT(name ORDER BY age ASC, name ASC) AS students_by_age_name
FROM
    students
GROUP BY
    class;

执行结果如下：

class	students_by_age_name
A	Bob,Eve,Alice,Grace
B	David,Charlie,Frank

使用表达式排序

ORDER BY 子句不仅可以按照列名排序，还可以按照表达式排序。例如，我们想按照学生姓名的长度排序。

SELECT
    class,
    GROUP_CONCAT(name ORDER BY LENGTH(name) ASC) AS students_by_name_length
FROM
    students
GROUP BY
    class;

执行结果如下：

class	students_by_name_length
A	Bob,Eve,Alice,Grace
B	David,Charlie,Frank

注意：

• ORDER BY 子句只影响连接值的顺序，不影响 GROUP BY 的分组结果。
• 如果没有指定 ORDER BY 子句，连接值的顺序是不确定的。

SEPARATOR 子句：自定义分隔符

SEPARATOR 子句允许我们自定义连接值之间的分隔符，默认为逗号 (,)。这在需要使用特定格式展示数据时非常有用。

示例：使用分号作为分隔符

我们继续使用上面的 students 表。现在，我们想按班级分组，并使用分号 (;) 作为分隔符连接每个班级的学生姓名。

SELECT
    class,
    GROUP_CONCAT(name SEPARATOR '; ') AS students_separated_by_semicolon
FROM
    students
GROUP BY
    class;

执行结果如下：

class	students_separated_by_semicolon
A	Alice; Bob; Eve; Grace
B	Charlie; David; Frank

示例：使用换行符作为分隔符

我们也可以使用换行符 (n) 作为分隔符，使每个学生姓名单独占一行。

SELECT
    class,
    GROUP_CONCAT(name SEPARATOR 'n') AS students_separated_by_newline
FROM
    students
GROUP BY
    class;

执行结果如下 (在实际显示中，每个姓名会换行)：

class	students_separated_by_newline
A	AlicenBobnEvenGrace
B	CharlienDavidnFrank

示例：使用 HTML 标签作为分隔符

如果我们想在 HTML 页面中展示数据，可以使用 HTML 标签作为分隔符。

SELECT
    class,
    GROUP_CONCAT(CONCAT('<p>', name, '</p>') SEPARATOR '') AS students_as_html
FROM
    students
GROUP BY
    class;

执行结果如下：

class	students_as_html
A	Alice Bob Eve Grace
B	Charlie David Frank

注意：

• SEPARATOR 子句的值必须是字符串常量。
• 可以使用空字符串 ('') 作为分隔符，将所有值连接在一起，不使用任何分隔符。

ORDER BY 和 SEPARATOR 结合使用

ORDER BY 和 SEPARATOR 可以结合使用，以实现更灵活的连接效果。

示例：按年龄排序并使用自定义分隔符

我们结合之前按年龄排序和使用分号分隔符的例子。

SELECT
    class,
    GROUP_CONCAT(name ORDER BY age ASC SEPARATOR '; ') AS students_by_age_separated
FROM
    students
GROUP BY
    class;

执行结果如下：

class	students_by_age_separated
A	Bob; Eve; Alice; Grace
B	David; Charlie; Frank

示例：按多个字段排序并使用换行符分隔

SELECT
    class,
    GROUP_CONCAT(name ORDER BY age ASC, name ASC SEPARATOR 'n') AS students_by_age_name_separated
FROM
    students
GROUP BY
    class;

执行结果如下 (在实际显示中，每个姓名会换行)：

class	students_by_age_name_separated
A	BobnEvenAlicenGrace
B	DavidnCharlienFrank

GROUP_CONCAT() 长度限制

需要注意的是，GROUP_CONCAT() 有长度限制。默认情况下，连接后的字符串的最大长度为 1024 字节。如果超过这个长度，多余的部分会被截断。

可以通过修改 group_concat_max_len 系统变量来增加最大长度。

SET SESSION group_concat_max_len = 102400;  -- 设置为 100KB

或者，在全局范围内修改：

SET GLOBAL group_concat_max_len = 102400;

注意：

• 修改 GLOBAL 变量需要 SUPER 权限。
• 增加 group_concat_max_len 会增加内存消耗，需要根据实际情况进行调整。

实际应用场景

GROUP_CONCAT() 在实际开发中有很多应用场景。以下是一些常见的例子：

1. 生成报表： 将多个相关数据连接成一个字符串，方便报表展示。例如，将某个订单的所有商品名称连接在一起。

2. 数据导出： 将数据导出为特定格式的文件，例如 CSV 或 HTML。

3. 权限管理： 将某个用户的多个权限连接成一个字符串，方便权限验证。

4. 数据分析： 对分组数据进行汇总分析，例如，统计每个地区的用户数量，并将用户姓名连接在一起。

示例：生成订单商品列表

假设我们有两个表：orders 和 order_items。orders 表包含订单信息，order_items 表包含订单的商品信息。

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_name VARCHAR(50) NOT NULL,
    quantity INT NOT NULL
);

INSERT INTO orders (order_date, customer_id) VALUES
('2023-10-26', 1),
('2023-10-27', 2),
('2023-10-28', 1);

INSERT INTO order_items (order_id, product_name, quantity) VALUES
(1, 'Laptop', 1),
(1, 'Mouse', 1),
(2, 'Keyboard', 1),
(2, 'Monitor', 2),
(3, 'Headphones', 1),
(3, 'Microphone', 1);

我们想要查询每个订单的商品列表，可以使用以下 SQL 语句：

SELECT
    o.id AS order_id,
    o.order_date,
    GROUP_CONCAT(oi.product_name SEPARATOR ', ') AS product_list
FROM
    orders o
JOIN
    order_items oi ON o.id = oi.order_id
GROUP BY
    o.id, o.order_date;

执行结果如下：

order_id	order_date	product_list
1	2023-10-26	Laptop, Mouse
2	2023-10-27	Keyboard, Monitor
3	2023-10-28	Headphones, Microphone

示例：统计每个用户的权限列表

假设我们有两个表：users 和 permissions。users 表包含用户信息，permissions 表包含用户的权限信息。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    permission_name VARCHAR(50) NOT NULL
);

INSERT INTO users (username) VALUES
('john'),
('jane');

INSERT INTO permissions (user_id, permission_name) VALUES
(1, 'read'),
(1, 'write'),
(2, 'read'),
(2, 'update'),
(2, 'delete');

我们想要查询每个用户的权限列表，可以使用以下 SQL 语句：

SELECT
    u.username,
    GROUP_CONCAT(p.permission_name ORDER BY p.permission_name SEPARATOR ', ') AS permission_list
FROM
    users u
JOIN
    permissions p ON u.id = p.user_id
GROUP BY
    u.username;

执行结果如下：

username	permission_list
jane	delete, read, update
john	read, write

总结和提升

今天我们详细学习了 GROUP_CONCAT() 函数在分组聚合时 ORDER BY 和 SEPARATOR 的用法。通过灵活运用这两个子句，我们可以更好地控制连接值的顺序和格式，满足各种复杂的业务需求。

灵活使用，解决聚合显示问题

GROUP_CONCAT() 结合 ORDER BY 和 SEPARATOR，能够有效地组织和呈现分组聚合后的数据，满足各种数据展示需求。

注意长度限制，适时调整

GROUP_CONCAT() 存在长度限制，要根据实际情况调整 group_concat_max_len，避免数据截断，确保信息的完整性。