我的mysql怎么变成json

我的MySQL怎么变成JSON？一篇文章搞定数据转换全流程

在日常开发中，我们经常需要将MySQL中的数据转换为JSON格式——无论是用于前后端数据交互、API接口返回，还是数据分析处理，本文将从“为什么需要转换”出发，详解MySQL内置函数、编程语言处理工具，以及常见问题的解决方案,帮你轻松搞定MySQL到JSON的转换。

为什么要把MySQL数据变成JSON？

在开始转换前，先明确几个常见场景：

前后端分离：后端从MySQL查数据，转成JSON传给前端，前端直接解析渲染；
API接口开发：RESTful API通常要求返回JSON格式，如{"code": 200, "data": [...]}；
数据缓存：将MySQL数据转JSON后存入Redis等缓存系统，减少数据库查询；
跨平台数据交换：不同系统间通过JSON格式传输数据，兼容性更好。

MySQL内置函数：直接在SQL中转JSON

MySQL 5.7+和MariaDB 10.2+原生支持JSON函数，无需编程代码，直接在查询语句中就能完成转换,以下是核心方法：

单行数据转JSON：`JSON_OBJECT()`和`JSON_ARRAY()`

场景1：将单行记录转为JSON对象（键值对格式）

假设有一张用户表user，结构如下：

CREATE TABLE `user` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(50),
  `age` INT,
  `email` VARCHAR(100)
);

数据：
| id | name | age | email | |----|------|-----|---------------| | 1 | 张三 | 25 | zhangsan@.com | | 2 | 李四 | 30 | lisi@.com |

需求：将每行用户数据转为JSON对象，如{"id": 1, "name": "张三", "age": 25, "email": "zhangsan@.com"}。

SQL：

SELECT 
  JSON_OBJECT(
    'id', id,
    'name', name,
    'age', age,
    'email', email
  ) AS user_json
FROM user;

结果：
| user_json | |--------------------------------------------------------------------------| | {"id": 1, "name": "张三", "age": 25, "email": "zhangsan@.com"} | | {"id": 2, "name": "李四", "age": 30, "email": "lisi@.com"} |

注意：JSON_OBJECT()的参数必须是“键, 值”成对出现，键必须是字符串（可以用单引号或双引号包裹）。

场景2：将单行多个字段转为JSON数组

需求：将id和name转为JSON数组，如[1, "张三"]。

SQL：

SELECT JSON_ARRAY(id, name) AS user_array FROM user;

结果：
| user_array | |---------------| | [1, "张三"] | | [2, "李四"] |

多行数据转JSON：`JSON_ARRAYAGG()`和`JSON_OBJECTAGG()`

场景3：将多行记录转为JSON数组（对象列表）

需求：将所有用户数据转为JSON数组，如[{"id": 1, "name": "张三"}, {"id": 2, "name": "李四"}]。

SQL：

SELECT JSON_ARRAYAGG(
  JSON_OBJECT('id', id, 'name', name)
) AS users_json
FROM user;

结果：
| users_json | |-------------------------------------------------------------| | [{"id": 1, "name": "张三"}, {"id": 2, "name": "李四"}] |

说明：JSON_ARRAYAGG()将多行结果聚合成JSON数组，每行由JSON_OBJECT()生成的对象组成。

场景4：将分组结果转为JSON对象（键为分组字段，值为聚合数组）

假设有一张订单表order：

CREATE TABLE `order` (
  `id` INT PRIMARY KEY,
  `user_id` INT,
  `product` VARCHAR(50),
  `amount` DECIMAL(10,2)
);

数据：
| id | user_id | product | amount | |----|---------|---------|--------| | 1 | 1 | 苹果 | 10.50 | | 2 | 1 | 香蕉 | 5.20 | | 3 | 2 | 橙子 | 8.00 |

需求：按user_id分组，将每个用户的订单转为JSON对象，如{"1": [{"product": "苹果", "amount": 10.50}, {"product": "香蕉", "amount": 5.20}], "2": [{"product": "橙子", "amount": 8.00}]}。

SQL：

SELECT 
  JSON_OBJECTAGG(
    user_id,
    JSON_ARRAYAGG(JSON_OBJECT('product', product, 'amount', amount))
  ) AS user_orders_json
FROM `order`;

结果：
| user_orders_json | |---------------------------------------------------------------------------------| | {"1": [{"product": "苹果", "amount": 10.50}, {"product": "香蕉", "amount": 5.20}], "2": [{"product": "橙子", "amount": 8.00}]} |

说明：JSON_OBJECTAGG()将分组字段作为键，聚合结果作为值,生成嵌套JSON对象。

其他实用JSON函数

JSON_ARRAY_LENGTH()：获取JSON数组长度，如SELECT JSON_ARRAY_LENGTH(JSON_ARRAY(1, 2, 3)); 返回3；
JSON_EXTRACT()：提取JSON中的字段，如SELECT JSON_EXTRACT('{"name": "张三"}', '$.name'); 返回"张三"；
JSON_UNQUOTE()：去除JSON字段的引号（如将"张三"转为张三），常与JSON_EXTRACT()配合使用：
```
SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name": "张三"}', '$.name')) AS name;
```

编程语言处理：从MySQL查询结果转JSON

如果需要在代码中处理MySQL到JSON的转换（例如结合业务逻辑），可以通过编程语言实现，以下是Python（常用）和Java的示例：

Python实现（使用`pymysql`+`json`）

安装依赖

pip install pymysql

示例代码

import pymysql
import json
# 连接MySQL数据库
connection = pymysql.connect(
    host='localhost',
    user='root',
    password='123456',
    database='test_db',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor  # 返回字典格式，方便转JSON
)
try:
    with connection.cursor() as cursor:
        # 查询用户数据
        sql = "SELECT id, name, age, email FROM user"
        cursor.execute(sql)
        result = cursor.fetchall()  # 获取所有行，结果为[{"id": 1, "name": "张三"}, ...]
        # 直接转JSON（Python字典转JSON字符串）
        json_data = json.dumps(result, ensure_ascii=False)  # ensure_ascii=False支持中文
        print("转换后的JSON数据：")
        print(json_data)
finally:
    connection.close()

输出：

[
  {"id": 1, "name": "张三", "age": 25, "email": "zhangsan@.com"},
  {"id": 2, "name": "李四", "age": 30, "email": "lisi@.com"}
]

关键点：使用DictCursor游标直接返回字典列表，避免手动拼接；json.dumps()的ensure_ascii=False确保中文不被转义为\u格式。

Java实现（使用`JDBC`+`Jackson`/`Gson`）

安装依赖（Maven）

<dependencies>
    <!-- MySQL驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.28</version>
    </dependency>
    <!-- JSON库（Jackson） -->
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
        <version>2.13.3</version>
    </dependency>
</dependencies>

正文

我的mysql怎么变成json