跳到主要内容

MySQL 8.0 JSON 增强

介绍​

MySQL 8.0 引入了对 JSON 数据类型的多项增强，使得处理和查询 JSON 数据变得更加高效和灵活。JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，广泛用于现代 Web 应用程序中。MySQL 8.0 的 JSON 增强功能包括新的函数、操作符以及对 JSON 路径表达式的支持，这些功能使得开发者能够更轻松地存储、查询和操作 JSON 数据。

在本教程中，我们将逐步介绍 MySQL 8.0 中的 JSON 增强功能，并通过实际案例展示如何在实际应用中使用这些功能。

JSON 数据类型​

在 MySQL 中，JSON 是一种原生数据类型，可以存储 JSON 格式的数据。JSON 数据类型支持存储 JSON 对象和数组，并且可以通过 SQL 语句进行查询和操作。

创建包含 JSON 列的表​

首先，我们创建一个包含 JSON 列的表：

sql
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    profile JSON
);

在这个例子中，profile 列是一个 JSON 数据类型，用于存储用户的个人信息。

插入 JSON 数据​

接下来，我们插入一些 JSON 数据：

sql
INSERT INTO users (name, profile) VALUES 
('Alice', '{"age": 30, "email": "alice@example.com", "hobbies": ["reading", "traveling"]}'),
('Bob', '{"age": 25, "email": "bob@example.com", "hobbies": ["gaming", "coding"]}');

JSON 函数和操作符​

MySQL 8.0 提供了许多新的 JSON 函数和操作符，用于处理和查询 JSON 数据。

JSON_EXTRACT​

JSON_EXTRACT 函数用于从 JSON 文档中提取数据。它接受两个参数：JSON 文档和 JSON 路径表达式。

sql
SELECT name, JSON_EXTRACT(profile, '$.email') AS email
FROM users;

输出：

nameemail
Alice"alice@example.com"
Bob"bob@example.com"

JSON_SET​

JSON_SET 函数用于更新 JSON 文档中的值。如果路径不存在，它会创建新的键值对。

sql
UPDATE users
SET profile = JSON_SET(profile, '$.age', 31)
WHERE name = 'Alice';

JSON_REMOVE​

JSON_REMOVE 函数用于从 JSON 文档中删除指定的键值对。

sql
UPDATE users
SET profile = JSON_REMOVE(profile, '$.hobbies')
WHERE name = 'Bob';

JSON_CONTAINS​

JSON_CONTAINS 函数用于检查 JSON 文档中是否包含指定的值。

sql
SELECT name
FROM users
WHERE JSON_CONTAINS(profile, '"reading"', '$.hobbies');

输出：

name
Alice

JSON 路径表达式​

JSON 路径表达式用于指定 JSON 文档中的特定部分。路径表达式以 $ 开头，后跟键名或数组索引。

示例​

sql
SELECT JSON_EXTRACT(profile, '$.hobbies[0]') AS first_hobby
FROM users;

输出：

first_hobby
"reading"
"gaming"

实际案例​

假设我们有一个电子商务网站，需要存储用户的订单信息。订单信息可能包括订单号、商品列表、价格等。我们可以使用 JSON 数据类型来存储这些信息。

创建订单表​

sql
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_details JSON
);

插入订单数据​

sql
INSERT INTO orders (customer_id, order_details) VALUES 
(1, '{"order_number": "1001", "items": [{"product_id": 101, "quantity": 2}, {"product_id": 102, "quantity": 1}], "total_price": 150.00}'),
(2, '{"order_number": "1002", "items": [{"product_id": 103, "quantity": 1}], "total_price": 75.00}');

查询订单中的商品信息​

sql
SELECT order_id, JSON_EXTRACT(order_details, '$.items') AS items
FROM orders;

输出：

order_iditems
1[{"product_id": 101, "quantity": 2}, {"product_id": 102, "quantity": 1}]
2[{"product_id": 103, "quantity": 1}]

总结​

MySQL 8.0 的 JSON 增强功能为开发者提供了强大的工具来处理和查询 JSON 数据。通过使用新的 JSON 函数和操作符，开发者可以更高效地存储和操作复杂的 JSON 数据结构。在实际应用中，JSON 数据类型可以用于存储用户信息、订单信息等复杂数据，使得数据库设计更加灵活。

附加资源​

练习​

  1. 创建一个包含 JSON 列的表，并插入一些 JSON 数据。
  2. 使用 JSON_EXTRACT 函数查询 JSON 数据中的特定字段。
  3. 使用 JSON_SET 函数更新 JSON 数据中的值。
  4. 使用 JSON_CONTAINS 函数检查 JSON 数据中是否包含指定的值。

通过完成这些练习，你将更好地理解 MySQL 8.0 中的 JSON 增强功能。