【MySQL】MySQL如何查询和筛选存储的JSON数据？

MySQL如何查询和筛选存储的JSON数据？

一、背景介绍

JSON(JavaScript Object Notation)是一种轻量级的数据交换格式，在现代应用程序中得到了广泛的应用。处理和存储非结构化数据变得越来越重要。MySQL 是一种常用的关系型数据库管理系统，为了支持非结构化数据的存储和查询，于是 MySQL引入了对JSON数据类型的支持。

MySQL从5.7.8版本开始添加了对JSON的支持，使得我们可以在MySQL数据库中存储和查询JSON格式的数据。本文将介绍如何使用MySQL查询和筛选存储的JSON数据，并提供相应的代码示例。

二、支持的JSON数据类型

从MySQL 5.7.8版本开始，MySQL引入了对JSON数据类型的支持。MySQL可以存储任意的JSON数据，包括对象、数组、字符串等。并且MySQL还对JSON数据类型还提供了一系列的操作函数和运算符，以便于对JSON数据进行查询和操作。

三、基础数据

3.1 创建表

DROPTABLEIFEXISTS`my_table`;CREATETABLE`my_table`(`id`intNOTNULL,`message` json NULL,PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBCHARACTERSET= utf8mb4 COLLATE= utf8mb4_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS =1;

上述示例创建了一个名为

my_table

的表，包含一个名为

message

的JSON列。

message

列可以存储任意的JSON数据。

3.2 插入 JSON 数据

要在JSON列中插入数据，可以使用JSON函数将JSON字符串转换为JSON数据类型。以下是一个示例：

INSERTINTO`my_table`(`id`,`message`)VALUES(1,'{\"age\": 30, \"name\": \"John\"}');INSERTINTO`my_table`(`id`,`message`)VALUES(2,'{\"age\": 40, \"name\": \"Mary\"}');INSERTINTO`my_table`(`id`,`message`)VALUES(3,'{\"age\": 50, \"name\": \"Tom\"}');INSERTINTO`my_table`(`id`,`message`)VALUES(4,'{\"age\": 60, \"name\": \"Jermy\"}');

上述示例在

my_table

表中插入了4条记录，其中

message

列包含一个JSON对象。

3.3 查询 JSON 数据

可以使用->或者->>运算符来查询JSON数据。->运算符返回JSON对象的属性值，->>运算符返回属性值的字符串表示。

以下是两个

查询JSON数据的示例1：

SELECT message->'$.name'AS name FROM my_table;

结果如下：

查询示例2：

SELECT*FROM my_table WHERE JSON_EXTRACT(message,'$.age')>40

结果如下：

四、操作函数

MySQL提供了一系列的JSON数据操作函数，以方便对JSON数据进行查询和操作。以下是一些常用的JSON数据操作函数：

4.1 JSON_OBJECT

JSON_OBJECT

函数用于创建一个JSON对象。以下是一个示例：

SELECT JSON_OBJECT('name','John','age',30)AS message;

上述示例创建了一个名为

message

的JSON对象，包含name和age属性。

4.2 JSON_ARRAY

JSON_ARRAY

函数用于创建一个JSON数组。以下是一个示例：

SELECT JSON_ARRAY('apple','banana','orange')AS fruits;

上述示例创建了一个名为fruits的JSON数组，包含三个元素。

4.3 JSON_EXTRACT

JSON_EXTRACT

函数用于提取JSON数据中的值。以下是一个示例：

SELECT JSON_EXTRACT('{"name": "John", "age": 30}','$.name')AS name;

上述示例从JSON数据中提取了name属性的值。

以上只是最基本的用法，关于MySQL操作JSON数据类型的函数还有很多高级用法，等用到的时候再学。

本文完结！