如何在 MySQL 中使用 JSON 处理数组数据（完整示例指南）

1. 引言

在 MySQL 中处理数组数据的需求

数据库通常基于关系设计原则存储数据。然而，根据应用需求，可能会出现需要在单个列中存储多个值的情况。在这种情况下，类似“数组”的数据结构就显得非常有用。

例如，考虑以下场景：

• 存储用户选择的多个标签。
• 为商品保存多个图片 URL。
• 将历史记录或日志合并到单个字段中。

使用 JSON 数据类型的好处

MySQL 并未提供直接的“数组类型”，但通过使用 JSON 数据类型，可以处理类似数组的数据结构。JSON 类型高度灵活，具备以下优势：

• 支持嵌套数据结构。
• 允许在查询中轻松操作数据。
• 能在单个字段中管理多种数据格式。

本文将介绍如何使用 JSON 数据类型在 MySQL 中高效处理数组数据。

2. 使用 MySQL JSON 处理数组的基础知识

什么是 JSON 数据类型？

JSON（JavaScript Object Notation）是一种轻量且简洁的数据交换格式。在 MySQL 中，原生 JSON 支持自 5.7 版本起引入，允许直接在数据库中存储和操作 JSON 格式的数据。

示例：下面是可以存储在 JSON 列中的数据示例。

{
  "tags": ["PHP", "MySQL", "JSON"],
  "status": "published"
}

JSON 数据类型的优势与使用场景

使用 JSON 类型的主要好处如下：

1. 灵活的数据结构：无需修改关系模式即可处理可变长度的数据。
2. 高效的数据操作：可以使用 MySQL 专用函数（如 JSON_EXTRACT、JSON_ARRAY）轻松操作数据。
3. 无模式设计：当应用需求变化时，无需频繁修改模式。

示例使用场景：

• 为商品信息分配多个分类。
• 保存自定义用户设置。
• 在 Web 应用中使用嵌套的 JSON 数据。

3. 基本的 JSON 数组操作

创建 JSON 数组

在 MySQL 中，可以使用 JSON_ARRAY 函数轻松创建 JSON 数组。数组在将多个值存储于单列时非常有用。

示例

以下查询创建了一个名为 tags 的 JSON 数组。

SELECT JSON_ARRAY('PHP', 'MySQL', 'JavaScript') AS tags;

结果：

["PHP", "MySQL", "JavaScript"]

实际示例

下面的示例展示了如何使用 INSERT 语句将 JSON 数组存入数据库。

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags JSON
);

INSERT INTO articles (tags) 
VALUES (JSON_ARRAY('PHP', 'MySQL', 'JavaScript'));

从 JSON 数组中提取数据

要检索存储在 JSON 数组中的数据，可使用 JSON_EXTRACT 函数。该函数可以轻松提取数组中的特定元素。

示例

以下示例检索数组中的第二个元素（索引从 0 开始）。

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[1]') AS second_tag;

结果：

"MySQL"

检索多个元素

也可以一次检索多个元素。

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[0]', '$[2]') AS extracted_values;

添加、更新和删除数据

向数组添加数据

可以使用 JSON_ARRAY_APPEND 函数向已有数组添加新数据。

SET @tags = '["PHP", "MySQL"]';
SELECT JSON_ARRAY_APPEND(@tags, '$', 'JavaScript') AS updated_tags;

结果：

["PHP", "MySQL", "JavaScript"]

更新数组中的数据

可以使用 JSON_SET 函数更新数组中的特定元素。

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

结果：

["PHP", "Python", "JavaScript"]

从数组中删除数据

您可以使用 JSON_REMOVE 函数从数组中删除特定元素。

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_REMOVE(@tags, '$[1]') AS updated_tags;

结果：

["PHP", "JavaScript"]

4. 搜索和过滤 JSON 数组

搜索包含特定数据的数组

要检查 JSON 数组是否包含特定数据，请使用 JSON_CONTAINS 函数。该函数用于确定指定的值是否存在于 JSON 数组中。

示例

以下示例检查 JSON 数组是否包含 “MySQL”。

SELECT JSON_CONTAINS('["PHP", "MySQL", "JavaScript"]', '"MySQL"') AS is_present;

结果：

1  (if present)
0  (if not present)

实用示例：条件搜索

要在数据库表中搜索 JSON 数组中包含特定值的行，请在 WHERE 子句中使用 JSON_CONTAINS。

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');

此查询检索 tags 列中包含 “MySQL” 的行。

获取数组的长度

要获取 JSON 数组中元素的数量，请使用 JSON_LENGTH 函数。该函数返回数组中元素的数量，对数据分析和条件逻辑非常有用。

示例

以下示例检索数组中元素的数量。

SELECT JSON_LENGTH('["PHP", "MySQL", "JavaScript"]') AS array_length;

结果：

3

实用示例：提取满足特定条件的行

要提取元素数量大于或等于特定值的行，请在 WHERE 子句中使用 JSON_LENGTH。

SELECT * 
FROM articles
WHERE JSON_LENGTH(tags) >= 2;

此查询检索 tags 列中包含两个或更多元素的行。

高级条件查询示例

您可以组合多个条件进行更高级的搜索。以下查询搜索 tags 数组中包含 “JavaScript” 且元素数量不少于三的行。

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"JavaScript"') 
  AND JSON_LENGTH(tags) >= 3;

5. 实用示例：在实际用例中使用 JSON 数组

如何将产品类别存储为 JSON 数组

在电子商务网站及类似系统中，产品可能属于多个类别。在这种情况下，您可以使用 JSON 数组高效地存储类别信息。

示例：存储产品类别数据

以下示例展示了在产品表中创建名为 categories 的 JSON 列并存储多个类别。

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

INSERT INTO products (name, categories) 
VALUES ('Laptop', JSON_ARRAY('Electronics', 'Computers')),
       ('Smartphone', JSON_ARRAY('Electronics', 'Mobile Devices'));

即使产品属于多个类别，此结构也能保持数据简洁。

查询特定类别的产品

利用 JSON 数据类型，您可以轻松搜索属于特定类别的产品。

查询示例

以下查询检索 “Electronics” 类别中的所有产品。

SELECT name 
FROM products
WHERE JSON_CONTAINS(categories, '"Electronics"');

结果：

Laptop
Smartphone

此查询使得按类别灵活检索产品列表变得简单。

示例：按价格范围过滤

下面看看如何存储包含价格信息的 JSON 数据，然后根据价格范围搜索产品。

示例数据

以下示例使用 JSON 类型存储每个产品的价格信息。

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

INSERT INTO products_with_prices (name, details)
VALUES ('Laptop', '{"price": 150000, "categories": ["Electronics", "Computers"]}'),
       ('Smartphone', '{"price": 80000, "categories": ["Electronics", "Mobile Devices"]}');

查询示例

要搜索价格为 100,000 或更高的产品，请使用 JSON_EXTRACT 函数。

SELECT name 
FROM products_with_prices
WHERE JSON_EXTRACT(details, '$.price') >= 100000;

结果：

Laptop

使用 JSON_TABLE 扩展 JSON 并查询示例

如果您想以关系型格式查询 JSON 数据，JSON_TABLE 函数非常有用。该函数可以将 JSON 数组展开为虚拟表。

示例

以下示例展开一个 JSON 数组，并将每个类别显示为单独的一行。

SELECT * 
FROM JSON_TABLE(
    '["Electronics", "Computers", "Mobile Devices"]',
    '$[*]' COLUMNS(
        category_name VARCHAR(100) PATH '$'
    )
) AS categories_table;

结果：

category_name
--------------
Electronics
Computers
Mobile Devices

6. 使用 JSON 数据类型时的重要注意事项

性能优化技巧

虽然 JSON 类型灵活，但不良的设计会对数据库性能产生负面影响。以下是关键的优化要点。

1. 使用索引

在 MySQL 中，不能直接在 JSON 列上创建索引，但可以创建生成列并对特定键建立索引。

示例：使用生成列创建索引

在以下示例中，JSON 数据中的 price 键被用作索引目标。

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

通过使用生成列，可以显著提升对 JSON 数据的搜索性能。

2. 避免过于复杂的 JSON 结构

深度嵌套的 JSON 结构会降低查询的可读性和性能。设计数据时，应选择尽可能简单的 JSON 结构。

良好示例：

{
  "categories": ["Electronics", "Computers"],
  "price": 150000
}

应避免的示例：

{
  "product": {
    "details": {
      "price": 150000,
      "categories": ["Electronics", "Computers"]
    }
  }
}

如何利用索引

在使用生成列进行索引时，请注意以下要点：

1. 生成列必须为 STORED。
2. 使用 JSON_EXTRACT 函数将特定键提取为生成列。

例如，要提取 categories 键的第一个元素并创建索引，可按以下方式操作。

ALTER TABLE products
ADD COLUMN main_category VARCHAR(255) AS (JSON_EXTRACT(categories, '$[0]')) STORED,
ADD INDEX idx_main_category (main_category);

数据验证的重要性

由于 JSON 数据灵活，也更容易以错误的格式存储数据。为保持数据完整性，请使用以下方法。

1. 使用 CHECK 约束

在 MySQL 8.0 及更高版本中，您可以使用 CHECK 约束验证 JSON 结构和内容。

ALTER TABLE products_with_prices
ADD CONSTRAINT check_price CHECK (JSON_EXTRACT(details, '$.price') >= 0);

2. 应用层验证

插入数据时，建议在应用层验证 JSON 格式。PHP、Python 等编程语言可以使用其标准库对 JSON 进行验证。

7. MySQL 中使用数组的常见问题

问1：MySQL 有数组数据类型吗？

A1:MySQL 没有直接的“数组数据类型”。但是，通过使用 JSON 类型，您可以处理类似数组的数据结构。使用 JSON 类型，您可以在单个列中存储多个值，并通过查询进行操作。

示例：

SELECT JSON_ARRAY('Value 1', 'Value 2', 'Value 3') AS array_example;

结果：

["Value 1", "Value 2", "Value 3"]

Q2：可以在 JSON 数据上创建索引吗？

A2:您不能直接在 JSON 类型本身上创建索引。但是，您可以将特定键或值提取到生成列中，并在该生成列上创建索引。

示例：

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

这使您能够高效地搜索 JSON 数据中的值。

Q3：JSON 数据有大小限制吗？

A3:MySQL 的 JSON 类型可以存储最多 4GB 的数据。然而，使用极大的 JSON 文档会降低性能，因此您应谨慎设计数据。

建议：

• 仅存储所需的最小数据。
• 避免深层嵌套的 JSON 结构。

Q4：如何更新 JSON 数组中的特定元素？

A4:您可以使用 JSON_SET 函数更新数组中的特定元素。

示例：

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

结果：

["PHP", "Python", "JavaScript"]

Q5：JSON 类型与普通表设计的比较

A5:虽然 JSON 类型非常灵活，但它与传统关系型数据库设计相比具有不同的特性。

8. 摘要

使用 JSON 数据类型进行 MySQL 中数组操作的优势

在本文中，我们解释了 JSON 数据类型，它在 MySQL 中处理类似数组的数据时非常有用。以下是所覆盖关键点的摘要：

1. 为何使用 JSON 类型 MySQL 没有直接的数组类型，但通过使用 JSON 类型，您可以在单列中存储多个值，并实现灵活的数据操作。

2. 基本的 JSON 操作

• 我们介绍了如何创建 JSON 数组、提取数据、更新数据以及删除数据。
• 通过使用诸如 JSON_ARRAY、JSON_EXTRACT 和 JSON_SET 等便利函数，您可以高效地操作数组数据。

1. 搜索与过滤

• 如何使用 JSON_CONTAINS 搜索包含特定值的数据。
• 如何使用 JSON_LENGTH 获取元素数量并进行条件过滤。

1. 实际案例 通过管理产品类别和按价格过滤等真实案例，我们学习了在应用中使用 JSON 数组的具体方法。

2. 注意事项与优化

• 我们解释了如何使用生成列设置索引，并强调了验证 JSON 数据的重要性。

使用 JSON 数据类型的后续步骤

通过在 MySQL 中使用 JSON 类型，您可以比传统关系型数据库设计更灵活地管理数据。但良好的设计和性能考虑是必不可少的。

接下来要学习的主题：

• 使用复合索引 将 JSON 数据与普通列结合的索引设计。
• 使用高级 JSON 函数 使用诸如 JSON_MERGE 和 JSON_OBJECT 等函数执行更复杂的操作。
• 应用层数据处理 如何使用 PHP 或 Python 高效地操作 MySQL JSON 数据。

摘要

通过本文，您现在应该了解如何使用 MySQL 的 JSON 数据类型高效处理类似数组的数据。通过运用这些知识，您可以设计更灵活且可扩展的数据库。