mysql 结果集转 JSON
在开发Web应用程序时，我们经常需要将MySQL数据库中的查询结果转换为JSON格式，以便于前端页面的展示和处理。本文将介绍如何在MySQL中将结果集转换为JSON，并且提供了相关的代码示例。
什么是JSON
JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，常用于前后端数据的传输和存储。它以键值对的形式组织数据，支持嵌套和数组，易于阅读和编写，并且被大多数编程语言所支持。
MySQL的JSON函数
MySQL从5.7版本开始引入了一系列的JSON函数，用于处理和操作JSON数据。这些函数包括：
- JSON_ARRAY：创建一个JSON数组
- JSON_OBJECT：创建一个JSON对象
- JSON_ARRAYAGG：将行转换为JSON数组
- JSON_OBJECTAGG：将行转换为JSON对象
- JSON_EXTRACT：提取JSON数据
- JSON_UNQUOTE：去掉JSON数据的引号
- JSON_MERGE：合并多个JSON对象
- JSON_SET：设置JSON对象中的值
- JSON_REPLACE：替换JSON对象中的值
- JSON_REMOVE：删除JSON对象中的值
在本文中，我们将重点介绍如何使用JSON_ARRAYAGG和JSON_OBJECTAGG函数将MySQL的结果集转换为JSON格式。
示例数据库
为了方便演示，我们将使用一个简单的学生表作为示例数据。该表包含以下字段：
- id：学生ID，整数类型
- name：学生姓名，字符串类型
- age：学生年龄，整数类型
- gender：学生性别，字符串类型
以下是示例数据：
| id | name | age | gender |
|---|---|---|---|
| 1 | Tom | 18 | male |
| 2 | Lucy | 20 | female |
| 3 | Jack | 19 | male |
将结果集转换为JSON数组
我们首先来看如何将结果集转换为JSON数组。假设我们要查询学生表中的所有数据，并将其转换为JSON数组。可以使用JSON_ARRAYAGG函数实现这个需求。
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'id', id,
'name', name,
'age', age,
'gender', gender
)
) AS json_data
FROM students;
在上述代码中，我们使用了JSON_OBJECT函数来创建一个JSON对象，每个字段都对应相应的列。然后使用JSON_ARRAYAGG函数将多个JSON对象合并为一个JSON数组。通过AS关键字给结果集命名为json_data。
执行以上查询语句后，将得到以下结果：
[{"id": 1, "name": "Tom", "age": 18, "gender": "male"}, {"id": 2, "name": "Lucy", "age": 20, "gender": "female"}, {"id": 3, "name": "Jack", "age": 19, "gender": "male"}]
将结果集转换为JSON对象
除了将结果集转换为JSON数组，我们还可以将结果集转换为JSON对象。假设我们要按照性别对学生表进行分组，并将每个分组的数据转换为一个JSON对象。可以使用JSON_OBJECTAGG函数实现这个需求。
SELECT JSON_OBJECTAGG(
gender,
JSON_ARRAYAGG(
JSON_OBJECT(
'id', id,
'name', name,
'age', age
)
)
) AS json_data
FROM students
GROUP BY gender;
在上述代码中，我们首先使用GROUP BY语句按照性别对学生表进行分组。然后使用JSON_OBJECTAGG函数将每个分组的数据转换为一个JSON对象，其中键为性别，值为对应分组的JSON数组。
执行以上查询语句后，将得到以下结果：
{
"male": [
{"id": 1, "name": "Tom", "age": 18},
{"id": 3, "name": "Jack", "age": 19}
],
"female": [
{"id": 2, "name": "Lucy", "age": 20}
]
}
