Mysql 直接查询存储的Json字符串中的数据

在MySQL中，可直接查询存储的JSON字符串数据，利用MySQL的JSON函数提取和操作其中的信息。

Mysql数据库中Json字符串的查询技巧与实战应用

随着互联网技术的快速发展，数据结构变得越来越多样化，JSON（JavaScript Object Notation）作为一种轻量级的数据交换格式，因其简洁、清晰的层次结构，逐渐在各种应用场景中得到了广泛的应用，Mysql数据库从5.7版本开始，引入了对JSON数据类型的支持，使得我们可以直接在数据库层面处理JSON数据，本文将详细介绍如何在Mysql中查询存储的Json字符串中的数据。

Mysql中Json类型的支持

1、创建Json类型的表

我们需要创建一个支持Json类型的表，以下是一个简单的示例：

CREATE TABLE json_test (
  id int(11) NOT NULL AUTO_INCREMENT,
  json_data json DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、插入Json数据

插入Json数据时，可以直接插入符合Json格式的字符串：

INSERT INTO json_test (json_data) VALUES ('{"name": "张三", "age": 30, "email": "zhangsan@example.com"}');

查询Json字符串中的数据

1、使用->和->>操作符

Mysql提供了两种操作符来查询Json字符串中的数据：->和->>。

– ->操作符：用于获取Json对象的某个字段，返回的是一个Json类型的值。

– ->>操作符：用于获取Json对象的某个字段的值，返回的是一个非Json类型的值。

示例：

-- 使用->操作符
SELECT json_data->'$.name' AS name FROM json_test WHERE id = 1;
-- 使用->>操作符
SELECT json_data->>'$.name' AS name FROM json_test WHERE id = 1;

2、使用JSON_EXTRACT函数

JSON_EXTRACT函数可以从Json字符串中提取指定路径的值，其用法如下：

JSON_EXTRACT(json_doc, path)

示例：

SELECT JSON_EXTRACT(json_data, '$.name') AS name FROM json_test WHERE id = 1;

3、使用JSON_VALUE函数

JSON_VALUE函数可以从Json字符串中提取指定路径的值，并返回一个非Json类型的值。

JSON_VALUE(json_doc, path [ON EMPTY | ON ERROR])

示例：

SELECT JSON_VALUE(json_data, '$.name') AS name FROM json_test WHERE id = 1;

4、使用JSON_CONTAINS函数

JSON_CONTAINS函数用于判断Json字符串中是否包含某个指定的值。

JSON_CONTAINS(json_doc, val [path])

示例：

SELECT * FROM json_test WHERE JSON_CONTAINS(json_data, '"zhangsan@example.com"', '$.email');

5、使用JSON_SEARCH函数

JSON_SEARCH函数用于在Json字符串中搜索指定值，并返回匹配路径。

JSON_SEARCH(json_doc, 'one', search_str[, escape_char[, path]])

示例：

SELECT JSON_SEARCH(json_data, 'one', 'zhangsan@example.com', '$.email') FROM json_test WHERE id = 1;

实战应用

假设我们有一个用户表，其中包含一个Json类型的字段用于存储用户的兴趣爱好，现在我们需要查询喜欢篮球的用户信息。

1、创建用户表

CREATE TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  hobbies json DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、插入用户数据

INSERT INTO user (name, age, hobbies) VALUES ('张三', 30, '{"sports": ["篮球", "足球"], "music": ["流行", "古典"]}'), ('李四', 28, '{"sports": ["乒乓球", "羽毛球"], "music": ["摇滚", "民谣"]}'), ('王五', 32, '{"sports": ["篮球"], "music": ["爵士"]}');

3、查询喜欢篮球的用户信息

SELECT * FROM user WHERE JSON_CONTAINS(hobbies, '"篮球"', '$.sports');

通过上述查询，我们可以找到喜欢篮球的用户信息。

本文介绍了Mysql中查询存储的Json字符串中的数据的方法，包括使用操作符、函数以及实战应用，这些方法可以帮助我们更好地处理Json类型的数据，提高数据库查询的灵活性和效率，在实际开发过程中，我们可以根据具体需求选择合适的方法来实现对Json数据的查询。