我的mysql怎么变成json

我的MySQL怎么变成JSON：数据转换全攻略

在日常开发中，我们经常需要将MySQL中的数据转换为JSON格式——无论是为了API接口返回、数据迁移，还是满足前端的数据结构需求，MySQL从5.7版本开始原生支持JSON类型，8.0版本进一步优化了JSON操作函数，让数据转换变得更加高效，本文将详细介绍MySQL数据转JSON的多种方法，从基础查询到复杂场景处理，帮你彻底搞懂“我的MySQL怎么变成JSON”。

为什么需要将MySQL数据转JSON？

在开始转换之前，我们先明确一下常见需求场景：

接口开发：后端MySQL数据需按JSON格式返回给前端（如RESTful API的响应体）；
数据交互：与其他系统（如微服务、大数据平台）对接时，JSON作为通用数据格式更易传输；
配置存储：将复杂配置（如用户权限、系统参数）以JSON形式存入MySQL，便于动态解析；
数据分析：结合JSON函数灵活提取数据，简化复杂查询逻辑。

基础转换：单表数据转JSON数组

场景：将单表数据转换为JSON数组，每个元素对应一行记录

假设我们有一张users表，结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);
INSERT INTO users VALUES (1, '张三', 25, 'zhangsan@example.com');
INSERT INTO users VALUES (2, '李四', 30, 'lisi@example.com');
INSERT INTO users VALUES (3, '王五', 28, 'wangwu@example.com');

我们需要将所有用户数据转换为如下JSON数组：

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

方法1：使用`JSON_ARRAY`和`JSON_OBJECT`（MySQL 5.7+）

通过循环拼接每行数据为JSON对象，再用JSON_ARRAY包裹：

SELECT JSON_ARRAY(
    JSON_OBJECT('id', id, 'name', name, 'age', age, 'email', email),
    JSON_OBJECT('id', id, 'name', name, 'age', age, 'email', email),
    JSON_OBJECT('id', id, 'name', name, 'age', age, 'email', email)
) AS users_json FROM users;

缺点：需要手动写每行数据,表字段多或数据量大时极不实用。

方法2：使用`GROUP_CONCAT` + `JSON_OBJECT`（经典方案）

通过GROUP_CONCAT将每行的JSON对象拼接成字符串，再用JSON_ARRAY转换为数组：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT('id', id, 'name', name, 'age', age, 'email', email)
) AS users_json FROM users;

说明：

JSON_OBJECT(key1, value1, key2, value2, ...)：将键值对转换为JSON对象；
JSON_ARRAYAGG(...)：将多个JSON对象聚合成JSON数组（MySQL 5.7+支持）；
结果直接返回目标JSON格式,无需手动拼接。

方法3：使用`GROUP_CONCAT` + `CONCAT`（兼容旧版本）

如果MySQL版本低于5.7（如5.6），可以使用CONCAT手动构造JSON字符串：

SELECT CONCAT(
    '[',
    GROUP_CONCAT(
        CONCAT(
            '{"id": ', id, ', "name": "', name, '", "age": ', age, ', "email": "', email, '"}'
        )
    ),
    ']'
) AS users_json FROM users;

注意：此方法需要手动处理JSON字符串的转义（如字段中的双引号）,否则可能导致JSON格式错误。

进阶转换：关联表数据转嵌套JSON

场景：将关联表数据转换为嵌套JSON结构

假设users表关联orders表（用户订单），结构如下：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    product VARCHAR(50),
    price DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);
INSERT INTO orders VALUES (1, 1, '手机', 2999.00);
INSERT INTO orders VALUES (2, 1, '耳机', 199.00);
INSERT INTO orders VALUES (3, 2, '电脑', 5999.00);

我们需要将用户及其订单数据转换为如下嵌套JSON：

[
    {
        "id": 1,
        "name": "张三",
        "age": 25,
        "email": "zhangsan@example.com",
        "orders": [
            {"id": 1, "product": "手机", "price": 2999.00},
            {"id": 2, "product": "耳机", "price": 199.00}
        ]
    },
    {
        "id": 2,
        "name": "李四",
        "age": 30,
        "email": "lisi@example.com",
        "orders": [
            {"id": 3, "product": "电脑", "price": 5999.00}
        ]
    }
]

方法：使用`JSON_ARRAYAGG` + 子查询（MySQL 5.7+）

通过子查询获取每个用户的订单，再用JSON_ARRAYAGG嵌套到用户数据中：

SELECT 
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', u.id,
            'name', u.name,
            'age', u.age,
            'email', u.email,
            'orders', (
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT('id', o.id, 'product', o.product, 'price', o.price)
                ) FROM orders o WHERE o.user_id = u.id
            )
        )
    ) AS users_json
FROM users u;

说明：

子查询SELECT ... FROM orders o WHERE o.user_id = u.id获取当前用户的订单列表；
内层JSON_ARRAYAGG将订单转换为JSON数组，外层JSON_OBJECT将其作为“orders”字段嵌入用户对象；
最终JSON_ARRAYAGG将所有用户聚合成JSON数组。

复杂场景处理：动态字段与条件转换

场景1：只转换部分字段，且字段名动态映射

假设我们只需要返回用户的id和name，并将字段名改为userId和userName：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT('userId', id, 'userName', name)
) AS users_json FROM users;

输出：

[
    {"userId": 1, "userName": "张三"},
    {"userId": 2, "userName": "李四"},
    {"userId": 3, "userName": "王五"}
]

场景2：根据条件动态添加字段

只返回年龄大于25的用户，并添加“isAdult”字段：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'id', id,
        'name', name,
        'age', age,
        'isAdult', CASE WHEN age > 25 THEN TRUE ELSE FALSE END
    )
) AS users_json FROM users WHERE age > 25;

输出：

[
    {"id": 2, "name": "李四", "age": 30, "isAdult": true},
    {"id": 3, "name": "王五", "age": 28, "isAdult": true}
]

场景3：处理NULL值与默认值

如果某些字段可能为NULL，可以使用COALESCE设置默认值：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'id', id,
        'name', COALESCE(name, '未知用户'),
        'age', COALESCE(age, 0)
    )
) AS users_json FROM users;

说明：COALESCE(value1, value2, ...)返回第一个非NULL值，避免JSON中出现null导致解析问题。

性能优化与注意事项

避免大结果集的JSON转换

JSON_ARRAYAGG和JSON_OBJECT在处理大量数据（如百万级记录）时可能占用大量内存，导致查询缓慢，建议：

分页查询：用LIMIT分批获取数据，再转换为JSON；
只选择必要字段：避免SELECT *,减少数据传输量。

注意JSON字段的索引

如果MySQL表中已存在

正文

我的mysql怎么变成json