mysql如何保存json

MySQL中高效存储与操作JSON数据的全面指南**

随着Web应用和移动应用的日益复杂，以及对数据灵活性和可扩展性要求的不断提高，JSON（JavaScript Object Notation）因其轻量级、易读易写以及与JavaScript天然兼容的特性，已成为数据交换和存储的主流格式之一，MySQL从5.7版本开始正式对JSON数据类型提供了支持，使得在关系型数据库中高效地存储、查询和操作JSON数据成为可能，本文将详细介绍MySQL如何保存JSON数据，包括相关数据类型、操作函数、最佳实践以及注意事项。

MySQL中的JSON数据类型

MySQL提供了两种与JSON相关的数据类型：JSON 和 JSON 的衍生类型（虽然主要是JSON类型本身）。

JSON 数据类型：
- 这是MySQL 5.7及以后版本引入的核心类型,专门用于存储有效的JSON文档。
- 与VARCHAR或TEXT等字符串类型存储JSON字符串不同，JSON类型在存储时会进行验证，确保存储的内容是符合JSON格式的有效文档，如果插入无效的JSON,MySQL会拒绝并报错。
- JSON类型还会对存储的内容进行优化，使得后续的查询和操作（如路径提取、搜索）更加高效。
- 优势：
  - 数据完整性：确保只有有效的JSON才能被存储。
  - 查询效率：MySQL提供了专门的JSON函数和操作符,可以高效地提取和查询JSON内部的数据。
  - 存储优化：内部采用二进制格式存储,通常比纯文本JSON字符串更节省空间。
VARCHAR / TEXT 等字符串类型：
- 在MySQL 5.7之前，开发者通常使用VARCHAR或TEXT类型来存储JSON格式的字符串。
- 缺点：
  - 无验证：数据库不会验证字符串内容是否为有效的JSON，无效数据可能被存储,导致后续解析错误。
  - 查询效率低：查询JSON内部数据需要使用字符串函数（如SUBSTRING_INDEX, REGEXP等），复杂且性能低下,无法利用专门的JSON查询能力。
  - 缺乏JSON特定功能：无法使用MySQL的JSON函数和操作符。

除非有特殊需求，否则应优先选择JSON数据类型来存储JSON数据。

如何在MySQL中创建JSON列并插入数据

创建包含JSON列的表

在创建表时，可以直接将列的数据类型指定为JSON。

CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    profile_info JSON,
    preferences JSON
);

插入JSON数据

向JSON列插入数据时，需要确保数据是有效的JSON格式,MySQL支持以下几种插入方式：

直接插入JSON字符串：使用单引号或双引号包裹JSON字符串，注意，JSON字符串内部的双引号需要转义（使用\"）。

INSERT INTO user_profiles (username, profile_info, preferences)
VALUES ('john_doe', '{"name": "John Doe", "age": 30, "city": "New York"}', '{"theme": "dark", "notifications": true}');

使用JSON_OBJECT()函数创建JSON对象：当数据以键值对形式存在时，使用JSON_OBJECT()函数更为方便和安全,它自动处理转义。
```
INSERT INTO user_profiles (username, profile_info)
VALUES ('jane_smith', JSON_OBJECT('name', 'Jane Smith', 'age', 28, 'hobbies', JSON_ARRAY('reading', 'hiking')));
```
注意：JSON_ARRAY()用于创建JSON数组。

使用JSON_ARRAY()函数创建JSON数组：用于创建JSON数组类型的值。

INSERT INTO user_profiles (username, preferences)
VALUES ('bob_brown', JSON_ARRAY('light', 'email', 'sms'));

使用JSON_MERGE_PATCH()或JSON_MERGE_PRESERVE()合并JSON：（MySQL 8.0.3+）当需要合并多个JSON文档时可以使用。
插入空JSON：可以插入NULL或（空JSON对象）或'[]'（空JSON数组）。
```
INSERT INTO user_profiles (username, profile_info) VALUES ('new_user', '{}');
```

查询和操作JSON数据

MySQL提供了丰富的函数和操作符来查询和操作JSON数据，这使得我们可以灵活地提取、修改和搜索JSON文档内部的内容。

提取JSON数据

-> 操作符：提取JSON对象中指定键的值，结果为JSON类型（包括引号）。

SELECT profile_info -> '$.name' FROM user_profiles WHERE username = 'john_doe';
-- 结果: "John Doe"

->> 操作符：提取JSON对象中指定键的值，结果为字符串类型（去除引号）。

SELECT profile_info ->> '$.name' FROM user_profiles WHERE username = 'john_doe';
-- 结果: John Doe

JSON_EXTRACT() 函数：与->操作符功能相同，提取JSON路径对应的值,返回JSON类型。

SELECT JSON_EXTRACT(profile_info, '$.age') FROM user_profiles WHERE username = 'john_doe';
-- 结果: 30 (作为JSON数字)

JSON_UNQUOTE() 函数：去除JSON值的引号,返回字符串。

SELECT JSON_UNQUOTE(JSON_EXTRACT(profile_info, '$.name')) FROM user_profiles WHERE username = 'john_doe';
-- 结果: John Doe

常与JSON_EXTRACT()结合使用，效果等同于->>。

JSON_KEYS() 函数：返回JSON对象顶层键的数组。

SELECT JSON_KEYS(profile_info) FROM user_profiles WHERE username = 'john_doe';
-- 结果: ["name", "age", "city"]

JSON_LENGTH() 函数：返回JSON数组中元素的个数或JSON对象中键的个数。

SELECT JSON_LENGTH(profile_info) FROM user_profiles WHERE username = 'john_doe';
-- 结果: 3
SELECT JSON_LENGTH(preferences) FROM user_profiles WHERE username = 'john_doe';
-- 假设preferences是对象，结果为其键的数量

修改JSON数据

JSON_SET() 函数：在JSON文档中设置值，如果路径存在则更新,不存在则添加。

UPDATE user_profiles
SET profile_info = JSON_SET(profile_info, '$.age', 31, '$.country', 'USA')
WHERE username = 'john_doe';

JSON_INSERT() 函数：在JSON文档中插入值，仅当路径不存在时才添加,存在则不修改。

UPDATE user_profiles
SET profile_info = JSON_INSERT(profile_info, '$.country', 'USA')
WHERE username = 'john_doe';
-- .country已存在，则不会修改

JSON_REPLACE() 函数：替换JSON文档中已存在的路径的值,路径不存在则不操作。

UPDATE user_profiles
SET profile_info = JSON_REPLACE(profile_info, '$.age', 32)
WHERE username = 'john_doe';
-- .age不存在，则不会添加

JSON_REMOVE() 函数：从JSON文档中移除指定路径的值。

UPDATE user_profiles
SET profile_info = JSON_REMOVE(profile_info, '$.city')
WHERE username = 'john_doe';

搜索JSON数据

JSON_CONTAINS() 函数：检查JSON文档中是否包含指定的值或键。

-- 检查profile_info中是否包含键为"name"的值
SELECT JSON_CONTAINS(profile_info, '"John Doe"', '$.name') FROM user_profiles WHERE username = 'john_doe';
-- 检查profile_info中是否包含值"New York"（在顶层或任意位置）
SELECT JSON_CONTAINS(profile_info, '"New York"') FROM user_profiles WHERE username = 'john_doe';

JSON_CONTAINS_PATH() 函数：检查JSON文档中是否存在至少一个指定的路径。

SELECT JSON_CONTAINS_PATH(profile_info, 'one', '$.age', '$.country') FROM user_profiles WHERE username = 'john_doe';
-- 'one'表示至少有一个路径存在，'all'表示所有路径都存在