MySQL 查询 Text 字段 JSON 数据

在现代数据库应用中，JSON 数据格式因其灵活性和易用性而变得越来越流行。MySQL 从 5.7 版本开始支持 JSON 数据类型，但在此之前，JSON 数据通常以 TEXT 字段的形式存储。本文将介绍如何在 MySQL 中查询存储在 TEXT 字段中的 JSON 数据。

JSON 数据存储在 TEXT 字段中

首先，我们需要了解 JSON 数据是如何存储在 TEXT 字段中的。以下是一个简单的示例：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    data TEXT
);
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.

在这个示例中，data 字段是一个 TEXT 类型，用于存储 JSON 数据。例如：

INSERT INTO users (id, name, data) VALUES
(1, 'Alice', '{"age": 25, "city": "New York"}'),
(2, 'Bob', '{"age": 30, "city": "San Francisco"}');
  • 1.
  • 2.
  • 3.

使用 JSON 函数查询 TEXT 字段中的 JSON 数据

MySQL 提供了一系列 JSON 函数来处理存储在 TEXT 字段中的 JSON 数据。以下是一些常用的 JSON 函数：

  • JSON_EXTRACT(json_string, path[, path, ...])
    从 JSON 文档中提取值。
  • JSON_UNQUOTE(json_string)
    去除 JSON 字符串的引号。
示例：查询 JSON 数据中的特定字段

假设我们想要查询所有用户的 age 字段，可以使用以下 SQL 查询：

SELECT id, name, JSON_EXTRACT(data, '$.age') AS age
FROM users;
  • 1.
  • 2.

这个查询将返回所有用户的 id、name 和 age 字段。

示例：使用 JSON 函数进行条件查询

我们可以使用 JSON_EXTRACT 函数和 WHERE 子句进行条件查询。例如，查询所有年龄大于 25 的用户：

SELECT id, name
FROM users
WHERE JSON_EXTRACT(data, '$.age') > 25;
  • 1.
  • 2.
  • 3.

关系图

以下是 users 表的实体关系图：

USER int id PK Primary Key string name text data

结论

在 MySQL 中查询存储在 TEXT 字段中的 JSON 数据是一个相对简单的过程，通过使用 MySQL 提供的 JSON 函数，我们可以轻松地提取和操作 JSON 数据。然而，随着 MySQL 5.7 及更高版本的发布，推荐使用原生的 JSON 数据类型来存储 JSON 数据，以获得更好的性能和更多的功能。

请注意，虽然本文主要关注在 MySQL 中查询 TEXT 字段中的 JSON 数据，但这些概念和技巧也可以应用于其他支持 JSON 数据类型的数据库系统。