【MySQL】MySQL 8 的 JSON 新特性详解（1）JSON 数据类型-Toy模板网

这篇具有很好参考价值的文章主要介绍了【MySQL】MySQL 8 的 JSON 新特性详解（1）JSON 数据类型。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一、概述

你好，我是小雨青年，一名使用MySQL 8 的程序员。

MySQL 8 引入了对 JSON 数据类型的全面支持，并提供了一组内置函数以有效处理 JSON 数据。MySQL 8 中的 JSON 支持的一些关键特性如下：

JSON 数据类型：MySQL 8 拥有一个名为 JSON 的新数据类型，可用于在列中存储 JSON 数据。该数据类型可以以紧凑和二进制格式存储 JSON 数据，使其比将 JSON 数据存储为字符串更有效。
JSON 函数：MySQL 8 提供了一组内置函数，允许您提取和操纵 JSON 数据。例如，JSON_EXTRACT 函数允许您从 JSON 文档中提取值，JSON_ARRAY 函数允许您创建 JSON 数组，JSON_OBJECT 函数允许您创建 JSON 对象。
JSON 索引：MySQL 8 提供了一个选项，使用 GENERATED 列索引 JSON 数据。这允许您对 JSON 数据执行有效的搜索和排序。
JSON 验证：MySQL 8 提供了一个 JSON_VALID 函数，允许您验证字符串是否为有效的 JSON 文档。
JSON 聚合：MySQL 8 提供了诸如 JSON_ARRAYAGG 和 JSON_OBJECTAGG 等函数，允许您聚合 JSON 数据并将其作为单个 JSON 文档返回。

本文将详细讲解MySQL 8 的JSON 数据类型的使用方法。

二、MySQL 8 的环境搭建

我们使用 Docker 作为本地环境，使用 Docker 具有以下优点：

简化部署：Docker 容器可以在任何支持 Docker 的系统上部署，并且不会受到底层系统的限制。
提高可移植性：Docker 容器可以在任何支持 Docker 的环境中运行，并且不需要担心应用程序的依赖关系。
提高安全性：Docker 容器在运行时独立于其他容器和操作系统，因此可以提高安全性。
提高可重复性：Docker 容器可以被保存和复制，因此可以提高开发、测试和生产环境之间的可重复性。
缩短开发周期：Docker 可以加速开发周期，因为它可以快速部署和测试应用程序。
减少资源消耗：Docker 可以节省系统资源，因为它可以共享操作系统内核，并且可以在同一系统上运行多个容器。
增强灵活性：Docker 可以根据您的需求轻松更改容器配置，以实现快速迭代和弹性。

在安装 Docker 环境之后，使用docker pull mysql:8拉取镜像到本地。

使用以下命令启动一个名为“mysql-8”的 MySQL 8 容器：

docker run --name mysql8 -e MYSQL_ROOT_PASSWORD=password -p 3310:3306 -d mysql:8

使用以下命令连接到启动的 MySQL 8 容器：

docker exec -it mysql-8 mysql -uroot -ppassword

也可以使用图形化客户端进行连接，在本系列中，我使用的 DBeaver 社区版本，免费开源的数据库图形化客户端。我们在创建连接是注意上面一步配置的端口是3310，下图为连接成功。

mysql8 json,MySQL,json,mysql,docker

三、创建数据库、数据表并插入默认数据

MySQL 8 支持 JSON 数据格式，并且 JSON 数据格式可以在 InnoDB 和 MyISAM 引擎上使用。

InnoDB 是 MySQL 中默认的数据引擎，支持事务、外键约束和其他高级功能。

接下来我们创建数据库，并创建一个带有JSON格式的表，插入数据。

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

USE mydatabase;

CREATE TABLE mytable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(255) NOT NULL,
  data JSON,
  UNIQUE KEY email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_General_ci;

INSERT INTO mytable (name, email, phone, data) VALUES 
("John Doe", "johndoe@example.com", "555-555-5555", '{"address": {"city": "San Francisco", "state": "CA"}}'), 
("Jane Doe", "janedoe@example.com", "555-555-5556", '{"address": {"city": "New York", "state": "NY"}}'), 
("Jim Smith", "jimsmith@example.com", "555-555-5557", '{"address": {"city": "Los Angeles", "state": "CA"}}');

创建完成。

mysql8 json,MySQL,json,mysql,docker

四、JSON格式数据的增加和查询

1. 增加一条带JOSN格式的数据

INSERT INTO mytable (name, email, phone, data) VALUES ('John Doe', 'johndoe@example.com', '555-555-5555', '{"address": "123 Main St", "city": "New York", "state": "NY"}');

注意，第四个字段 data 中存储的是一个 JSON 字符串。

2.查询JSON内数据

MySQL 8中查询json类型的数据可以使用->>运算符和->运算符。

->> 运算符可以提取json字段中的文本，而-> 运算符则可以提取json字段中的json对象。

例如，以下是使用 ->> 运算符查询json字段中的文本：

SELECT name, data->>'$.address.city' as city FROM mytable;

查询结果如下所示。

mysql8 json,MySQL,json,mysql,docker

以下是使用 -> 运算符查询json字段中的json对象：

SELECT name, data->'$.address' as city FROM mytable;

查询结果如下所示。

mysql8 json,MySQL,json,mysql,docker

3.带筛选条件的查询

在 MySQL 8 中查询带条件的 JSON 类型数据的方法如下：

使用 JSON 数据路径表达式：

SELECT name, email, phone, data->'$.address' as address, data->>'$.address.city' as city, data->>'$.address.state' 
FROM mytable 
WHERE data->'$.address.state' = 'NY';

查询结果如下所示。

mysql8 json,MySQL,json,mysql,docker

使用 JSON 函数：

SELECT name, email, phone, JSON_EXTRACT(data, '$.address') as address, JSON_EXTRACT(data, '$.address.city') as city, JSON_EXTRACT(data, '$.address.state') as state
FROM mytable 
WHERE JSON_EXTRACT(data, '$.address.state') = 'NY';

查询结果和上面的一致。