mysql json 搜索

MySQL JSON 搜索

概述

MySQL 是一个广泛使用的关系型数据库管理系统，而 JSON 是一种轻量级的数据交换格式。在 MySQL 5.7 版本之后，MySQL 引入了对 JSON 数据类型的支持，使得我们可以在关系型数据库中存储和查询 JSON 数据。本文将介绍如何在 MySQL 中进行 JSON 搜索操作。

JSON 数据类型

在 MySQL 中，JSON 数据类型用于存储 JSON 数据。JSON 数据类型可以存储任意类型的数据，包括字符串、数字、布尔值、数组、对象等。我们可以在创建表时指定一个列为 JSON 类型，也可以在已有的表中添加 JSON 列。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    data JSON
);

JSON 搜索

MySQL 提供了一系列的函数和运算符来操作 JSON 数据类型。下面是一些常用的 JSON 搜索操作。

1. JSON_EXTRACT

JSON_EXTRACT 函数用于从 JSON 值中提取数据。它接受两个参数，第一个参数是 JSON 值，第二个参数是要提取的数据的路径。路径可以是简单的点号分隔的字符串，也可以是通配符。

SELECT JSON_EXTRACT(data, '$.name') AS name
FROM users;

上面的语句将从 users 表的 data 列中提取出名为 name 的字段的值。

2. JSON_CONTAINS

JSON_CONTAINS 函数用于检查一个 JSON 值是否包含另一个 JSON 值。它接受两个参数，第一个参数是要搜索的 JSON 值，第二个参数是要搜索的值。

SELECT *
FROM users
WHERE JSON_CONTAINS(data, 'admin', '$.roles');

上面的语句将从 users 表中选择包含 admin 角色的行。

3. -> 运算符

-> 运算符用于从 JSON 值中提取一个特定的字段的值。它接受两个参数，第一个参数是 JSON 值，第二个参数是要提取的字段的名称。

SELECT data->'$.name' AS name
FROM users;

上面的语句将从 users 表的 data 列中提取出名为 name 的字段的值。

4. ->> 运算符

->> 运算符用于从 JSON 值中提取一个特定的字段的字符串值。它接受两个参数，第一个参数是 JSON 值，第二个参数是要提取的字段的名称。

SELECT data->>'$.name' AS name
FROM users;

上面的语句将从 users 表的 data 列中提取出名为 name 的字段的字符串值。

示例

假设我们有一个 users 表，其中包含了用户的信息，每个用户的信息都存储在一个 JSON 对象中。下面是一个示例表的结构和数据：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    data JSON
);

INSERT INTO users (id, name, data) VALUES
(1, 'John', '{"age": 25, "email": "[e-mail]", "roles": ["admin", "user"]}'),
(2, 'Emma', '{"age": 30, "email": "[e-mail]", "roles": ["user"]}'),
(3, 'Michael', '{"age": 35, "email": "[e-mail]", "roles": ["admin"]}'),
(4, 'Sophia', '{"age": 20, "email": "[e-mail]", "roles": ["user"]}'),
(5, 'William', '{"age": 28, "email": "[e-mail]", "roles": ["admin", "user"]}'),
(6, 'Olivia', '{"age": 32, "email": "[e-mail]", "roles": ["user"]}');

现在，我们可以使用上述介绍的 JSON 搜索操作来查询和过滤数据。

查询所有用户的名字

SELECT data->>'$.name' AS name
FROM users;

结果：

name
John
Emma
Michael
Sophia
William
Olivia

查询年龄大于 25 的用户

SELECT *
FROM users
WHERE JSON_EXTRACT(data, '$.age') > 25;
``