MySQL遇上JSON 一文处理Mysql中的JSON数据 Mysql如何处理嵌套的JSON结构

KK_crazy

已于 2024-04-26 18:21:56 修改

阅读量970

点赞数 20

文章标签： mysql json 数据库 intellij-idea java kafka spring cloud

于 2024-04-26 16:04:17 首次发布

本文链接：https://blog.csdn.net/weixin_42373241/article/details/138188971

版权

本文详细介绍了在MySQL中如何对JSON字符串进行增删改查操作，包括基本语法、高级函数应用、性能优化策略，并提供了实际案例分析。对于Java架构师来说，这是理解和使用MySQLJSON功能的重要参考。

摘要由CSDN通过智能技术生成

2024最全大厂面试题无需C币点我下载或者在网页打开全套面试题已打包

AI绘画关于SD,MJ,GPT,SDXL百科全书

2024面试题

2024最新面试合集链接

正文：

大家好，我是你们的老朋友，一位在Java架构领域摸爬滚打多年的架构师。今天，我要和大家分享一个非常实用且前沿的话题——在MySQL表中的字段对JSON字符串进行增删改查的语句汇总，以及它们的运行原理和应用场景。这篇文章将带你深入理解MySQL中的JSON数据类型，以及如何高效地进行操作。准备好了吗？让我们开始这段技术之旅！

1. 引言

在现代软件开发中，JSON格式因其轻量和易于阅读的特点，被广泛应用于数据交换。MySQL从5.7.8版本开始原生支持JSON数据类型，这为存储和操作JSON数据提供了极大的便利。作为一名高级Java架构师，掌握在MySQL中对JSON字符串进行增删改查的技能，无疑能大大提升我们的数据处理能力。

2. MySQL中的JSON数据类型

在深入了解如何操作JSON数据之前，我们首先需要了解MySQL中的JSON数据类型。JSON数据类型是一种用于存储JSON文档的字符串类型。MySQL提供了一系列的函数和操作符来帮助我们处理JSON类型的数据。

3. JSON数据的增删改查

3.1 JSON数据的增加

在MySQL中，我们可以使用标准的INSERT语句来增加包含JSON数据的记录。下面是一个简单的例子：

INSERT INTO my_table (json_column) VALUES ('{"key": "value"}');

3.2 JSON数据的查询

查询JSON数据时，我们可以使用JSON_EXTRACT函数来提取JSON对象中的某个字段：

SELECT JSON_EXTRACT(json_column, '$.key') FROM my_table;

3.3 JSON数据的更新

更新JSON数据时，我们可以使用JSON_SET函数来改变JSON对象中的某个字段：

UPDATE my_table SET json_column = JSON_SET(json_column, '$.new_key', 'new_value') WHERE id = 1;

3.4 JSON数据的删除

删除JSON对象中的某个字段，我们可以使用JSON_REMOVE函数：

UPDATE my_table SET json_column = JSON_REMOVE(json_column, '$.key') WHERE id = 1;

当然，下面我将提供一些更具体的SQL语句示例，这些示例将覆盖JSON数据的增加、查询、更新和删除操作。这些操作将帮助您更深入地理解如何在MySQL中使用JSON数据类型。

增加JSON数据

在MySQL中，您可以将JSON字符串直接插入到支持JSON类型的列中。

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    details JSON
);

INSERT INTO products (name, details) VALUES ('Laptop', '{"brand": "Dell", "specs": {"cpu": "i7", "ram": "16GB"}}');

查询JSON数据

查询JSON列中的特定路径可以使用JSON_EXTRACT函数。

SELECT id, name, JSON_EXTRACT(details, '$.brand') AS brand FROM products;

您也可以使用JSON_QUERY来获取整个JSON对象或数组。

SELECT JSON_QUERY(details, '$.specs') AS specs FROM products;

更新JSON数据

使用JSON_SET函数可以更新JSON对象中的某个键的值。

UPDATE products SET details = JSON_SET(details, '$.color', 'Black') WHERE id = 1;

如果需要合并两个JSON对象，可以使用JSON_MERGE。

UPDATE products SET details = JSON_MERGE(details, '{"warranty": "2 years"}') WHERE id = 1;

删除JSON数据

使用JSON_REMOVE函数可以从JSON对象中移除一个键。

UPDATE products SET details = JSON_REMOVE(details, '$.color') WHERE id = 1;

条件查询JSON数据

您可以使用JSON_CONTAINS或JSON_SEARCH来进行条件查询。

-- 查找包含特定键的记录
SELECT * FROM products WHERE JSON_CONTAINS(details, '"Black"', '$.color');

-- 查找包含某个字符串的记录
SELECT * FROM products WHERE JSON_SEARCH(details, 'one', 'warranty') IS NOT NULL;

聚合JSON数据

对JSON数据进行聚合操作也是可能的，比如统计某个键出现的次数。

SELECT id, (SELECT COUNT(*) FROM JSON_TABLE(details, '$[*]' COLUMNS (key VARCHAR PATH '$')) WHERE key = 'warranty') AS warranty_count FROM products;

在MySQL中对JSON数据去重通常涉及到对JSON字段中的数据进行解析，并根据解析后的数据进行去重操作。由于JSON字段是作为一个字符串存储的，MySQL没有直接的去重函数可以应用于JSON字段。但是，我们可以使用一些技巧和查询来实现去重的目的。

1. 使用JSON_TABLE进行去重

如果JSON字段中包含了数组，并且我们希望基于数组中的元素进行去重，可以使用JSON_TABLE函数将JSON数组转换为可以进行SQL操作的虚拟表，然后对虚拟表进行去重。

SELECT DISTINCT j.*
FROM your_table
CROSS JOIN JSON_TABLE(
  your_json_column, '$[*]' COLUMNS(
    id FOR ORDINALITY,
    value VARCHAR(255) PATH '$'
  )
) AS j;

这个查询会为your_json_column中的每个数组元素创建一个行，然后通过SELECT DISTINCT去重。

2. 使用JSON_EXTRACT去重

如果JSON字段是对象，并且我们希望基于对象中的某个键的值进行去重，可以使用JSON_EXTRACT函数来提取该键的值，然后去重。

SELECT DISTINCT JSON_EXTRACT(json_column, '$.key_to_use_for_distinct')
FROM your_table;

这个查询会返回所有不同的键值对中的键对应的值。

3. 使用GROUP BY去重

如果JSON字段是对象，并且我们希望基于整个JSON对象去重，可以使用GROUP BY语句。

SELECT id, json_column
FROM your_table
GROUP BY json_column;

这个查询会根据整个JSON字段对结果进行去重。

4. 去重并选择特定键

如果需要基于整个JSON对象去重，并且同时选择JSON对象中的特定键，可以结合使用JSON_EXTRACT和GROUP BY。

SELECT id, JSON_EXTRACT(json_column, '$.key_to_select') AS extracted_value
FROM your_table
GROUP BY json_column;

注意事项

JSON字段的去重操作可能非常依赖于JSON的结构和去重的具体需求。
在处理大型JSON字段或大量数据时，性能可能是一个需要考虑的因素。
确保在执行去重操作前，理解JSON字段的结构和内容。

在MySQL中操作JSON数据时，可以使用多种SQL语句来实现不同的功能。以下是一些额外的SQL语句示例，这些示例包括了查询、更新、插入和删除JSON数据的操作：

查询JSON数据

查询JSON对象中的所有键值对：

SELECT JSON_OBJECTAGG(key, value) AS json_object FROM (
  SELECT JSON_EXTRACT(details, CONCAT('$."', column_name, '"')) AS key, value
  FROM table_name
  WHERE some_condition
) AS subquery;

查询JSON数组中的元素：

SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT(details, '$[0]') AS first_element FROM table_name;

查询JSON文档中的特定路径：

SELECT JSON_EXTRACT(details, '$.store.book[0].author') AS author FROM table_name;

更新JSON数据

更新JSON对象中的单个键：

UPDATE table_name SET details = JSON_SET(details, '$.newKey', 'newValue') WHERE id = 1;

向JSON数组添加元素：

UPDATE table_name SET details = JSON_ARRAY_APPEND(details, '$', 'newElement') WHERE id = 1;

替换JSON数组中的元素：

UPDATE table_name SET details = JSON_REPLACE(details, '$[0]', 'newFirstElement') WHERE id = 1;

插入JSON数据

插入一个新的JSON对象：

INSERT INTO table_name (id, details) VALUES (1, '{"name": "John", "age": 30}');

插入一个新的JSON数组：

INSERT INTO table_name (id, details) VALUES (1, '["apple", "banana", "cherry"]');

删除JSON数据

从JSON数组中删除元素：

UPDATE table_name SET details = JSON_REMOVE(details, '$[1]') WHERE id = 1;

删除JSON对象中的键：

UPDATE table_name SET details = JSON_REMOVE(details, '$.name') WHERE id = 1;

条件查询JSON数据

查询包含特定键的JSON对象：

SELECT * FROM table_name WHERE JSON_CONTAINS(details, '"keyToLookFor"', '$');

查询JSON数组中包含特定文本的元素：

SELECT * FROM table_name WHERE JSON_SEARCH(details, 'one', 'textToSearch');

聚合JSON数据

计算JSON数组的长度：

SELECT id, JSON_LENGTH(details) AS array_length FROM table_name;

从JSON数组中获取所有不同的值：

SELECT DISTINCT JSON_EXTRACT_ARRAY_ELEMENT_TEXT(details, '$') AS element FROM table_name;

排序和过滤JSON数据

根据JSON对象中的值进行排序：

SELECT * FROM table_name ORDER BY JSON_EXTRACT(details, '$.age') DESC;

过滤JSON数组中的元素：

SELECT JSON_FILTER(details, '$', '. > 20') AS filtered_array FROM table_name;

这些SQL语句覆盖了JSON数据操作的许多方面，包括基本的CRUD操作、条件查询、聚合和排序。在实际应用中，您可能需要根据具体的业务逻辑和数据结构来调整这些语句。记得在执行这些操作之前，确保您的表和列已经正确地设置了JSON数据类型。

由于篇幅限制，我将分多次为您提供文章内容。以下是第二部分：

MySQL表字段与JSON字符串的增删改查：Java架构师的终极指南（续）

除了基本的增删改查操作，MySQL还提供了其他一些函数和操作符来处理JSON数据。以下是一些高级操作的例子：

使用JSON_SET更新多个值

UPDATE users SET profile = JSON_SET(profile, '$.age', 32, '$.address.zip', '10002') WHERE id = 1;

这个语句同时更新了age和address.zip两个字段。

使用JSON_INSERT插入新值

UPDATE users SET profile = JSON_INSERT(profile, '$.email', 'alice@example.com') WHERE id = 1;

这个语句在profile中插入了一个新的email字段。

使用JSON_REMOVE删除多个值

UPDATE users SET profile = JSON_REMOVE(profile, '$.address', '$.email') WHERE id = 1;

这个语句删除了address和email两个字段。

使用JSON_SEARCH查找特定值

SELECT JSON_SEARCH(profile, 'one', 'Alice') AS search_result FROM users WHERE id = 1;

这个语句查找profile中所有包含Alice的路径。

优化JSON字段的查询性能

JSON字段的查询性能可能会因为其复杂性和灵活性而受到影响。以下是一些优化JSON字段查询性能的建议：

使用索引

虽然MySQL 5.7及以上版本支持对JSON字段的索引，但索引的使用需要谨慎。由于JSON字段的复杂性，创建索引时应该只针对最常用的查询路径。

ALTER TABLE users ADD INDEX idx_profile_age ((CAST(profile->>'$.age' AS UNSIGNED)));

这个语句为profile字段中的age路径创建了一个索引。

避免使用JSON_EXTRACT

在查询中尽量避免使用JSON_EXTRACT函数，因为它会遍历整个JSON对象。如果可能，尽量使用JSON_UNQUOTE和JSON_SEARCH等函数来减少查询的复杂性。

使用JSON_TABLE

MySQL 8.0及以上版本引入了JSON_TABLE函数，它可以将JSON数据转换为一个虚拟的表，然后可以像查询普通表一样查询这个虚拟表。

SELECT jt.name, jt.age FROM users, JSON_TABLE(profile, '$' COLUMNS (name VARCHAR(255) PATH '$.name', age INT PATH '$.age')) AS jt WHERE id = 1;

这个语句将profile字段转换为一个虚拟表，并从中选择name和age字段。

这里提供的是一些基础和常用的JSON操作SQL语句。实际上，JSON在MySQL中的使用可以非常复杂和强大，具体取决于您的应用场景和需求。

JSON_TABLE 函数是 MySQL 8.0 版本引入的一个强大的功能，它允许我们将 JSON 文档转换为关系型数据表，这样就可以使用 SQL 查询来访问和操作 JSON 文档中的数据。JSON_TABLE 函数的基本语法如下：

JSON_TABLE(json_doc, path COLUMNS
  (column_name column_type PATH path_as_string) [AS alias]
  [, column_name column_type PATH path_as_string] ...
)

json_doc 是 JSON 文档的路径或表达式。
path 是 JSON 文档中要转换的路径。
COLUMNS 定义了如何从 JSON 文档中提取数据。
column_name 是结果表中的列名。
column_type 是结果表中列的数据类型。
path_as_string 是 JSON 文档中数据的路径表达式。
alias 是结果表的别名。

下面是一个使用 JSON_TABLE 函数的例子：

假设我们有一个 JSON 文档存储在 users 表的 profile 字段中，如下所示：

{
  "name": "Alice",
  "age": 30,
  "address": {
    "city": "New York",
    "zip": "10001"
  },
  "hobbies": ["reading", "swimming", "hiking"]
}

我们想要查询这个 JSON 文档中的 name、age 和 city 字段。我们可以使用 JSON_TABLE 函数来实现：

SELECT jt.*
FROM users,
JSON_TABLE(
  profile,
  '$' COLUMNS (
    name VARCHAR(255) PATH '$.name',
    age INT PATH '$.age',
    city VARCHAR(255) PATH '$.address.city'
  )
) AS jt;

这个查询将返回一个虚拟表 jt，其中包含了从 profile 字段中提取的 name、age 和 city 字段。JSON_TABLE 函数将 JSON 文档转换为一个关系型数据表，使得我们可以像查询普通表一样查询 JSON 文档中的数据。

请注意，JSON_TABLE 函数的使用可能会受到 MySQL 版本的限制，因此在使用之前，请确保您的 MySQL 版本支持该函数。此外，JSON_TABLE 函数的性能可能会受到 JSON 文档大小和复杂性的影响，因此在设计查询时应该考虑到这一点。

处理嵌套的 JSON 结构时，JSON_TABLE 函数提供了强大的功能来提取和查询这些数据。在嵌套的 JSON 结构中，您可能需要访问内部对象或数组中的数据。JSON_TABLE 函数允许您指定路径表达式来访问这些嵌套的数据。

以下是一个处理嵌套 JSON 结构的例子：

假设我们有一个 JSON 文档存储在 users 表的 profile 字段中，如下所示：

{
   "name": "Alice",
   "age": 30,
   "address": {
     "city": "New York",
     "zip": "10001",
     "coordinates": {
       "lat": 40.712776,
       "lng": -74.005974
     }
   },
   "hobbies": ["reading", "swimming", "hiking"]
}

我们想要查询这个 JSON 文档中的 name、age、city 和 coordinates 中的 lat 和 lng 字段。我们可以使用 JSON_TABLE 函数来实现：

SELECT jt.*
FROM users,
JSON_TABLE(
   profile,
   '$' COLUMNS (
     name VARCHAR(255) PATH '$.name',
     age INT PATH '$.age',
     city VARCHAR(255) PATH '$.address.city',
     coordinates STRUCT(
       lat DOUBLE PATH '$.address.coordinates.lat',
       lng DOUBLE PATH '$.address.coordinates.lng'
     ) PATH '$.address.coordinates'
   )
) AS jt;

在这个例子中，我们使用了 STRUCT 关键字来定义一个结构体，它包含了 lat 和 lng 字段。我们还使用了 PATH 子句来指定这些字段在 JSON 文档中的路径。

JSON_TABLE 函数将 JSON 文档转换为一个关系型数据表，其中包含了 name、age、city 和 coordinates 结构体。coordinates 结构体进一步包含了 lat 和 lng 字段。

在 MySQL 中处理 JSON 数组中的数据时，您可以使用 JSON_TABLE 函数来将 JSON 数组转换为关系型数据表的行。这样，您就可以使用 SQL 查询来访问和操作数组中的每个元素。

以下是一个处理 JSON 数组的例子：

假设我们有一个 JSON 文档存储在 users 表的 profile 字段中，如下所示：

{
    "name": "Alice",
    "age": 30,
    "hobbies": ["reading", "swimming", "hiking"]
}

我们想要查询这个 JSON 文档中的 name 和 hobbies 数组中的每个元素。我们可以使用 JSON_TABLE 函数来实现：

SELECT jt.*
FROM users,
JSON_TABLE(
    profile,
    '$' COLUMNS (
        name VARCHAR(255) PATH '$.name',
        hobbies ARRAY PATH '$.hobbies' COLUMNS (
            hobby VARCHAR(255) PATH '$'
        )
    )
) AS jt;

在这个例子中，我们使用了 ARRAY PATH 子句来指定 hobbies 是一个数组，并且我们使用了 COLUMNS 子句来定义数组中的每个元素应该被转换成的列。在这个例子中，每个 hobby 都是一个 VARCHAR(255) 类型的列。

JSON_TABLE 函数将 JSON 文档转换为一个关系型数据表，其中包含了 name 和 hobbies 数组中的每个元素。hobbies 数组中的每个元素都被转换为 hobby 列。

4. 运行原理解析

MySQL中的JSON操作依赖于其内部的JSON解析器，它能够解析JSON格式的字符串，并根据我们的指令进行相应的操作。这些操作都是原子性的，意味着它们要么完全执行，要么完全不执行，这保证了数据的一致性。

5. 应用场景

JSON数据类型在许多场景下都非常有用，比如：

配置存储：应用程序的配置信息可以用JSON格式存储在数据库中。
多语言支持：不同语言环境下的数据可以用JSON统一存储和交换。
动态数据结构：JSON的灵活性允许我们存储结构不固定的数据。

6. 实际案例分析

为了更好地理解这些概念，让我们通过一个实际的案例来分析。假设我们有一个电子商务平台的数据库，我们需要存储产品信息，包括产品描述、价格和可选的促销信息。

7. 结语

在这篇文章中，我们探讨了MySQL中JSON数据类型的增删改查操作，以及它们的运行原理和应用场景。JSON数据类型为数据库设计带来了新的灵活性和可能性，作为一名Java架构师，掌握这些技能将极大地提升你的技术实力。

KK_crazy

关注

20
点赞
踩
29

收藏

觉得还不错? 一键收藏
1
评论
MySQL遇上JSON 一文处理Mysql中的JSON数据 Mysql如何处理嵌套的JSON结构

在这篇文章中，我们探讨了MySQL中JSON数据类型的增删改查操作，以及它们的运行原理和应用场景。JSON数据类型为数据库设计带来了新的灵活性和可能性，作为一名Java架构师，掌握这些技能将极大地提升你的技术实力。
复制链接

扫一扫