MySQL高级函数之：GROUP_CONCAT与JSON_ARRAYAGG：其在数据聚合中的优劣对比
大家好，今天我们来深入探讨MySQL中两个常用的数据聚合函数：GROUP_CONCAT 和 JSON_ARRAYAGG。这两个函数都用于将分组后的数据聚合起来，但它们在处理方式、适用场景以及性能方面存在显著差异。理解这些差异对于编写高效、可维护的SQL查询至关重要。
1. GROUP_CONCAT：字符串聚合的经典选择
GROUP_CONCAT 函数是MySQL中历史悠久且广泛使用的字符串聚合函数。它的主要作用是将同一分组内的多个值连接成一个字符串，并使用指定的分隔符分隔。
语法:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {col_name | expr | position}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str])参数说明:
DISTINCT: 可选。如果指定，则只连接不同的值。expr: 要连接的表达式，通常是列名。ORDER BY: 可选。指定连接值的排序方式。SEPARATOR: 可选。指定分隔符，默认为逗号 (,)。
示例:
假设我们有一个 orders 表，包含以下数据：
| order_id | customer_id | product_name |
|---|---|---|
| 1 | 101 | Apple |
| 2 | 101 | Banana |
| 3 | 102 | Orange |
| 4 | 101 | Grape |
| 5 | 102 | Apple |
我们可以使用 GROUP_CONCAT 将每个客户购买的产品名称连接成一个字符串：
SELECT customer_id, GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products
FROM orders
GROUP BY customer_id;查询结果：
| customer_id | products |
|---|---|
| 101 | Apple, Banana, Grape |
| 102 | Apple, Orange |
优点:
- 简单易用: 语法简单直观，容易理解和使用。
- 广泛兼容: 在较早的MySQL版本中就已存在，具有良好的兼容性。
- 字符串处理能力: 擅长处理字符串类型的聚合，可以灵活地自定义分隔符和排序方式。
缺点:
- 字符串长度限制: 默认情况下，
GROUP_CONCAT有一个最大长度限制（group_concat_max_len系统变量控制），超过该限制的数据会被截断。这可能导致数据丢失。 - 数据类型限制: 通常只能处理字符串类型的数据，对于非字符串类型的数据需要进行显式转换。
- 缺乏结构化: 聚合结果是一个纯字符串，缺乏结构化信息，难以进行进一步的解析和处理。
- 无法处理嵌套结构: 难以处理需要嵌套聚合的复杂场景。例如，想要聚合每个客户的订单信息，每个订单包含多个产品，
GROUP_CONCAT就很难实现。
2. JSON_ARRAYAGG：JSON数组聚合的新选择
JSON_ARRAYAGG 函数是MySQL 5.7.22 引入的 JSON 聚合函数。它将同一分组内的多个值聚合为一个 JSON 数组。
语法:
JSON_ARRAYAGG(expr) [OVER (analytic_clause)]参数说明:
expr: 要聚合的表达式，可以是任何数据类型。OVER (analytic_clause): 可选。用于窗口函数，这里我们主要关注分组聚合，暂不讨论窗口函数。
示例:
使用 JSON_ARRAYAGG 聚合 orders 表中的数据：
SELECT customer_id, JSON_ARRAYAGG(product_name) AS products
FROM orders
GROUP BY customer_id;查询结果：
| customer_id | products |
|---|---|
| 101 | ["Apple", "Banana", "Grape"] |
| 102 | ["Orange", "Apple"] |
优点:
- 无字符串长度限制: 不存在字符串长度限制，可以处理任意大小的数据。
- 支持多种数据类型: 可以聚合任何数据类型，包括数值、字符串、日期等。
- 结构化数据: 聚合结果是一个 JSON 数组，具有结构化信息，可以使用 JSON 函数进行进一步的解析和处理。
- 易于嵌套: 可以方便地进行嵌套聚合，处理更复杂的场景。
- 与JSON函数配合: 可以和JSON函数进行配合，提取和操作JSON内部的数据。
缺点:
- 版本要求: 需要在MySQL 5.7.22 或更高版本中使用。
- JSON处理开销: 生成和处理 JSON 数据的开销可能比字符串处理略高。
- 语法相对复杂: 对于不熟悉 JSON 的开发者，语法可能稍显复杂。
- 排序不直接支持: 不像
GROUP_CONCAT可以直接在函数内部指定排序，JSON_ARRAYAGG需要借助子查询或窗口函数来实现排序。
3. 优劣对比：表格总结
| 特性 | GROUP_CONCAT |
JSON_ARRAYAGG |
|---|---|---|
| 数据类型 | 字符串 | 任意数据类型 |
| 聚合结果 | 字符串 | JSON 数组 |
| 长度限制 | 有 (受 group_concat_max_len 限制) |
无 |
| 版本要求 | 较低 (较早版本可用) | 较高 (MySQL 5.7.22+) |
| 排序 | 函数内部直接支持 | 需要借助子查询或窗口函数 |
| 嵌套聚合 | 困难 | 容易 |
| 结构化 | 缺乏 | 具有 |
| 适用场景 | 简单的字符串聚合，对长度要求不高，需要兼容旧版本 | 复杂的数据聚合，需要结构化信息，数据量较大，需要处理多种数据类型 |
| 性能 | 字符串处理性能较好 | JSON 处理开销可能略高 |
4. 实际应用场景分析
场景 1：商品标签聚合
假设有一个 products 表，包含商品和对应的标签：
| product_id | product_name | tag |
|---|---|---|
| 1 | Apple iPhone | smartphone |
| 1 | Apple iPhone | apple |
| 2 | Samsung Galaxy | smartphone |
| 2 | Samsung Galaxy | android |
| 3 | Xiaomi Redmi | smartphone |
| 3 | Xiaomi Redmi | xiaomi |
如果我们想将每个商品的标签聚合在一起，可以使用 GROUP_CONCAT：
SELECT product_id, product_name, GROUP_CONCAT(tag SEPARATOR ', ') AS tags
FROM products
GROUP BY product_id, product_name;结果：
| product_id | product_name | tags |
|---|---|---|
| 1 | Apple iPhone | smartphone, apple |
| 2 | Samsung Galaxy | smartphone, android |
| 3 | Xiaomi Redmi | smartphone, xiaomi |
在这个场景中，GROUP_CONCAT 足够满足需求，因为标签是字符串类型，且数量通常不会太多，不会超出长度限制。
场景 2：订单明细聚合
假设有一个 orders 表和一个 order_items 表：
orders 表：
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 101 | 2023-10-26 |
| 2 | 102 | 2023-10-27 |
order_items 表：
| item_id | order_id | product_id | product_name | quantity | price |
|---|---|---|---|---|---|
| 1 | 1 | 1 | Apple iPhone | 1 | 999 |
| 2 | 1 | 2 | Apple AirPods | 1 | 199 |
| 3 | 2 | 3 | Samsung Galaxy | 1 | 899 |
如果我们需要查询每个订单的详细信息，包括订单日期、客户ID以及订单中包含的所有商品信息（商品名称、数量、价格），使用 GROUP_CONCAT 难以实现，因为我们需要将每个商品的信息作为一个整体聚合起来。
使用 JSON_ARRAYAGG 可以轻松解决这个问题：
SELECT
o.order_id,
o.customer_id,
o.order_date,
JSON_ARRAYAGG(
JSON_OBJECT(
'product_name', oi.product_name,
'quantity', oi.quantity,
'price', oi.price
)
) AS order_items
FROM
orders o
JOIN
order_items oi ON o.order_id = oi.order_id
GROUP BY
o.order_id, o.customer_id, o.order_date;结果：
| order_id | customer_id | order_date | order_items |
|---|---|---|---|
| 1 | 101 | 2023-10-26 | [{"product_name": "Apple iPhone", "quantity": 1, "price": 999}, {"product_name": "Apple AirPods", "quantity": 1, "price": 199}] |
| 2 | 102 | 2023-10-27 | [{"product_name": "Samsung Galaxy", "quantity": 1, "price": 899}] |
在这个场景中，JSON_ARRAYAGG 将每个订单中的商品信息聚合为一个 JSON 数组，每个数组元素是一个包含商品名称、数量和价格的 JSON 对象。 这样，我们就可以方便地获取订单的详细信息，并且可以使用 JSON 函数进一步解析和处理这些数据。
场景 3：带排序的聚合
假设我们需要聚合每个客户购买的产品名称，并按照产品名称的字母顺序排序。 使用 GROUP_CONCAT 可以直接在函数内部指定排序方式：
SELECT customer_id, GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products
FROM orders
GROUP BY customer_id;如果使用 JSON_ARRAYAGG，则需要借助子查询或窗口函数来实现排序：
方法一：使用子查询
SELECT customer_id, JSON_ARRAYAGG(product_name) AS products
FROM (
SELECT customer_id, product_name
FROM orders
ORDER BY customer_id, product_name
) AS subquery
GROUP BY customer_id;方法二：使用窗口函数 (MySQL 8.0+)
SELECT customer_id, JSON_ARRAYAGG(product_name) OVER (PARTITION BY customer_id ORDER BY product_name) AS products
FROM orders
GROUP BY customer_id;在这个场景中，GROUP_CONCAT 在排序方面更简洁方便。
5. 性能考量
在选择 GROUP_CONCAT 和 JSON_ARRAYAGG 时，性能也是一个重要的考虑因素。
- 字符串处理 vs. JSON 处理:
GROUP_CONCAT的字符串处理性能通常比JSON_ARRAYAGG的 JSON 处理性能略好，尤其是在数据量较小的情况下。 但是，随着数据量的增加，JSON_ARRAYAGG的优势会逐渐显现，因为它没有字符串长度限制，可以避免数据截断。 - 索引优化: 对于包含大量数据的表，合理的索引可以显著提高聚合查询的性能。 确保在分组字段和排序字段上创建索引。
group_concat_max_len调整: 如果使用GROUP_CONCAT，需要根据实际情况调整group_concat_max_len系统变量，以避免数据截断。 但是，增加group_concat_max_len的值会消耗更多的内存，需要权衡考虑。
在实际应用中，建议根据具体场景进行性能测试，选择最合适的聚合函数。
6. 代码示例：处理NULL值
在实际应用中，我们需要考虑NULL值的情况。 默认情况下，GROUP_CONCAT 会忽略NULL值，而 JSON_ARRAYAGG 会将NULL值包含在JSON数组中。
示例：
假设 orders 表中 product_name 列允许为 NULL:
| order_id | customer_id | product_name |
|---|---|---|
| 1 | 101 | Apple |
| 2 | 101 | NULL |
| 3 | 102 | Orange |
| 4 | 101 | Grape |
| 5 | 102 | Apple |
使用 GROUP_CONCAT：
SELECT customer_id, GROUP_CONCAT(product_name SEPARATOR ', ') AS products
FROM orders
GROUP BY customer_id;结果：
| customer_id | products |
|---|---|
| 101 | Apple, Grape |
| 102 | Orange, Apple |
NULL 值被忽略。
使用 JSON_ARRAYAGG：
SELECT customer_id, JSON_ARRAYAGG(product_name) AS products
FROM orders
GROUP BY customer_id;结果：
| customer_id | products |
|---|---|
| 101 | ["Apple", null, "Grape"] |
| 102 | ["Orange", "Apple"] |
NULL 值被包含在 JSON 数组中。
如果我们需要在 JSON_ARRAYAGG 中忽略 NULL 值，可以使用 IFNULL 或 CASE 语句进行处理：
SELECT customer_id, JSON_ARRAYAGG(IFNULL(product_name, '')) AS products
FROM orders
GROUP BY customer_id;或者：
SELECT customer_id, JSON_ARRAYAGG(CASE WHEN product_name IS NULL THEN '' ELSE product_name END) AS products
FROM orders
GROUP BY customer_id;结果：
| customer_id | products |
|---|---|
| 101 | ["Apple", "", "Grape"] |
| 102 | ["Orange", "Apple"] |
在这个例子中，我们将NULL值替换为空字符串。 你也可以根据实际需求选择其他替换值。
7. 高级用法：结合其他函数
GROUP_CONCAT 和 JSON_ARRAYAGG 可以与其他函数结合使用，实现更复杂的数据聚合和处理。
示例 1：使用 SUBSTRING_INDEX 提取 GROUP_CONCAT 结果的一部分
假设我们使用 GROUP_CONCAT 聚合了多个产品的名称，并用逗号分隔。 如果我们只需要提取第一个产品名称，可以使用 SUBSTRING_INDEX 函数：
SELECT
customer_id,
SUBSTRING_INDEX(GROUP_CONCAT(product_name SEPARATOR ', '), ',', 1) AS first_product
FROM
orders
GROUP BY
customer_id;示例 2：使用 JSON_EXTRACT 提取 JSON_ARRAYAGG 结果中的某个元素
假设我们使用 JSON_ARRAYAGG 聚合了订单中的商品信息，并存储在 JSON 数组中。 如果我们需要提取每个订单的第一个商品的名称，可以使用 JSON_EXTRACT 函数：
SELECT
o.order_id,
JSON_EXTRACT(
JSON_ARRAYAGG(
JSON_OBJECT('product_name', oi.product_name)
),
'$[0].product_name'
) AS first_product_name
FROM
orders o
JOIN
order_items oi ON o.order_id = oi.order_id
GROUP BY
o.order_id;在这个例子中，$[0].product_name 表示提取 JSON 数组的第一个元素的 product_name 属性。
示例 3：使用 JSON_LENGTH 获取 JSON_ARRAYAGG 数组的长度
想知道聚合了多少个元素，可以使用 JSON_LENGTH 函数来获取数组长度。
SELECT
customer_id,
JSON_LENGTH(JSON_ARRAYAGG(product_name)) AS product_count
FROM
orders
GROUP BY
customer_id;8. 一些建议
- 优先考虑数据结构化的需求。 如果需要处理复杂的数据结构，或者需要对聚合结果进行进一步的解析和处理，
JSON_ARRAYAGG是更好的选择。 - 注意 MySQL 版本兼容性。 如果需要在较低版本的 MySQL 中使用聚合函数，只能选择
GROUP_CONCAT。 - 根据实际场景进行性能测试，选择最合适的聚合函数。
- 注意处理 NULL 值的情况。
- 合理使用索引来提高查询性能。
- 充分利用 MySQL 提供的其他函数，例如字符串函数和 JSON 函数，来实现更复杂的数据聚合和处理。
关于 GROUP_CONCAT 和 JSON_ARRAYAGG 的总结
GROUP_CONCAT 和 JSON_ARRAYAGG 都是强大的数据聚合函数，各自具有不同的优势和适用场景。理解它们的差异，并在实际应用中灵活选择，可以帮助我们编写更高效、可维护的SQL查询。 选择哪个函数取决于具体的需求，包括数据类型、数据量、结构化需求以及 MySQL 版本等因素。