在后端开发场景中，将MySQL查询得到的结果集转换为标准JSON格式是高频需求，无论是前后端分离项目的数据接口返回，还是服务间的数据交互，都需要保证JSON格式的规范性和完整性。下面将从不同维度介绍实现该需求的方法。
一、使用MySQL原生函数直接输出JSON
MySQL 5.7及以上版本内置了完善的JSON处理函数，可以直接在SQL层面将查询结果转换为JSON格式，无需在应用层做额外处理，适合简单的查询场景。
1. JSON_OBJECT函数
该函数可以将指定的键值对转换为JSON对象，适合单条记录的转换。示例如下：
-- 查询用户表单条记录并转为JSON对象
SELECT JSON_OBJECT(
'user_id', id,
'user_name', username,
'user_age', age,
'create_time', create_time
) AS user_json
FROM user
WHERE id = 1;
2. JSON_ARRAYAGG函数
如果需要将多条查询结果转换为JSON数组，可以结合JSON_ARRAYAGG函数使用，示例如下：
-- 查询所有用户记录并转为JSON数组
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'user_id', id,
'user_name', username,
'user_age', age
)
) AS user_list_json
FROM user
WHERE status = 1;
二、应用层处理查询结果转JSON
如果使用的MySQL版本较低，不支持原生JSON函数，或者需要对数据做复杂的二次处理，可以在应用层完成转换，下面以PHP和Python为例说明。
1. PHP实现示例
PHP通过mysqli扩展获取查询结果后，将结果集整理为数组，再用json_encode函数转换即可，注意要处理中文编码问题。
<?php
// 连接MySQL数据库
$conn = new mysqli('127.0.0.1', 'root', 'password', 'test_db');
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
// 执行查询
$sql = "SELECT id, username, age, email FROM user WHERE status = 1";
$result = $conn->query($sql);
$userList = [];
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
// 处理特殊字符，避免JSON格式错误
$row['username'] = htmlspecialchars($row['username'], ENT_QUOTES, 'UTF-8');
$userList[] = $row;
}
}
// 输出标准JSON，设置中文不转义
header('Content-Type: application/json; charset=utf-8');
echo json_encode([
'code' => 200,
'msg' => '查询成功',
'data' => $userList
], JSON_UNESCAPED_UNICODE);
$conn->close();
?>
2. Python实现示例
Python使用pymysql库获取查询结果，通过json模块转换，同时可以处理日期时间等特殊类型的格式适配。
import pymysql
import json
from datetime import datetime
# 连接数据库
conn = pymysql.connect(
host='127.0.0.1',
user='root',
password='password',
database='test_db',
charset='utf8mb4'
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 执行查询
sql = "SELECT id, username, age, create_time FROM user WHERE status = 1"
cursor.execute(sql)
result = cursor.fetchall()
# 处理日期时间类型，转换为字符串
for item in result:
if isinstance(item['create_time'], datetime):
item['create_time'] = item['create_time'].strftime('%Y-%m-%d %H:%M:%S')
# 输出标准JSON
response = {
'code': 200,
'msg': '查询成功',
'data': result
}
print(json.dumps(response, ensure_ascii=False))
cursor.close()
conn.close()
三、常见问题及处理方案
- 特殊字符导致JSON格式错误：如果字段内容包含双引号、换行符等特殊字符，需要在转换前做转义处理，避免破坏JSON结构。
- 数据类型丢失：MySQL的查询结果中，数字类型、布尔类型在部分语言的转换过程中可能被转为字符串，需要在转换时显式指定类型，或者在JSON编码时设置对应参数。
- 中文乱码问题：需要保证数据库连接、查询、JSON编码三个环节的字符集都是UTF-8，避免出现乱码。
- 大结果集内存溢出：如果查询结果数据量极大，不要一次性将所有结果加载到内存中转换，建议分页查询，逐批处理转换。
四、两种方案的选择建议
如果是简单的查询场景，且MySQL版本符合要求，优先使用原生JSON函数，减少应用层的处理逻辑，提升性能。如果需要对数据做复杂的过滤、格式适配，或者MySQL版本不支持原生JSON函数，则选择应用层处理的方案，灵活性更高。