MySQL中JSON数据处理方法与实用技巧

## 介绍 这个实验的核心目标，是把 MySQL 中的 JSON 数据类型真正应用到实际操作中，并且做到高效、便捷、易维护。你将依次完成几项基础但非常关键的任务：插入 JSON 文档；使用 `JSON_EXTRACT`、`->>` 等函数与操作符查询指定字段；更新 JSON 列中的数据；以及为 JSON 属性创建索引，从而优化查询性能。 整个实验会按照一条清晰的实践路径推进：先连接到 MySQL 服务器，然后创建专用数据库和数据表，接着逐步完成一系列操作练习。最终目的很明确，就是帮助你在关系型数据库环境下，更熟练地处理、查询和管理 JSON 数据。 ## 连接到 MySQL 并创建数据库 第一步，需要先连接 MySQL 服务器，并准备好本次实验所需的数据库和数据表。 先在桌面打开终端。 然后使用 `root` 用户连接 MySQL 服务器。在这个实验环境中，`sudo` 已完成配置，因此无需输入密码即可直接登录。 ```bash sudo mysql -u root ``` 连接成功后，命令行提示符会变为 `mysql>`，这表示你已经进入 MySQL shell。 接下来，创建一个名为 `jsondb` 的数据库。这里使用 `IF NOT EXISTS` 子句，是为了避免数据库已存在时出现报错，确保命令可以顺利执行。 ```sql CREATE DATABASE IF NOT EXISTS jsondb; ``` 创建完成后，切换到该数据库，使其成为后续 SQL 操作的当前数据库。 ```sql USE jsondb; ``` 最后，创建一个名为 `products` 的表。该表包含一个 `JSON` 类型的列，用于存储详细的产品信息。nn```sqlnCREATE TABLE IF NOT EXISTS products (n id INT AUTO_INCREMENT PRIMARY KEY,n product_name VARCHAR(255),n product_details JSONn);n```nn这条语句定义了一个包含三列的数据表：nn- `id`：每条记录的唯一标识，采用自动递增整数。n- `product_name`：用于保存产品名称的字符串字段。n- `product_details`：用于存储结构化产品信息的 `JSON` 列。nn至此，你已经成功完成数据库和数据表的初始化设置。请保持 MySQL shell 打开，以便继续进行下一步操作。n","need_verify":true,"has_solution":false},{"position":2,"title":"插入和查询 JSON 数据","layout":"doc-workbench-split","text":"## 插入和查询 JSON 数据nn表创建完成后，接下来你将插入一条包含 JSON 文档的记录，并执行一次基础查询来验证数据是否写入成功。nn在同一个 MySQL shell 中，执行下面的 `INSERT` 语句，添加一个新产品。nn```sqlnINSERT INTO products (product_name, product_details) VALUES (n 'Laptop',n '{n "brand": "Dell",n "model": "XPS 13",n "specs": {n "processor": "Intel Core i7",n "memory": "16GB",n "storage": "512GB SSD"n },n "price": 1200n }'n);n```nn这条命令会插入一条名为 `Laptop` 的记录。`product_details` 列中保存的是一个 JSON 对象，其中包含了嵌套结构的数据，例如 `specs`。nn为了确认数据已经正确插入，请查询 `products` 表查看内容。nn```sqlnSELECT * FROM products;n```nn输出结果应显示你刚刚插入的这一行。注意观察 JSON 数据在 `product_details` 列中的存储形式。nn```n+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+n| id | product_name | product_details |n+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+n| 1 | Laptop | {"brand": "Dell", "model": "XPS 13", "price": 1200, "specs": {"memory": "16GB", "storage": "512GB SSD", "processor": "Intel Core i7"}} |n+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+n1 row in set (0.00 sec)n```nn现在，你已经成功插入了一条包含 JSON 数据的记录。下一步，你将学习如何从这个 JSON 对象中提取指定字段的信息。n","need_verify":true,"has_solution":false},{"position":3,"title":"从 JSON 字段提取数据","layout":"doc-workbench-split","text":"## 从 JSON 字段提取数据nn将数据存储为 JSON 十分灵活，但在实际开发和数据库查询中，你还需要能够准确提取其中的单个字段。在这一步中，你将使用 `JSON_EXTRACT` 函数和 `JSON_UNQUOTE` 从 `product_details` 列中读取指定值。nn`JSON_EXTRACT` 函数允许你通过路径表达式，从 JSON 文档中选取目标数据。路径以 `$` 开头，表示 JSON 文档的根节点。nn先来提取这台笔记本电脑的 `brand` 字段。nn```sqlnSELECT JSON_EXTRACT(product_details, '$.brand') AS brand FROM products WHERE product_name = 'Laptop';n```nn这条查询会返回品牌信息，但要注意，结果仍然是 JSON 字符串，因此会带有双引号。nn```n+--------+n| brand |n+--------+n| "Dell" |n+--------+n1 row in set (0.00 sec)n```nn如果你希望得到更直观的查询结果，可以将 `JSON_UNQUOTE` 与 `JSON_EXTRACT` 搭配使用。这样既能提取 JSON 值，也能去掉外层引号，返回普通字符串。nn```sqlnSELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.brand')) AS brand FROM products WHERE product_name = 'Laptop';n```nn此时输出结果就是纯文本 `Dell`。nn```n+-------+n| brand |n+-------+n| Dell |n+-------+n1 row in set (0.00 sec)n```nn你还可以通过路径表达式访问嵌套对象中的字段。例如，要从 `specs` 对象中提取 `processor`，可使用路径 `$.specs.processor`。nn```sqlnSELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.specs.processor')) AS processor FROM products WHERE product_name = 'Laptop';n```nn这条语句会正确提取嵌套字段的值。nn```n+-----------------+n| processor |n+-----------------+n| Intel Core i7 |n+-----------------+n1 row in set (0.00 sec)n```nn这些 JSON 查询函数在 `WHERE` 条件中过滤数据时同样非常实用。比如，要查找所有价格大于 1000 的产品，需要先将提取出的 JSON 数值通过 `CAST` 转换为数字类型，再进行比较。nn```sqlnSELECT product_name, JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS price FROM products WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED) > 1000;n```nn这条查询展示了如何基于 JSON 字段中的数值内容筛选记录。nn```n+--------------+-------+n| product_name | price |n+--------------+-------+n| Laptop | 1200 |n+--------------+-------+n1 row in set (0.00 sec)n```nn至此，你已经掌握了如何从 JSON 字段中提取数据，并根据 JSON 属性进行过滤查询。n","need_verify":true,"has_solution":false},{"position":4,"title":"更新和添加 JSON 字段","layout":"doc-workbench-split","text":"## 更新和添加 JSON 字段nn数据通常会随着业务变化而更新，因此你需要掌握修改数据库中 JSON 文档的方法。在这一步中，你将使用 `JSON_SET` 函数来更新已有字段，并新增新的键值对。nn`JSON_SET` 函数通过接收目标列、字段路径和新值作为参数，对 JSON 文档进行修改。nn首先，将这台笔记本电脑的 `price` 从 1200 更新为 1250。nn```sqlnUPDATE productsnSET product_details = JSON_SET(product_details, '$.price', 1250)nWHERE product_name = 'Laptop';n```nn为了确认更新结果，请再次查询价格字段。nn```sqlnSELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS price FROM products WHERE product_name = 'Laptop';n```nn输出结果现在应显示新的价格。nn```n+-------+n| price |n+-------+n| 1250 |n+-------+n1 row in set (0.00 sec)n```nn如果指定的路径不存在，`JSON_SET` 还可以直接新增对应的键和值。下面为该产品添加一个 `color` 属性。nn```sqlnUPDATE productsnSET product_details = JSON_SET(product_details, '$.color', 'Silver')nWHERE product_name = 'Laptop';n```nn现在，查询整个 JSON 对象，查看新字段是否已经成功加入。nn```sqlnSELECT product_details FROM products WHERE product_name = 'Laptop';n```nn输出将显示更新后的 `product_details` 文档，其中已经包含 `color` 属性。nn```n+-----------------------------------------------------------------------------------------------------------------------------------------------------+n| product_details |n+-----------------------------------------------------------------------------------------------------------------------------------------------------+n| {"brand": "Dell", "color": "Silver", "model": "XPS 13", "price": 1250, "specs": {"memory": "16GB", "storage": "512GB SSD", "processor": "Intel Core i7"}} |n+-----------------------------------------------------------------------------------------------------------------------------------------------------+n1 row in set (0.00 sec)n```nn现在，你已经成功完成了 JSON 文档的修改与字段扩展。n","need_verify":true,"has_solution":false},{"position":5,"title":"为 JSON 属性创建索引","layout":"doc-workbench-split","text":"## 为 JSON 属性创建索引nn当数据表规模变大后，直接查询 JSON 字段的性能可能会明显下降。为了提升查询效率，你可以针对从 JSON 列中提取出的值建立索引。在 MariaDB 中，常见做法是先创建一个基于 JSON 属性的虚拟列，再在这个虚拟列上建立索引。nn在这一步中，你将为 `price` 属性添加一个虚拟列，并对其创建索引，以加快基于价格的查询速度。nn首先，添加一个虚拟列，用于从 JSON 数据中提取价格：nn```sqlnALTER TABLE products ADD COLUMN price_virtual INT AS (CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED)) STORED;n```nn这条命令会新增一个名为 `price_virtual` 的虚拟列，该列会自动计算并存储 JSON 数据中的价格值。nn接着，在这个虚拟列上创建索引：nn```sqlnCREATE INDEX idx_product_price ON products (price_virtual);n```nn通过这种方式，MariaDB 就可以利用该索引，更高效地执行基于价格的数字查询。nn要确认索引已经成功创建，请执行 `SHOW INDEXES` 命令。nn```sqlnSHOW INDEXES FROM products;n```nn输出结果会列出 `products` 表上的所有索引，其中包括你刚创建的新索引 `idx_product_price`。nn```n+----------+------------+-------------------+...n| Table | Non_unique | Key_name 接下来，关键不只是“索引是否创建成功”，更重要的是“查询执行时是否真正使用了索引”。判断这一点最直接的方法，就是查看优化器的执行计划，也就是使用 `EXPLAIN` 进行确认。 ```sql EXPLAIN SELECT product_name FROM products WHERE price_virtual > 1200; ``` 查看 `EXPLAIN` 输出时，重点关注 `possible_keys` 和 `key` 这两列。正常情况下，这里应当能看到 `idx_product_price`，这说明 MariaDB 确实使用了该索引，从而提升了查询性能。 此外，即使不直接查询虚拟列，而是继续使用原始 JSON 表达式，MariaDB 的优化器通常也能够利用建立在虚拟列上的索引。例如： ```sql EXPLAIN SELECT product_name FROM products WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED) > 1200; ``` 到这里，这套优化流程就已经完整跑通：虚拟列成功创建，索引已经建立，并且能够用于优化针对 JSON 属性的查询。 最后，退出 MySQL shell 即可。 ```sql exit ```c-fullscreen","text":"## 总结nn在这个实验中，你获得了在 MariaDB 中处理 JSON 数据的实际操作经验，并学习了一套较完整的工作流程。nn你成功插入了结构化 JSON 数据，使用 `JSON_EXTRACT` 和 `JSON_UNQUOTE` 查询指定字段，并基于 JSON 文档中的值对记录进行了筛选。你还通过 `JSON_SET` 练习了 JSON 数据更新，包括修改已有属性和新增字段。最后，你学习了如何通过为 JSON 属性创建虚拟列并建立索引，来提升 JSON 查询性能。nn这些技能对于设计更灵活的数据库结构，以及在 MariaDB 中高效管理半结构化数据，都具有很高的实用价值。n","need_verify":false,"has_solution":false}