JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，MySQL 从 5.7+版本开始支持JSON数据类型，并提供了丰富的JSON操作函数。本文将详细介绍JSON数据操作函数和JSON函数的应用，并通过具体示例帮助你掌握JSON的高效使用方法。
1 JSON数据操作函数
MySQL提供了多种JSON处理函数，主要包括提取、修改、构建JSON等功能。
1.1 json_extract()与json_unquote()
- json_extract(json_doc, path)：从 JSON 文档中提取指定路径的值
- json_unquote(json_val)：去除 JSON 字符串的引号，返回纯文本
-- 提取JSON字段值
-- 示例数据
select json_extract('{"name": "张三", "age": 25}', '$.name') as name;
-- 输出: "张三"（带引号）
mysql> select json_extract('{"name": "张三", "age": 25}', '$.name') as name;
+----------+
| name |
+----------+
| "张三" |
+----------+
1 row in set (0.01 sec)mysql> -- 使用json_unquote去掉引号
select json_unquote(json_extract('{"name": "张三", "age": 25}', '$.name')) as name;
-- 输出: 张三（纯文本）
mysql> select json_unquote(json_extract('{"name": "张三", "age": 25}', '$.name')) as name;
+--------+
| name |
+--------+
| 张三 |
+--------+
1 row in set (0.01 sec)mysql> 1.2 json_array()与json_object()
- json_array(val1, val2, ...)：构建JSON数组
- json_object(key1, val1, key2, val2, ...)：构建JSON对象
-- 构建json数组
select json_array(1, 'mysql', true, null) as json_array;
-- 输出: [1, "mysql", true, null]
mysql> select json_array(1, 'mysql', true, null) as json_array;
+--------------------------+
| json_array |
+--------------------------+
| [1, "mysql", true, null] |
+--------------------------+
1 row in set (0.01 sec)mysql> -- 构建json对象
select json_object('id', 1, 'name', '张三', 'is_active', true) as json_obj;
-- 输出: {"id": 1, "name": "张三", "is_active": true}
mysql> select json_object('id', 1, 'name', '张三', 'is_active', true) as json_obj;
+------------------------------------------------+
| json_obj |
+------------------------------------------------+
| {"id": 1, "name": "张三", "is_active": true} |
+------------------------------------------------+
1 row in set (0.00 sec)mysql> 2 JSON 函数在实际开发中的应用
2.1 解析嵌套JSON字段
2.1.1 数据准备
create table user (user_id int primary key,profile_data json
);insert into user values
(1, '{"name": "张三", "contact": {"email": "zhangsan@example.com", "phone": "13800138000"}}'),
(2, '{"name": "李四", "contact": {"email": "lisi@example.com", "phone": null}}');
commit;2.1.2 查询嵌套字段
-- 查询所有用户的邮箱
select user_id,profile_data ->> '$.name' as name,profile_data ->> '$.contact.email' as email
from user;mysql> select -> user_id,-> profile_data ->> '$.name' as name,-> profile_data ->> '$.contact.email' as email-> from user;
+---------+--------+----------------------+
| user_id | name | email |
+---------+--------+----------------------+
| 1 | 张三 | zhangsan@example.com |
| 2 | 李四 | lisi@example.com |
+---------+--------+----------------------+
2 rows in set (0.00 sec)mysql> 2.2 存储和查询复杂结构数据
2.2.1 数据准备
create table orders (order_id int primary key,order_info json
);insert into orders values
(1001, '{"order_date": "2023-10-01","customer": "张三","items": [{"product_id": 101, "name": "手机", "price": 5999, "quantity": 1},{"product_id": 205, "name": "耳机", "price": 399, "quantity": 2}]
}');
commit;2.2.2 数据查询
-- 提取第一个商品名称
select order_id,order_info ->> '$.customer' as customer,order_info -> '$.items[0].name' as first_product
from orders;mysql> select -> order_id,-> order_info ->> '$.customer' as customer,-> order_info -> '$.items[0].name' as first_product-> from orders;
+----------+----------+---------------+
| order_id | customer | first_product |
+----------+----------+---------------+
| 1001 | 张三 | "手机" |
+----------+----------+---------------+
1 row in set (0.01 sec)mysql> 3 总结
| 场景 | 推荐函数 |
| 提取JSON字段 | json_extract() |
| 构建JSON | json_array()，json_object() |
| 解析嵌套JSON | json_table()(MySQL 8.0+) |
| 存储动态结构数据 | 直接使用JSON数据类型 |