MySQL JSON 数据处理

简介

在这个实验中，你将学习如何在 MySQL 数据库中处理 JSON 数据。这包括将 JSON 数据插入到列中、使用 JSON_EXTRACT 函数查询 JSON 数据、更新 JSON 字段中的值，以及在 JSON 属性上创建索引以提高查询性能。

实验首先会连接到一个预先配置好的 MySQL 服务器，并创建一个专门用于存储 JSON 数据的数据库和表。然后，你将插入一条示例产品记录，其详细信息以 JSON 格式存储，为后续涉及查询和操作这些数据的步骤奠定基础。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql(("MySQL")) -.-> mysql/AdvancedQueryingandOptimizationGroup(["Advanced Querying and Optimization"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/BasicKeywordsandStatementsGroup -.-> mysql/update("Data Update") mysql/AdvancedQueryingandOptimizationGroup -.-> mysql/index("Index Management") subgraph Lab Skills mysql/use_database -.-> lab-550911{{"MySQL JSON 数据处理"}} mysql/create_database -.-> lab-550911{{"MySQL JSON 数据处理"}} mysql/create_table -.-> lab-550911{{"MySQL JSON 数据处理"}} mysql/select -.-> lab-550911{{"MySQL JSON 数据处理"}} mysql/insert -.-> lab-550911{{"MySQL JSON 数据处理"}} mysql/update -.-> lab-550911{{"MySQL JSON 数据处理"}} mysql/index -.-> lab-550911{{"MySQL JSON 数据处理"}} end

向列中插入 JSON 数据

在这一步中，你将学习如何将 JSON 数据插入到 MySQL 列中。MySQL 5.7.22 及更高版本支持原生的 JSON 数据类型，这使你可以直接在数据库中存储和操作 JSON 文档。

在开始之前，请确保你可以访问 MySQL 服务器。在这个 LabEx 环境中，已经预先配置好了一个 MySQL 服务器。

首先，使用 mysql 客户端连接到 MySQL 服务器。打开终端并执行以下命令：

mysql -u root -p

系统会提示你输入 root 用户的密码。由于安装过程中未设置密码，直接按 Enter 键即可。

现在你已连接到 MySQL 服务器，接下来创建一个数据库和一个表来存储 JSON 数据。

CREATE DATABASE IF NOT EXISTS jsondb;
USE jsondb;

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255),
    product_details JSON
);

这段 SQL 代码首先会检查是否存在名为 jsondb 的数据库，如果不存在则创建该数据库。然后，它会切换到使用 jsondb 数据库。最后，创建一个名为 products 的表，该表包含三列：id、product_name 和 product_details。product_details 列的数据类型为 JSON。

现在，向 product_details 列中插入一些 JSON 数据。我们将插入一条代表产品的记录，其详细信息以 JSON 格式存储。

INSERT INTO products (product_name, product_details) VALUES (
    'Laptop',
    '{
        "brand": "Dell",
        "model": "XPS 13",
        "specs": {
            "processor": "Intel Core i7",
            "memory": "16GB",
            "storage": "512GB SSD"
        },
        "price": 1200
    }'
);

这条 INSERT 语句向 products 表中插入了一行新数据。product_name 设置为 'Laptop'，product_details 列填充了一个 JSON 文档，其中包含了笔记本电脑的品牌、型号、规格和价格信息。

为了验证数据是否已正确插入，我们来查询该表：

SELECT * FROM products;

你应该会看到类似以下的输出：

1 row in set (0.00 sec)

这证实了 JSON 数据已成功插入到 product_details 列中。

你可以通过输入 exit 并按 Enter 键退出 MySQL 客户端。

exit

在这一步中，你学习了如何创建一个包含 JSON 列的表，并向其中插入 JSON 数据。这是在 MySQL 中处理 JSON 数据的基础。

使用 JSON_EXTRACT 查询 JSON 数据

在这一步中，你将学习如何使用 JSON_EXTRACT 函数查询存储在 MySQL 列中的 JSON 数据。该函数允许你根据路径从 JSON 文档中提取特定的值。

首先，使用 mysql 客户端连接到 MySQL 服务器。打开终端并执行以下命令：

mysql -u root -p

系统会提示你输入 root 用户的密码。由于安装过程中未设置密码，直接按 Enter 键即可。

接下来，选择我们在上一步中创建的 jsondb 数据库：

USE jsondb;

现在，使用 JSON_EXTRACT 函数从 product_details 列中检索笔记本电脑的品牌。

SELECT JSON_EXTRACT(product_details, '$.brand') AS brand FROM products;

在这个查询中，JSON_EXTRACT(product_details, '$.brand') 从 product_details JSON 文档中提取与键 "brand" 关联的值。$.brand 是一个 JSON 路径表达式，用于指定所需值的路径。$ 符号表示 JSON 文档的根。

你应该会看到类似以下的输出：

+-------+
| brand |
+-------+
| "Dell"|
+-------+
1 row in set (0.00 sec)

注意，提取的值用双引号括起来了。这是因为 JSON_EXTRACT 将值作为 JSON 字符串返回。如果你想以不带引号的字符串形式检索该值，可以使用 ->> 运算符，它是 JSON_UNQUOTE(JSON_EXTRACT(...)) 的简写形式。

我们来试试看：

SELECT product_details->>'$.brand' AS brand FROM products;

现在的输出将是：

+-------+
| brand |
+-------+
| Dell  |
+-------+
1 row in set (0.00 sec)

现在，从 product_details JSON 文档的 specs 对象中提取处理器信息。

SELECT product_details->>'$.specs.processor' AS processor FROM products;

这里，$.specs.processor 指定了 "specs" 对象中 "processor" 值的路径。

输出将是：

+-----------------+
| processor       |
+-----------------+
| Intel Core i7   |
+-----------------+
1 row in set (0.00 sec)

你还可以在 WHERE 子句中使用 JSON_EXTRACT 来根据 JSON 值过滤结果。例如，查找所有价格大于 1000 的产品。

SELECT product_name FROM products WHERE CAST(product_details->>'$.price' AS SIGNED) > 1000;

在这个查询中，我们使用 CAST(product_details->>'$.price' AS SIGNED) 将提取的价格值转换为有符号整数，以便进行数值比较。

输出将是：

+--------------+
| product_name |
+--------------+
| Laptop       |
+--------------+
1 row in set (0.00 sec)

你可以通过输入 exit 并按 Enter 键退出 MySQL 客户端。

exit

在这一步中，你学习了如何使用 JSON_EXTRACT 函数和 ->> 运算符在 MySQL 中查询 JSON 数据。你还学习了如何在 WHERE 子句中使用 JSON 值来过滤结果。

更新 JSON 字段值

在这一步中，你将学习如何使用 JSON_SET 函数更新 MySQL 列中的 JSON 字段值。该函数允许你根据路径修改 JSON 文档中的特定值。

首先，使用 mysql 客户端连接到 MySQL 服务器。打开终端并执行以下命令：

mysql -u root -p

系统会提示你输入 root 用户的密码。由于安装过程中未设置密码，直接按 Enter 键即可。

接下来，选择我们在上一步中创建的 jsondb 数据库：

USE jsondb;

现在，使用 JSON_SET 函数更新 product_details 列中笔记本电脑的价格。假设我们要将价格提高到 1250。

UPDATE products
SET product_details = JSON_SET(product_details, '$.price', 1250)
WHERE product_name = 'Laptop';

在这个 UPDATE 语句中，JSON_SET(product_details, '$.price', 1250) 通过将与键 "price" 关联的值设置为 1250 来修改 product_details JSON 文档。$.price 是一个 JSON 路径表达式，用于指定要更新的值的路径。

为了验证数据是否已正确更新，我们来查询该表并检查价格：

SELECT product_details->>'$.price' AS price FROM products WHERE product_name = 'Laptop';

你应该会看到类似以下的输出：

+-------+
| price |
+-------+
| 1250  |
+-------+
1 row in set (0.00 sec)

这证实了价格已成功更新为 1250。

现在，更新 product_details JSON 文档中 specs 对象里的处理器信息。假设我们要将处理器升级为 "Intel Core i9"。

UPDATE products
SET product_details = JSON_SET(product_details, '$.specs.processor', 'Intel Core i9')
WHERE product_name = 'Laptop';

这里，$.specs.processor 指定了 "specs" 对象中 "processor" 值的路径。

为了验证更新情况，我们再次查询该表：

SELECT product_details->>'$.specs.processor' AS processor FROM products WHERE product_name = 'Laptop';

输出将是：

+-----------------+
| processor       |
+-----------------+
| Intel Core i9   |
+-----------------+
1 row in set (0.00 sec)

这证实了处理器已更新为 "Intel Core i9"。

你还可以使用 JSON_SET 向 JSON 文档中添加新字段。例如，添加一个名为 "color"、值为 "Silver" 的新字段。

UPDATE products
SET product_details = JSON_SET(product_details, '$.color', 'Silver')
WHERE product_name = 'Laptop';

为了验证是否添加成功，我们查询该表：

SELECT product_details->>'$.color' AS color FROM products WHERE product_name = 'Laptop';

输出将是：

+--------+
| color  |
+--------+
| Silver |
+--------+
1 row in set (0.00 sec)

你可以通过输入 exit 并按 Enter 键退出 MySQL 客户端。

exit

在这一步中，你学习了如何使用 JSON_SET 函数更新 MySQL 中现有 JSON 字段的值，并向 JSON 文档中添加新字段。

在 JSON 属性上创建索引

在这一步中，你将学习如何在 MySQL 的 JSON 属性上创建索引。为 JSON 属性创建索引可以显著提高基于 JSON 文档内的值进行数据过滤或排序的查询性能。

首先，使用 mysql 客户端连接到 MySQL 服务器。打开终端并执行以下命令：

mysql -u root -p

系统会提示你输入 root 用户的密码。由于安装过程中未设置密码，直接按 Enter 键即可。

接下来，选择我们在前面步骤中创建的 jsondb 数据库：

USE jsondb;

现在，在 product_details JSON 列中的 price 属性上创建一个索引。我们将在 products 表上创建一个名为 idx_product_price 的索引。

CREATE INDEX idx_product_price ON products ((CAST(product_details->>'$.price' AS SIGNED)));

在这个 CREATE INDEX 语句中：

idx_product_price 是索引的名称。
products 是表的名称。
((CAST(product_details->>'$.price' AS SIGNED))) 指定了创建索引的表达式。我们使用 product_details->>'$.price' 从 JSON 文档中提取 price 值，然后使用 CAST(... AS SIGNED) 将其转换为有符号整数。这是必要的，因为索引只能在标量值上创建，而不能直接在 JSON 文档上创建。

要验证索引是否已创建，可以使用 SHOW INDEXES 命令：

SHOW INDEXES FROM products;

你应该会看到包含 idx_product_price 索引的输出。输出大致如下：

+----------+------------+---------------------+--------------+------------------------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name            | Seq_in_index | Column_name                                    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+---------------------+--------------+------------------------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| products |          0 | PRIMARY             |            1 | id                                             | A         |           1 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| products |          1 | idx_product_price   |            1 | NULL                                           | NULL      |           1 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | CAST(json_extract(`product_details`,_utf8mb4'$.price') as signed) |
+----------+------------+---------------------+--------------+------------------------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

需要注意的重要部分是 Key_name（即 idx_product_price）和 Expression（显示了经过类型转换的 JSON 提取操作）。

现在，我们来分析索引是如何提高查询性能的。为此，我们将使用 EXPLAIN 命令。首先，对一个在没有索引的情况下按价格过滤的查询运行 EXPLAIN：

（为了模拟不使用索引，我们先删除它，之后再重新创建）

DROP INDEX idx_product_price ON products;
EXPLAIN SELECT product_name FROM products WHERE CAST(product_details->>'$.price' AS SIGNED) > 1200;
CREATE INDEX idx_product_price ON products ((CAST(product_details->>'$.price' AS SIGNED)));

EXPLAIN 命令的输出将显示 MySQL 必须执行全表扫描才能执行该查询。在输出中查找 type: ALL。

现在，在有索引的情况下运行相同的 EXPLAIN 命令：

EXPLAIN SELECT product_name FROM products WHERE CAST(product_details->>'$.price' AS SIGNED) > 1200;

EXPLAIN 命令的输出现在应该显示 MySQL 正在使用索引来执行查询。在输出中查找 type: range、possible_keys: idx_product_price 和 key: idx_product_price。这表明正在使用索引，这将显著加快查询执行速度，尤其是对于大表而言。

你可以通过输入 exit 并按 Enter 键退出 MySQL 客户端。

exit

在这一步中，你学习了如何在 MySQL 的 JSON 属性上创建索引，以及如何验证查询优化器是否正在使用该索引。为 JSON 属性创建索引是优化处理 JSON 数据的查询性能的一项重要技术。

总结

在本次实验中，你学习了如何将 JSON 数据插入到 MySQL 列中。首先，你连接到 MySQL 服务器，创建了一个名为 jsondb 的数据库和一个名为 products 的表，该表包含一个 JSON 类型的列 product_details。

然后，你向 products 表中插入了一条记录，在 product_details 列中填充了一个 JSON 文档，其中包含了一台笔记本电脑的品牌、型号、规格和价格信息。这展示了在 MySQL 数据库中存储 JSON 数据的基本过程。