【MySQL】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 创建表

DROP TABLE IF EXISTS `my_table`;
CREATE TABLE `my_table`  (
  `id` int NOT NULL,
  `message` json NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = 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数据类型。以下是一个示例：

INSERT INTO `my_table` (`id`, `message`) VALUES (1, '{\"age\": 30, \"name\": \"John\"}');
INSERT INTO `my_table` (`id`, `message`) VALUES (2, '{\"age\": 40, \"name\": \"Mary\"}');
INSERT INTO `my_table` (`id`, `message`) VALUES (3, '{\"age\": 50, \"name\": \"Tom\"}');
INSERT INTO `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数据类型的函数还有很多高级用法，等用到的时候再学。

本文完结！