Mysql 简明教程

MySQL - JSON

MySQL 提供了一个本机 JSON（JavaScript 对象表示法）数据类型，它可以高效地访问 JSON 文档中的数据。此数据类型在 MySQL 版本 5.7.8 及更高版本中引入。

在引入此数据类型之前，JSON 格式的字符串存储在表的字符串列中。但是，由于以下原因，JSON 数据类型被证明比字符串更有优势：

它自动验证 JSON 文档，每当存储无效文档时都会显示错误。
它以内部格式存储 JSON 文档，从而允许轻松读取文档元素。因此，当 MySQL 服务器稍后以二进制格式读取存储的 JSON 值时，它只需使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的任何值。

JSON 文档的存储需求类似于 LONGBLOB 或 LONGTEXT 数据类型。

MySQL JSON

要在带有 JSON 数据类型的表列的 CREATE TABLE 语句中，我们使用关键字 JSON 。

我们可以在 MySQL 中创建两种类型的 JSON 值：

JSON array: 一个由逗号分隔并用方括号 ([]) 括起来的值列表。
JSON object: 一个由逗号分隔并用大括号 ({} ) 括起来的一组键值对的对象。

Syntax

以下是定义数据类型为 JSON 的列的语法 −

CREATE TABLE table_name (
   ...
   column_name JSON,
   ...
);

Example

让我们看一个展示在 MySQL 表中使用 JSON 数据类型的示例。在这里，我们使用以下查询创建一个名为 MOBILES 的表 −

CREATE TABLE MOBILES(
   ID INT NOT NULL,
   NAME VARCHAR(25) NOT NULL,
   PRICE DECIMAL(18,2),
   FEATURES JSON,
   PRIMARY KEY(ID)
);

现在，让我们使用 INSERT 语句将值插入到此表中。在 FEATURES 列中，我们使用 key-value 对作为 JSON 值。

INSERT INTO MOBILES VALUES
(121, 'iPhone 15', 90000.00, '{"OS": "iOS", "Storage": "128GB", "Display": "15.54cm"}'),
(122, 'Samsung S23', 79000.00, '{"OS": "Android", "Storage": "128GB", "Display": "15.49cm"}'),
(123, 'Google Pixel 7', 59000.00, '{"OS": "Android", "Storage": "128GB", "Display": "16cm"}');

Output

该表将被创建为：

Retrieving Data From JSON Column

由于 JSON 数据类型提供了对所有 JSON 元素的更简单的读取访问，我们还可以直接从 JSON 列中检索每个元素。MySQL 提供了一个 JSON_EXTRACT() 函数来执行此操作。

Syntax

以下是 JSON_EXTRACT() 函数的语法 −

JSON_EXTRACT(json_doc, path)

在 JSON 数组中，我们可以通过指定其索引（从 0 开始）来检索特定元素。而在 JSON 对象中，我们指定键值对中的键。

Example

在这个示例中，从先前创建的 MOBILES 表中，我们使用以下查询检索每个手机的 OS 名称 −

SELECT NAME, JSON_EXTRACT(FEATURES,'$.OS')
AS OS FROM MOBILES;

除了调用该函数，我们还可以使用 → 作为 JSON_EXTRACT 的快捷方式。请看下面的查询 −

SELECT NAME, FEATURES->'$.OS'
AS OS FROM MOBILES;

Output

这两个查询都显示了以下相同的输出 −

The JSON_UNQUOTE() Function

JSON_UNQUOTE() 函数用于在检索 JSON 字符串时移除引号。语法如下 −

JSON_UNQUOTE(JSON_EXTRACT(json_doc, path))

Example

在这个示例中，让我们显示不带引号的每个手机的 OS 名称 −

SELECT NAME, JSON_UNQUOTE(JSON_EXTRACT(FEATURES,'$.OS'))
AS OS FROM MOBILES;

或者，我们可以使用 →> 作为 JSON_UNQUOTE(JSON_EXTRACT(…)) 的快捷方式。

SELECT NAME, FEATURES->>'$.OS'
AS OS FROM MOBILES;

Output

这两个查询都显示了以下相同的输出 −

The JSON_TYPE() Function

众所周知，JSON 字段可以包含数组和对象形式的值。为了识别该字段中存储的值类型，我们使用 JSON_TYPE() 函数。语法如下 −

JSON_TYPE(json_doc)

Example

在此示例中，让我们使用 JSON_TYPE() 函数检查 MOBILES 表的 FEATURES 列的类型。

SELECT JSON_TYPE(FEATURES) FROM MOBILES;

Output

如我们在输出中看到的，songs 列类型为 OBJECT。

The JSON_ARRAY_APPEND() Function

如果我们想在 MySQL 中的 JSON 字段中添加另一个元素，我们可以使用 JSON_ARRAY_APPEND() 函数。但新元素将仅追加为一个数组。以下是语法：

JSON_ARRAY_APPEND(json_doc, path, new_value);

Example

我们来看一个使用 JSON_ARRAY_APPEND() 函数在 JSON 对象末尾添加新元素的示例：

UPDATE MOBILES
SET FEATURES = JSON_ARRAY_APPEND(FEATURES,'$',"Resolution:2400x1080 Pixels");

我们可以使用 SELECT 查询验证是否添加了该值：

SELECT NAME, FEATURES FROM MOBILES;

Output

表将更新为：

The JSON_ARRAY_INSERT() Function

我们可以使用 JSON_ARRAY_APPEND() 函数仅在数组的末尾插入一个 JSON 值。但是，我们也可以使用 JSON_ARRAY_INSERT() 函数选择一个位置以将新值插入 JSON 字段。以下是语法：

JSON_ARRAY_INSERT(json_doc, pos, new_value);

Example

在此，我们正在使用 JSON_ARRAY_INSERT() 函数在数组中添加在 index=1 的新元素：

UPDATE MOBILES
SET FEATURES = JSON_ARRAY_INSERT(
   FEATURES, '$[1]', "Charging: USB-C"
);

要验证是否添加了该值，请使用 SELECT 查询显示更新后的表：

SELECT NAME, FEATURES FROM MOBILES;

Output

表将更新为：

JSON Using Client Program

我们还可以在使用客户端程序的情况下使用 JSON 数据类型定义 MySQL 表列。

Syntax

Example

以下是这些程序 −