Mysql 简明教程
MySQL - JSON
MySQL 提供了一个本机 JSON（JavaScript 对象表示法）数据类型，它可以高效地访问 JSON 文档中的数据。此数据类型在 MySQL 版本 5.7.8 及更高版本中引入。
MySQL provides a native JSON (JavaScript Object Notation) datatype that enables efficient access to the data in JSON documents. This datatype is introduced in MySQL versions 5.7.8 and later.
在引入此数据类型之前，JSON 格式的字符串存储在表的字符串列中。但是，由于以下原因，JSON 数据类型被证明比字符串更有优势：
Before it was introduced, the JSON-format strings were stored in the string columns of a table. However, the JSON datatype proves to be more advantageous than strings due to the following reasons −
-
It automatically validates the JSON documents, displaying an error whenever an invalid document is stored.
-
It stores the JSON documents in an internal format allowing easy read access to the document elements. Hence, when the MySQL server later reads the stored JSON values in a binary format, it just enables the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.
JSON 文档的存储需求类似于 LONGBLOB 或 LONGTEXT 数据类型。
The storage requirements for JSON documents are similar to those of LONGBLOB or LONGTEXT data types.
MySQL JSON
要在带有 JSON 数据类型的表列的 CREATE TABLE 语句中，我们使用关键字 JSON 。
To define a table column with JSON datatype, we use the keyword JSON in the CREATE TABLE statement.
我们可以在 MySQL 中创建两种类型的 JSON 值：
We can create two types of JSON values in MySQL:
-
JSON array: It is a list of values separated by commas and enclosed within square brackets ([]).
-
JSON object: An object with a set of key-value pairs separated by commas and enclosed within curly brackets ({}).
Syntax
以下是定义数据类型为 JSON 的列的语法 −
Following is the syntax to define a column whose data type is JSON −
CREATE TABLE table_name (
...
column_name JSON,
...
);Example
让我们看一个展示在 MySQL 表中使用 JSON 数据类型的示例。在这里，我们使用以下查询创建一个名为 MOBILES 的表 −
Let us see an example demonstrating the usage of JSON datatype in a MySQL table. Here, we are creating a table named MOBILES using the following query −
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 值。
Now, let us insert values into this table using the INSERT statement. In the FEATURES column, we use key-value pairs as a JSON value.
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"}');Retrieving Data From JSON Column
由于 JSON 数据类型提供了对所有 JSON 元素的更简单的读取访问，我们还可以直接从 JSON 列中检索每个元素。MySQL 提供了一个 JSON_EXTRACT() 函数来执行此操作。
As JSON datatype provides an easier read access to all JSON elements, we can also retrieve each element directly from the JSON column. MySQL provides a JSON_EXTRACT() function to do so.
Syntax
以下是 JSON_EXTRACT() 函数的语法 −
Following is the syntax of the JSON_EXTRACT() function −
JSON_EXTRACT(json_doc, path)在 JSON 数组中，我们可以通过指定其索引（从 0 开始）来检索特定元素。而在 JSON 对象中，我们指定键值对中的键。
In a JSON array, we can retrieve a particular element by specifying its index (starting from 0). And in a JSON object, we specify the key from key-value pairs.
Example
在这个示例中，从先前创建的 MOBILES 表中，我们使用以下查询检索每个手机的 OS 名称 −
In this example, from the previously created MOBILES table we are retrieving the OS name of each mobile using the following query −
SELECT NAME, JSON_EXTRACT(FEATURES,'$.OS')
AS OS FROM MOBILES;除了调用该函数，我们还可以使用 → 作为 JSON_EXTRACT 的快捷方式。请看下面的查询 −
Instead of calling the function, we can also use → as a shortcut for JSON_EXTRACT. Look at the query below −
SELECT NAME, FEATURES->'$.OS'
AS OS FROM MOBILES;The JSON_UNQUOTE() Function
JSON_UNQUOTE() 函数用于在检索 JSON 字符串时移除引号。语法如下 −
The JSON_UNQUOTE() function is used to remove the quotes while retrieving the JSON string. Following is the syntax −
JSON_UNQUOTE(JSON_EXTRACT(json_doc, path))Example
在这个示例中，让我们显示不带引号的每个手机的 OS 名称 −
In this example, let us display the OS name of each mobile without the quotes −
SELECT NAME, JSON_UNQUOTE(JSON_EXTRACT(FEATURES,'$.OS'))
AS OS FROM MOBILES;或者，我们可以使用 →> 作为 JSON_UNQUOTE(JSON_EXTRACT(…)) 的快捷方式。
Or, we can use →> as a shortcut for JSON_UNQUOTE(JSON_EXTRACT(…)).
SELECT NAME, FEATURES->>'$.OS'
AS OS FROM MOBILES;The JSON_TYPE() Function
众所周知，JSON 字段可以包含数组和对象形式的值。为了识别该字段中存储的值类型，我们使用 JSON_TYPE() 函数。语法如下 −
As we know, the JSON field can hold values in the form of arrays and objects. To identify which type of values are stored in the field, we use the JSON_TYPE() function. Following is the syntax −
JSON_TYPE(json_doc)The JSON_ARRAY_APPEND() Function
如果我们想在 MySQL 中的 JSON 字段中添加另一个元素，我们可以使用 JSON_ARRAY_APPEND() 函数。但新元素将仅追加为一个数组。以下是语法：
If we want to add another element to the JSON field in MySQL, we can use the JSON_ARRAY_APPEND() function. However, the new element will only be appended as an array. Following is the syntax −
JSON_ARRAY_APPEND(json_doc, path, new_value);Example
我们来看一个使用 JSON_ARRAY_APPEND() 函数在 JSON 对象末尾添加新元素的示例：
Let us see an example where we are adding a new element at the end of the JSON object using the JSON_ARRAY_APPEND() function −
UPDATE MOBILES
SET FEATURES = JSON_ARRAY_APPEND(FEATURES,'$',"Resolution:2400x1080 Pixels");我们可以使用 SELECT 查询验证是否添加了该值：
We can verify whether the value is added or not using a SELECT query −
SELECT NAME, FEATURES FROM MOBILES;The JSON_ARRAY_INSERT() Function
我们可以使用 JSON_ARRAY_APPEND() 函数仅在数组的末尾插入一个 JSON 值。但是，我们也可以使用 JSON_ARRAY_INSERT() 函数选择一个位置以将新值插入 JSON 字段。以下是语法：
We can only insert a JSON value at the end of the array using the JSON_ARRAY_APPEND() function. But, we can also choose a position to insert a new value into the JSON field using the JSON_ARRAY_INSERT() function. Following is the syntax −
JSON_ARRAY_INSERT(json_doc, pos, new_value);Example
在此，我们正在使用 JSON_ARRAY_INSERT() 函数在数组中添加在 index=1 的新元素：
Here, we are adding a new element in the index=1 of the array using the JSON_ARRAY_INSERT() function −
UPDATE MOBILES
SET FEATURES = JSON_ARRAY_INSERT(
FEATURES, '$[1]', "Charging: USB-C"
);要验证是否添加了该值，请使用 SELECT 查询显示更新后的表：
To verify whether the value is added or not, display the updated table using the SELECT query −
SELECT NAME, FEATURES FROM MOBILES;