mysql怎么解析json

MySQL如何解析JSON数据：从基础到实战技巧

在现代应用开发中,JSON（JavaScript Object Notation）已成为数据交换的主流格式之一，因其轻量、易读且结构灵活，被广泛应用于API响应、配置文件、日志存储等场景，作为最受欢迎的开源关系型数据库，MySQL从5.7版本开始正式支持JSON数据类型，并提供了丰富的函数和方法来解析、查询和操作JSON数据，本文将详细介绍MySQL中解析JSON的核心方法、实用技巧及实战案例，帮助开发者高效处理JSON数据。

MySQL JSON数据类型基础

在解析JSON之前,需要先了解MySQL的JSON数据类型，与传统的VARCHAR或TEXT类型存储JSON字符串不同，MySQL的JSON类型是原生类型，会对存储的JSON文档进行语法验证和结构化存储，确保数据的合法性和高效查询。

JSON类型的优势

• 自动验证：插入数据时，MySQL会检查是否符合JSON规范（如正确的括号匹配、转义字符等），非法数据会被拒绝。
• 优化存储：JSON数据以二进制格式存储，比文本类型更节省空间，且查询性能更优。
• 支持路径查询：可通过JSON路径表达式直接定位数据，避免全表扫描。

JSON解析核心函数

MySQL提供了一系列内置函数来解析JSON数据,主要分为查询函数（提取数据）、修改函数（更新数据）和辅助函数（类型判断、路径处理等），以下是最常用的解析函数：

查询类函数：提取JSON数据

（1）JSON_EXTRACT(json_doc, path)

作用：从JSON文档中提取指定路径的数据，返回JSON格式结果。
语法：JSON_EXTRACT(列名, '$.路径')
示例：假设有一张user表，包含id和profile（JSON类型）字段，profile存储用户信息：

CREATE TABLE user (
    id INT PRIMARY KEY,
    profile JSON
);
INSERT INTO user VALUES 
(1, '{"name": "张三", "age": 25, "contact": {"email": "zhangsan@example.com", "phone": "13800138000"}, "tags": ["编程", "MySQL"]}'),
(2, '{"name": "李四", "age": 30, "contact": {"email": "lisi@example.com", "phone": "13900139000"}, "tags": ["数据库", "Java"]}');

提取用户“张三”的姓名：

SELECT id, JSON_EXTRACT(profile, '$.name') AS name FROM user WHERE id = 1;

结果：

+----+-----------------+
| id | name            |
+----+-----------------+
| 1  | "张三"          |
+----+-----------------+

注意：JSON_EXTRACT返回的是JSON类型（带双引号的字符串），若需直接获取原始值（如张三而非"张三"），可结合->>操作符（见下文）。

（2）-> 和 ->> 操作符

MySQL提供了两个简化的JSON提取操作符,比JSON_EXTRACT更简洁：

• ->：等价于JSON_EXTRACT，返回JSON类型结果。
• ->>：返回原始值（去掉JSON双引号），适用于提取字符串、数字等简单类型。

示例：

-- 使用 -> 提取JSON类型
SELECT id, profile->'$.name' AS name_json FROM user WHERE id = 1;
-- 结果：name_json 为 "张三"（带双引号）
-- 使用 ->> 提取原始值
SELECT id, profile->>'$.name' AS name_raw FROM user WHERE id = 1;
-- 结果：name_raw 为 张三（无双引号）
-- 提取嵌套JSON（如联系方式）
SELECT id, profile->'$.contact' AS contact FROM user WHERE id = 1;
-- 结果：contact 为 {"email": "zhangsan@example.com", "phone": "13800138000"}
-- 提取数组元素（tags是数组，索引从0开始）
SELECT id, profile->'$.tags[0]' AS first_tag FROM user WHERE id = 1;
-- 结果：first_tag 为 "编程"

（3）JSON_UNQUOTE(json_doc)

作用：去掉JSON值的双引号，将JSON类型转换为字符串、数字等原始类型。
语法：JSON_UNQUOTE(JSON_EXTRACT(json_doc, path))
等价于操作符：JSON_UNQUOTE(profile->'$.name') 等价于 profile->>'$.name。

示例：

SELECT id, JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) AS name FROM user WHERE id = 1;
-- 结果与 profile->>'$.name' 相同

（4）JSON_SEARCH(json_doc, one/all, search_str[, path[, escape_char]])

作用：在JSON文档中搜索指定字符串，返回匹配的路径（若找到）或NULL（若未找到）。

• one：返回第一个匹配路径；all：返回所有匹配路径（数组格式）。
• path：可选，指定搜索的子路径，缩小搜索范围。

示例：

-- 搜索所有包含"MySQL"的用户
SELECT id, JSON_SEARCH(profile, 'one', 'MySQL') AS match_path FROM user WHERE id = 1;
-- 结果：match_path 为 "$.tags[1]"
-- 搜索所有包含"数据库"的用户（返回所有匹配路径）
SELECT id, JSON_SEARCH(profile, 'all', '数据库') AS match_paths FROM user WHERE id = 2;
-- 结果：match_paths 为 ["$.tags[1]"]

修改类函数：更新JSON数据

解析JSON不仅需要查询,还需要动态修改数据，MySQL提供了JSON_SET、JSON_INSERT、JSON_REPLACE等函数来更新JSON文档。

（1）JSON_SET(json_doc, path, value[, path, value, ...])

作用：在JSON文档中设置值，若路径存在，则覆盖原值；若路径不存在，则添加新值。

示例：修改用户“张三”的年龄为26，添加地址字段：

UPDATE user 
SET profile = JSON_SET(profile, '$.age', 26, '$.address', '北京市朝阳区') 
WHERE id = 1;
SELECT * FROM user WHERE id = 1;
-- 结果：profile 变为 {"name": "张三", "age": 26, "contact": {...}, "tags": [...], "address": "北京市朝阳区"}

（2）JSON_INSERT(json_doc, path, value[, path, value, ...])

作用：插入值，仅当路径不存在时才插入，若路径已存在，则保留原值。

示例：尝试为用户“张三”插入“name”（已存在，不修改）和“gender”（不存在，新增）：

UPDATE user 
SET profile = JSON_INSERT(profile, '$.name', '新名字', '$.gender', '男') 
WHERE id = 1;
SELECT * FROM user WHERE id = 1;
-- 结果：name 仍为 "张三"，gender 新增为 "男"

（3）JSON_REPLACE(json_doc, path, value[, path, value, ...])

作用：替换值，仅当路径存在时才替换，若路径不存在，则忽略。

示例：仅替换用户“张三”的邮箱（原路径存在）：

UPDATE user 
SET profile = JSON_REPLACE(profile, '$.contact.email', 'new_zhangsan@example.com') 
WHERE id = 1;
SELECT profile->'$.contact.email' FROM user WHERE id = 1;
-- 结果：new_zhangsan@example.com

辅助函数：类型判断与路径处理

（1）JSON_TYPE(json_doc)

作用：返回JSON值的类型（如OBJECT、ARRAY、STRING、INT等）。

示例：

SELECT 
    JSON_TYPE(profile->'$.name') AS name_type,  -- STRING
    JSON_TYPE(profile->'$.age') AS age_type,    -- INT
    JSON_TYPE(profile->'$.tags') AS tags_type,  -- ARRAY
    JSON_TYPE(profile->'$.contact') AS contact_type  -- OBJECT
FROM user WHERE id = 1;

（2）JSON_VALID(json_doc)

作用：检查JSON文档是否合法，返回1（合法）或0（非法）。

示例：

SELECT JSON_VALID('{"name": "张三"}') AS valid1,  -- 1
       JSON_VALID('{"name": "张三"') AS valid2;   -- 0