mysql中json怎么写

MySQL中JSON数据类型的使用与操作指南

在MySQL 5.7及以上版本中，JSON数据类型的引入极大地简化了存储和操作JSON格式数据的过程，相比传统的TEXT或VARCHAR类型存储JSON字符串，MySQL原生JSON类型提供了更高效的数据存储、查询和验证能力，支持直接在数据库层面进行JSON数据的解析、修改和索引，特别适合处理半结构化数据（如配置信息、日志、动态属性等），本文将详细介绍MySQL中JSON数据类型的定义、写入、查询、修改及索引等核心操作。

JSON数据类型的定义与优势

MySQL中的JSON数据类型是一种专门用于存储JSON文档的二进制格式，与传统的TEXT/VARCHAR存储JSON字符串相比,具有以下核心优势：

1. 高效存储：JSON数据以二进制格式存储，比文本格式更节省空间，且读写性能更优。
2. 数据验证：插入数据时，MySQL会自动验证格式是否符合JSON规范，无效数据会被拒绝。
3. 原生支持：提供丰富的JSON函数（如JSON_EXTRACT、JSON_SET、JSON_ARRAY等），可直接在SQL中操作JSON字段，无需应用层解析。
4. 部分索引：支持对JSON文档中的特定字段创建索引，提升查询效率。

MySQL中JSON数据的写入方式

创建包含JSON字段的表

在创建表时，直接使用JSON类型定义字段即可，创建一个存储用户信息的表，其中profile字段用于存储用户的动态属性（如爱好、地址等）：

CREATE TABLE `user` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL,
  `profile` JSON  -- 定义JSON类型字段
);

插入JSON数据

插入JSON数据有两种方式：直接插入合法的JSON字符串,或使用MySQL提供的JSON构造函数生成JSON对象。

直接插入JSON字符串

需确保字符串符合JSON规范（使用双引号包裹键和值，布尔值为true/false，null为null等）：

INSERT INTO `user` (username, profile) 
VALUES 
('张三', '{"age": 25, "city": "北京", "hobbies": ["阅读", "游泳"]}'),
('李四', '{"age": 30, "city": "上海", "hobbies": ["篮球", "旅行"], "contact": {"email": "lisi@example.com"}}');

使用JSON构造函数

MySQL提供了多种JSON构造函数,更灵活且能避免手动拼接字符串的错误：

• JSON_OBJECT(key1, value1, key2, value2, ...)：创建JSON对象（键值对集合）。
• JSON_ARRAY(value1, value2, ...)：创建JSON数组（有序值列表）。
• JSON_ARRAYAGG(value)：聚合结果为JSON数组（常用于分组查询）。
• JSON_OBJECTAGG(key, value)：聚合结果为JSON对象（键为分组字段，值为聚合值）。

示例：使用构造函数插入数据

INSERT INTO `user` (username, profile) 
VALUES 
('王五', JSON_OBJECT('age', 28, 'city': '广州', 'hobbies', JSON_ARRAY('跑步', '摄影'))),
('赵六', JSON_OBJECT('age', 35, 'city': '深圳', 'contact', JSON_OBJECT('phone', '13800138000', 'address', '南山区')));

注意事项

• JSON字符串中的键必须用双引号（）包裹，单引号（）会导致语法错误。
• 布尔值必须是小写的true/false，不能使用TRUE/False或1/0。
• 插入的数据必须符合JSON标准，否则会报错（如{"key": "value"}合法，{'key': 'value'}非法）。

MySQL中JSON数据的查询与解析

基本查询：直接查询JSON字段

直接查询JSON字段会返回完整的JSON字符串：

SELECT username, profile FROM `user` WHERE id = 1;

结果：

+----------+--------------------------------------------------+
| username | profile                                          |
+----------+--------------------------------------------------+
| 张三     | {"age": 25, "city": "北京", "hobbies": ["阅读", "游泳"]} |
+----------+--------------------------------------------------+

解析JSON数据：使用->和->>操作符

MySQL提供了两个JSON路径操作符,用于提取JSON数据中的特定值：

• ->：提取JSON数据，返回JSON格式（结果带引号，类型为JSON）。
• ->>：提取JSON数据，返回MySQL原生数据类型（结果不带引号，类型为VARCHAR/INT等）。

示例1：提取JSON对象的某个键值

-- 提取profile中的age（返回JSON类型，结果为"25"）
SELECT profile -> '$.age' FROM `user` WHERE username = '张三';
-- 提取profile中的age（返回INT类型，结果为25）
SELECT profile ->> '$.age' FROM `user` WHERE username = '张三';

示例2：提取JSON数组中的元素

JSON数组通过索引访问（从0开始）：

-- 提取hobbies数组的第一个元素（返回JSON类型，结果为"阅读"）
SELECT profile -> '$.hobbies[0]' FROM `user` WHERE username = '张三';
-- 提取hobbies数组的第二个元素（返回VARCHAR类型，结果为"游泳"）
SELECT profile ->> '$.hobbies[1]' FROM `user` WHERE username = '张三';

示例3：嵌套JSON数据查询

对于嵌套的JSON对象（如contact.email）,使用路径表达式逐层访问：

-- 提取contact中的email（返回JSON类型）
SELECT profile -> '$.contact.email' FROM `user` WHERE username = '李四';
-- 提取contact中的phone（返回VARCHAR类型）
SELECT profile ->> '$.contact.phone' FROM `user` WHERE username = '赵六';

路径表达式说明

• 路径以开头，表示JSON文档的根节点。
• 使用访问对象键（如$.age），使用[索引]访问数组元素（如$.hobbies[0]）。
• 嵌套结构用连接（如$.contact.email）。

使用JSON函数进行复杂查询

MySQL提供了丰富的JSON函数,支持更灵活的数据操作：

• JSON_EXTRACT(json_doc, path)：提取JSON数据（功能与->相同，但更明确）。
• JSON_UNQUOTE(json_doc)：去除JSON值的引号（功能与->>类似，常配合JSON_EXTRACT使用）。
• JSON_CONTAINS(json_doc, candidate)：检查JSON文档是否包含指定值。
• JSON_SEARCH(json_doc, one/all, search_str)：搜索指定值在JSON中的路径。

示例1：使用JSON_EXTRACT和JSON_UNQUOTE

-- 提取所有用户的年龄（去除引号后转为INT）
SELECT 
    username, 
    JSON_UNQUOTE(JSON_EXTRACT(profile, '$.age')) AS age 
FROM `user`;

示例2：条件查询：筛选JSON中特定条件的记录

-- 查询年龄大于28的用户
SELECT username, profile ->> '$.age' AS age 
FROM `user` 
WHERE CAST(profile ->> '$.age' AS UNSIGNED) > 28;
-- 查询hobbies包含"游泳"的用户
SELECT username 
FROM `user` 
WHERE JSON_CONTAINS(profile -> '$.hobbies', '"游泳"');  -- 注意：搜索值需用JSON格式（双引号包裹）

示例3：搜索JSON中的值

-- 查找profile中包含"北京"的用户，返回值的路径
SELECT username, JSON_SEARCH(profile, 'one', '北京') AS path 
FROM `user` 
WHERE JSON_SEARCH(profile, 'one', '北京') IS NOT NULL;

MySQL中JSON数据的修改

MySQL支持直接通过SQL语句修改JSON字段中的数据,常用函数包括：

• JSON_SET(json_doc, path, value, ...)：设置JSON数据，若路径存在则覆盖，不存在则添加。
• JSON_INSERT(json_doc, path, value, ...)：插入JSON数据，仅当路径不存在时生效。
• JSON_REPLACE(json_doc, path, value, ...)：替换JSON数据，仅当路径存在时生效。
• JSON_REMOVE(json_doc, path, ...)：删除JSON数据中的指定路径。

示例1：修改JSON对象的键值

-- 将张三的年龄修改为26（使用JSON_SET，若$.age存在则覆盖）
UPDATE `user` 
SET profile = JSON_SET(profile