在本实验中，你将学习如何在 MySQL 中有效地使用 JSON 数据类型。你将执行基本操作，例如插入 JSON 文档，使用 JSON_EXTRACT 和 ->> 操作符等函数查询特定字段，修改 JSON 列中的数据，以及通过在 JSON 属性上创建索引来优化查询。
在整个实验过程中，你将连接到 MySQL 服务器，创建一个专用的数据库和表，然后执行一系列实践任务，以在关系数据库环境中提升你管理 JSON 数据的技能。
在第一步中，你将连接到 MySQL 服务器并设置实验所需的数据库和表。
首先，从你的桌面打开终端。
使用 root 用户权限连接到 MySQL 服务器。在此实验环境中，sudo 允许你无需密码即可连接。
sudo mysql -u root连接成功后，命令提示符将变为 mysql>，表示你已进入 MySQL shell。
接下来，创建一个名为 jsondb 的新数据库。IF NOT EXISTS 子句可确保在数据库已存在时命令也能正常运行，不会报错。
CREATE DATABASE IF NOT EXISTS jsondb;现在，切换到你新创建的数据库，使其成为后续命令的活动数据库。
USE jsondb;最后，创建一个名为 products 的表。此表将包含一个 JSON 数据类型的列，用于存储详细的产品信息。
CREATE TABLE IF NOT EXISTS products (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255),
product_details JSON
);此语句定义了一个包含三列的表：
id: 每个记录的唯一、自动递增的整数。product_name: 用于产品名称的字符串。product_details: 用于存储结构化数据的 JSON 列。你已成功设置了必要的数据库和表。请保持 MySQL shell 打开，以便进行下一步。
表创建完成后，你现在将插入一条包含 JSON 文档的记录，然后执行一个基本查询来检索它。
在同一个 MySQL shell 中，执行以下 INSERT 语句来添加一个新产品。
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
}'
);此命令插入一条 'Laptop' 记录。product_details 列填充了一个 JSON 对象，其中包含嵌套数据，例如 specs。
为了确认数据已正确插入，请查询 products 表以查看其内容。
SELECT * FROM products;输出应显示你刚刚插入的行。请注意 JSON 数据是如何存储在 product_details 列中的。
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
| id | product_name | product_details |
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
| 1 | Laptop | {"brand": "Dell", "model": "XPS 13", "price": 1200, "specs": {"memory": "16GB", "storage": "512GB SSD", "processor": "Intel Core i7"}} |
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)你已成功插入一条包含 JSON 数据的记录。在下一步中，你将学习如何从这个 JSON 对象中提取特定的信息。
将数据存储在 JSON 中非常有用，但你也需要能够查询其中的单个字段。在这一步中，你将使用 JSON_EXTRACT 函数和 JSON_UNQUOTE 从 product_details 列中提取特定值。
JSON_EXTRACT 函数允许你使用路径表达式从 JSON 文档中选择一个值。路径以 $ 开始，表示文档的根。
让我们提取笔记本电脑的 brand。
SELECT JSON_EXTRACT(product_details, '$.brand') AS brand FROM products WHERE product_name = 'Laptop';此查询返回品牌，但请注意结果是一个 JSON 字符串，其中包含双引号。
+--------+
| brand |
+--------+
| "Dell" |
+--------+
1 row in set (0.00 sec)为了获得更清晰的结果，你可以将 JSON_UNQUOTE 与 JSON_EXTRACT 结合使用。这种组合可以提取值并删除引号，返回一个标准的字符串。
SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.brand')) AS brand FROM products WHERE product_name = 'Laptop';输出现在是纯文本 Dell。
+-------+
| brand |
+-------+
| Dell |
+-------+
1 row in set (0.00 sec)你还可以使用路径表达式来访问嵌套对象中的值。要从 specs 对象中获取 processor，请使用路径 $.specs.processor。
SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.specs.processor')) AS processor FROM products WHERE product_name = 'Laptop';这将正确提取嵌套的值。
+-----------------+
| processor |
+-----------------+
| Intel Core i7 |
+-----------------+
1 row in set (0.00 sec)这些函数在 WHERE 子句中也很有用，可用于过滤行。要查找所有价格超过 1000 的产品，你必须将提取的 JSON 值 CAST 为数字类型才能进行比较。
SELECT 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;此查询演示了如何根据 JSON 字段中的数值来过滤记录。
+--------------+-------+
| product_name | price |
+--------------+-------+
| Laptop | 1200 |
+--------------+-------+
1 row in set (0.00 sec)你现在知道如何根据 JSON 字段提取和过滤数据了。
数据会随时间变化，你需要一种方法来修改存储在数据库中的 JSON 文档。在这一步中，你将使用 JSON_SET 函数来更新现有值和添加新的键值对。
JSON_SET 函数通过接收目标列、字段的路径以及新值作为参数来修改 JSON 文档。
首先，让我们将笔记本电脑的 price 从 1200 更新到 1250。
UPDATE products
SET product_details = JSON_SET(product_details, '$.price', 1250)
WHERE product_name = 'Laptop';为了验证更改，请再次查询价格。
SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS price FROM products WHERE product_name = 'Laptop';输出现在应该显示新价格。
+-------+
| price |
+-------+
| 1250 |
+-------+
1 row in set (0.00 sec)如果指定的路径不存在，JSON_SET 将添加新的键和值。让我们为产品添加一个 color 属性。
UPDATE products
SET product_details = JSON_SET(product_details, '$.color', 'Silver')
WHERE product_name = 'Laptop';现在，查询整个 JSON 对象以查看新添加的字段。
SELECT product_details FROM products WHERE product_name = 'Laptop';输出将显示 product_details 文档，其中现在包含 color 属性。
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| product_details |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| {"brand": "Dell", "color": "Silver", "model": "XPS 13", "price": 1250, "specs": {"memory": "16GB", "storage": "512GB SSD", "processor": "Intel Core i7"}} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)你已成功修改和扩展了表中的 JSON 文档。
对于大型表，查询 JSON 字段可能会很慢。为了提高性能，你可以为从 JSON 列中提取的值创建索引。在 MariaDB 中，这可以通过首先添加一个基于 JSON 字段的虚拟列，然后在该虚拟列上创建索引来实现。
在这一步中，你将为 price 属性创建一个虚拟列，然后对其进行索引以加快基于价格的查询。
首先，添加一个虚拟列来从 JSON 数据中提取价格：
ALTER TABLE products ADD COLUMN price_virtual INT AS (CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED)) STORED;此命令添加一个名为 price_virtual 的虚拟列，该列自动计算并存储 JSON 数据中的价格值。
现在在此虚拟列上创建索引：
CREATE INDEX idx_product_price ON products (price_virtual);这种方法允许 MariaDB 通过使用索引的虚拟列来高效地查找基于数字价格的行。
要确认索引已创建，请使用 SHOW INDEXES 命令。
SHOW INDEXES FROM products;输出将列出 products 表上的所有索引，包括你的新索引 idx_product_price。
+----------+------------+-------------------+...
| Table | Non_unique | Key_name |...
+----------+------------+-------------------+...
| products | 0 | PRIMARY |...
| products | 1 | idx_product_price |...
+----------+------------+-------------------+...最重要的一点是查看优化器是否使用了索引。你可以使用 EXPLAIN 命令来检查这一点。
EXPLAIN SELECT product_name FROM products WHERE price_virtual > 1200;在 EXPLAIN 输出中，查看 possible_keys 和 key 列。你应该会看到列出了 idx_product_price，这证实了 MariaDB 正在使用你的索引来高效地执行查询。
你也可以使用原始的 JSON 表达式进行查询，MariaDB 的优化器仍然应该能够使用虚拟列上的索引：
EXPLAIN SELECT product_name FROM products WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED) > 1200;你已成功创建了一个虚拟列并对其进行了索引，以优化 JSON 属性查询。
现在你可以退出 MySQL shell。
exit在这个实验中，你获得了在 MariaDB 中处理 JSON 数据的实践经验。你学习了完整的流程，从设置数据库结构到执行高级操作。
你成功地插入了结构化的 JSON 数据，使用 JSON_EXTRACT 和 JSON_UNQUOTE 查询了特定字段，并根据 JSON 文档中的值过滤了记录。你还通过 JSON_SET 练习了修改这些数据，以更新和添加新属性。最后，你通过为 JSON 属性创建虚拟列并对其进行索引以提高查询性能，学习了一项关键的优化技术。
这些技能对于设计灵活的数据库模式和在 MariaDB 中高效管理半结构化数据非常有价值。
