怎样在 MySQL 中处理 JSON 数据

介绍

JavaScript Object Notation (JSON) 是一种流行的数据交换格式,因为它很容易被人和机器序列化和反序列化。 大多数主要编程语言也广泛支持 JSON,并且有数百个用于生成和解析它的库。

在 MySQL 中,您可以在创建表列时将 JSON 定义为数据类型。 虽然您实际上可以在文本字段中存储相同的 JSON 数据,但 JSON 数据类型有几个优点。 一、优化的存储格式使您能够高效地访问文档元素。 第二,使用 JSON 数据类型定义的字段提供自动验证,确保仅将语法有效的文档保存到数据库中。

在本指南中,您将介绍一组支持的 MySQL 函数和数据类型,这些函数和数据类型支持 JSON 值的操作,以提供对单个 JSON 键值对的访问。 在验证、创建、搜索和操作 JSON 文档时,您还将看到其中一些函数的灵活性和强大功能。

先决条件

要学习本教程,您需要具备以下条件:

一个 Ubuntu 20.04 服务器。 虽然本指南在 Ubuntu 20.04 服务器上进行了测试,但它应该在支持 MySQL 的任何其他发行版上运行良好。 一个非 root 用户 sudo 特权。 一个 MySQL 服务器。

1. 创建示例数据库

连接到您的服务器并以 root 身份登录到您的 MySQL。

$ sudo mysql -u root -p

接下来,输入您的 MySQL 根密码并按 ENTER 继续。 登录 MySQL 服务器后,执行以下语句以创建示例 json_test 数据库。

mysql> CREATE DATABASE json_test;

输出。

Query OK, 1 row affected (0.00 sec)

切换到新的 json_test 数据库。

mysql> USE json_test;

输出。

Database changed

接下来,定义一个 customers 桌子。 此表将使用唯一标识客户 AUTO_INCREMENT customer_id 列,作为 PRIMARY KEY. 您还将使用 first_namelast_name 定义的字段 VARCHAR 数据类型。 但是,首先,您将使用 JSON 数据类型。 该字段将使您能够灵活地接受不同的地址信息,这将是动态的。 例如,一些客户可能只有一个电话号码,而其他客户可能有两个不同的家庭和办公室联系人。

创建 customers 桌子。

mysql> CREATE table customers (
         customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         first_name VARCHAR(50),
         last_name  VARCHAR(50),
         address    JSON
       ) ENGINE = InnoDB;

输出。

Query OK, 0 rows affected (0.02 sec)

定义数据库和表后,您现在将了解 MySQL 支持的不同 JSON 数据类型。

2. MySQL 文档数据类型

在 MySQL 中,执行 SQL 语句时必须将 JSON 值写为字符串。 如果您提供无效的 JSON 文档,MySQL 会解析文本并引发错误。 在开始使用 MySQL JSON 函数之前,请熟悉以下 JSON 数据类型:

JSON 字符串: 这是一种基于文本的数据类型,必须用引号括起来(")。 例如,在下面的 JSON 片段中, SAMPLE PRODUCT 1 是一个 JSON 字符串,而 product_name 是一个 JSON key.

{
   "product_name":"SAMPLE PRODUCT 1"
}

JSON 编号: JSON 数字格式接受整数和带有浮动小数点的值。 在以下文档中 2537.89 是分配给 cost_priceretail_price 值分别。

{
   "product_name":"SAMPLE PRODUCT 1",
   "cost_price":25,
   "retail_price":37.89
}

JSON 布尔值: 这是一种只有两种结果的数据类型。 也就是说,要么 true 或者 false. 您可以在不同情况下使用此数据类型。 例如,在一个 employees 数据库,你可以创建一个 is_married 键并将其设置为 true 或者 false 取决于工作人员的婚姻状况。 此外,在产品目录中,您可以切换产品功能 onoff 通过使用布尔数据类型,如下所示。

{
   "product_name":"SAMPLE PRODUCT 1",
   "cost_price":25,
   "retail_price":37.89
   "available_for_sale":true
   "is_physical_product":false
   "discounted":false
}

JSON 对象: 这是一组键值对,用 {} 括号。 以上示例中使用的所有文档都是 JSON 对象。 但是,当您为单个键嵌套值时,JSON 对象会派上用场。 例如,在下面的示例中, extended_price key 是一个 JSON 对象。

{
   "product_name":"SAMPLE PRODUCT 1",
   "cost_price":25,
   "retail_price":37.89,
   "extended_price":{
      "discounted_price":34.26,
      "whole_sale_price":30.50,
      "shipping_cost":5.21
   }
}

JSON 数组: 这是由逗号分隔并用方括号括起来的值列表。 那是, [ and ]. 例如,要显示两个不同产品的属性,您可以使用以下 JSON 数组。

[
   {
      "product_name":"SAMPLE PRODUCT 1",
      "cost_price":25,
      "retail_price":37.89
   },
   {
      "product_name":"SAMPLE PRODUCT 2",
      "cost_price":180.85,
      "retail_price":256.25
   }
]

现在您已经熟悉了大多数 JSON 数据类型,现在您将验证一些基于 JSON 的示例记录并将其保存到 customers 桌子。

3. 验证、检查类型并将 JSON 文档保存到 MySQL 表

确定要在 JSON 列中使用的格式后,您可以获取文档并将其插入表中。 在本演示中,您将首先插入一个示例 JOHN DOE's 用以下数据记录。

名:

JOHN

姓:

DOE

地址: 在此列中,您将使用 JSON 对象捕获街道地址、城镇、州、邮政编码和不同客户的电话号码,如下所示。

{
   "street":"97 SIMPLE RD. NW #2",
   "town":"NEW TOWN",
   "state":"AZ",
   "zip":1013,
   "phone":{
      "home":111111,
      "work":222222
   },
   "available_in_day_time":true
}

在插入数据之前 customers 表,使用内置的 MySQL JSON_VALID 函数来检查文档的语法是否有效。

mysql> SELECT JSON_VALID('{
                           "street":"97 SIMPLE RD. NW #2",
                           "town":"NEW TOWN",
                           "state":"AZ",
                           "zip":1013,
                           "phone":{
                              "home":111111,
                              "work":222222
                           },
                           "available_in_day_time":true
                        }') AS is_valid;

下面的响应确认上面的地址信息是有效的 JSON 文档。

+----------+
| is_valid |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

接下来,继续将客户信息与 JSON 地址信息一起保存到数据库中。 为了更容易地遵循指南,首先,将联系信息放在 @address 通过执行下面的命令变量。

mysql> SET @address = ('{
                          "street":"97 SIMPLE RD. NW #2",
                          "town":"NEW TOWN",
                          "state":"AZ",
                          "zip":1013,
                          "phone":{
                            "home":111111,
                            "work":222222
                          },
                          "available_in_day_time":true
                       }');

输出。

Query OK, 0 rows affected (0.00 sec)

接下来,使用 JSON_TYPE 函数确认文档类型。

mysql> SELECT JSON_TYPE(@address);

下面的输出证实,地址确实是一个 JSON OBJECT.

+---------------------+
| JSON_TYPE(@address) |
+---------------------+
| OBJECT              |
+---------------------+
1 row in set (0.00 sec)

接下来,执行 INSERT 下面的语句保存客户的记录并使用 @address 变量来捕获地址信息。

mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('JOHN', 'DOE', @address);

MySQL 执行你的 INSERT 语句没有任何错误并显示下面的输出。

Query OK, 1 row affected (0.01 sec)

通过运行确认记录 SELECT 声明反对 customers 桌子。

mysql> SELECT
       customer_id,
       first_name,
       last_name,
       address
       FROM customers;

MySQL 列出了如下所示的记录。

+-------------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer_id | first_name | last_name | address                                                                                                                                                     |
+-------------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|           1 | JOHN       | DOE       | {"zip": 1013, "town": "NEW TOWN", "phone": {"home": 111111, "work": 222222}, "state": "AZ", "street": "97 SIMPLE RD. NW #2", "available_in_day_time": true} |
+-------------+------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如前所述,MySQL 限制将无效数据保存到 JSON 列中。 您可以通过尝试插入以下记录来测试这一点。 首先,给地址变量赋值一个新的值,并故意在两者之间留一个逗号 townstate 键,如下图。

mysql> SET @address = ('{
                          "street":"97 SIMPLE RD. NW #2",
                          "town":"SECOND TOWN"
                          "state":"NY",
                          "zip":5070,
                          "phone":{
                            "home":444444,
                            "work":777777
                          },
                          "available_in_day_time":TRUE
                       }');

通过确认以下输出来确保设置了变量。

Query OK, 0 rows affected (0.00 sec)

然后,尝试使用设置的无效数据创建新客户。

mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('MARY', 'ROE', @address);

MySQL 使命令失败并显示以下错误。 如果您将 JSON 数据直接保存到 VARCHAR 字段,MySQL 不会提供任何形式的验证,并且当您稍后检索并尝试解析数据时会遇到技术错误。 因此,在处理 JSON 文档时始终使用 JSON 数据类型。

ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 145 in value for column 'customers.address'.

您可以看到,上面的错误消息已经足够详细了,它可以让您更好地了解更正 JSON 文档。 然而,虽然这种方法运行良好,但它不会根据您的业务逻辑验证单个键值。 幸运的是,您将看到如何定义 JSON 模式并将其与 CHECK CONSTRAINT 验证单个 JSON 元素。

4. 根据架构验证 JSON 文档

在定义 JSON 列时,MySQL 提供了实现 CHECK CONSTRAINT 用于根据架构验证单个元素。 要测试此功能,请创建一个简单的架构来验证 zip 客户地址的一部分。 例如,在下面的架构中,指定一个有效的 zip 代码必须是介于两者之间的数字 19999 通过运行下面的代码。

mysql> SET @valid_zip = '{
                             "type":"object",
                                "properties":{
                                   "zip":{
                                      "type":"number",
                                      "minimum":1,
                                      "maximum":9999
                                   }
                                }
                          }';

输出。

Query OK, 0 rows affected (0.00 sec)

接下来,定义一个违反规则的新地址。 在这种情况下,设置 zip 到一个很大的价值 999999.

mysql> SET @address = ('{
                          "street":"101 2nd RD.",
                          "town":"NEW TOWN",
                          "state":"NJ",
                          "zip":999999,
                          "phone":{
                            "home":444444,
                            "work":888888
                          },
                          "available_in_day_time":true
                       }');

输出。

Query OK, 0 rows affected (0.00 sec)

现在,使用 MySQL JSON_SCHEMA_VALID 函数来测试地址 @address 符合您设置的架构 @valid_zip.

mysql> SELECT JSON_SCHEMA_VALID(@valid_zip, @address);

您可以看到输出显示 0,表示数据无效。 应返回有效值 1.

+-----------------------------------------+
| JSON_SCHEMA_VALID(@valid_zip, @address) |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.00 sec)

设置有效 zip 新地址中的代码,例如, 3630.

mysql> SET @address = ('{
                          "street":"101 2nd RD.",
                          "town":"NEW TOWN",
                          "state":"NJ",
                          "zip":3630,
                          "phone":{
                            "home":444444,
                            "work":888888
                          },
                          "available_in_day_time":true
                       }');

输出。

Query OK, 0 rows affected (0.00 sec)

通过执行以下命令检查新地址值是否在范围内。

mysql> SELECT JSON_SCHEMA_VALID(@valid_zip, @address);

的价值 1 下面证实了 3630zip 键入地址列。

+-----------------------------------------+
| JSON_SCHEMA_VALID(@valid_zip, @address) |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)

在多用户数据库环境中工作时,您不能相信所有用户都会在运行之前验证 JSON 数据 INSERT 陈述。 在这种情况下,你应该实现一个 CHECK CONSTRAINT 定义表时。

由于您已经创建了 customers 表,使用下面的命令更改它以设置 zip 代码约束。

mysql> ALTER TABLE customers
       ADD CONSTRAINT zip_validator
       CHECK(JSON_SCHEMA_VALID('{
                                    "type":"object",
                                    "properties":{
                                        "zip":{
                                            "type":"number",
                                            "minimum":1,
                                            "maximum":9999
                                        }
                                 }
                               }', address));

确保您收到以下确认信息。

Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

接下来,尝试将新的无效记录插入 customers 桌子。 首先将地址信息放入 @address 多变的。

mysql> SET @address = ('{
                          "street":"1 SAMPLE STREET",
                          "town":"THIRD TOWN",
                          "state":"NY",
                          "zip":10000,
                          "phone":{
                            "home":222222,
                            "work":666666
                          },
                          "available_in_day_time":false
                       }');

输出。

Query OK, 0 rows affected (0.00 sec)

然后,执行 INSERT 下面的声明。

mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('JANE', 'SMITH', @address);

MySQL 显示以下错误,详细说明 CHECK CONSTRAINT 设置已被违反。

ERROR 3819 (HY000): Check constraint 'zip_validator' is violated.

改变 zip 将新地址中的代码更改为约束设置范围内的值。

mysql> SET @address = ('{
                          "street":"1 SAMPLE STREET",
                          "town":"THIRD TOWN",
                          "state":"NY",
                          "zip": 7630,
                          "phone":{
                            "home":222222,
                            "work":666666
                          },
                          "available_in_day_time":false
                       }');

输出。

Query OK, 0 rows affected (0.00 sec)

然后,执行 INSERT 再次声明并检查响应。

mysql> INSERT INTO customers (first_name, last_name, address) VALUES ('JANE', 'SMITH', @address);

MySQL 现在应该插入新行并显示下面的确认输出。

Query OK, 1 row affected (0.01 sec)

在下一步中,您将从 MySQL 表中解析 JSON 文档。

5.解析JSON文档

您可以反序列化 JSON 文档并使用 MySQL 检索任何命名键的值 JSON_EXTRACT 功能。 例如,检索客户的信息以及他们的个人信息 town 名称,请在示例表中运行下面的 SQL 命令。

mysql> SELECT
       first_name,
       last_name,
       JSON_EXTRACT(address, '$.town') as town
       FROM customers;

您可以从下面的输出中看到 town 名称已被提取。

+------------+-----------+--------------+
| first_name | last_name | town         |
+------------+-----------+--------------+
| JOHN       | DOE       | "NEW TOWN"   |
| JANE       | SMITH     | "THIRD TOWN" |
+------------+-----------+--------------+
2 rows in set (0.00 sec)

类似地,如果您想提取嵌套在 JSON 文档中更深一层的键的值,例如, home 电话号码,请使用以下语法。

mysql> SELECT
       first_name,
       last_name,
       JSON_EXTRACT(address, '$.phone.home') as phone
       FROM customers;

您现在拥有 home 电话号码如下图。

+------------+-----------+--------+
| first_name | last_name | phone  |
+------------+-----------+--------+
| JOHN       | DOE       | 111111 |
| JANE       | SMITH     | 222222 |
+------------+-----------+--------+
2 rows in set (0.00 sec)

使用相同的路径提取语法,检索 work 通过执行下面的命令,电话号码。

mysql> SELECT
       first_name,
       last_name,
       JSON_EXTRACT(address, '$.phone.work') as phone
       FROM customers;

输出。

+------------+-----------+--------+
| first_name | last_name | phone  |
+------------+-----------+--------+
| JOHN       | DOE       | 222222 |
| JANE       | SMITH     | 666666 |
+------------+-----------+--------+
2 rows in set (0.00 sec)

此外,当您想在不进行额外解析的情况下检索 JSON 值时,您可以使用 JSON_PRETTY() 功能。 例如,要检索打印精美的客户地址信息列表,请执行以下命令。

mysql> SELECT
       JSON_PRETTY(address)
       FROM customers;

地址信息现在应该以整洁的格式打印,如下所示。

+---------------------------------------+
| JSON_PRETTY(address)                                                                                                                                                                  |
+---------------------------------------+
| {
  "zip": 1013,
  "town": "NEW TOWN",
  "phone": {
    "home": 111111,
    "work": 222222
  },
  "state": "AZ",
  "street": "97 SIMPLE RD. NW #2",
  "available_in_day_time": true
} |
| {
  "zip": 7630,
  "town": "THIRD TOWN",
  "phone": {
    "home": 222222,
    "work": 666666
  },
  "state": "NY",
  "street": "1 SAMPLE STREET",
  "available_in_day_time": false
}  |
+---------------------------------------+
2 rows in set (0.00 sec)

从 MySQL 结果中可以看出,JSON 函数按预期工作。

结论

在本教程中,您创建了一个示例测试数据库并了解了 MySQL 支持的所有 JSON 数据类型,包括字符串、数字、数组、对象和布尔变量。 您还完成了根据自定义模式验证 JSON 文档的步骤。 最后,您已经使用优雅的路径提取语法从数据库中解析并提取了 JSON 值。 下次在 MySQL 中处理 JSON 文档时,请使用您在本指南中获得的知识。

注:本教程在Vultr VPS上测试通过,如需部署请前往Vultr.com