Python MySQL Shell 入门指南（三）

最新推荐文章于 2024-11-12 21:42:05 发布

绝不原创的飞龙

最新推荐文章于 2024-11-12 21:42:05 发布

阅读量767

点赞数 15

分类专栏： VKDoc 文章标签： VKDoc

License CC BY-NC-SA 4.0 / 自豪地采用谷歌翻译

本文链接：https://blog.csdn.net/wizardforcel/article/details/141068592

版权

VKDoc 专栏收录该内容

2045 篇文章

订阅专栏

原文：Introducing MySQL Shell

协议：CC BY-NC-SA 4.0

六、将 Shell 与文档存储一起使用

到目前为止，在本书中，我们已经发现了 MySQL Shell，并了解了如何使用它来取代旧的 MySQL 客户端(MySQL ),以及如何使用 Shell 通过传统的 SQL 命令来管理我们的数据库。我们还学习了如何使用 X DevAPI 使用 shell 来开发我们的关系数据库代码，甚至进行测试！

现在是时候了解什么是文档存储以及我们如何开始使用它了。是的，你也可以用贝壳做同样的事情。核心概念是 JavaScript 对象符号(JSON)文档。我们将了解更多关于 JSON 是什么以及 MySQL 文档存储是如何工作的。我们还将看到几个如何将 JSON 与关系数据库结合的例子。在下一章，我们将着手用 JSON 文档构建一个 NoSQL 解决方案。

让我们简短地了解一下术语和技术概述。

概观

MySQL 文档存储库的起源在于几种技术，它们被结合在一起形成了文档存储库。具体来说，Oracle 将键、值机制与新的数据类型、新的编程库和新的访问机制结合起来，创建了现在的文档存储。这不仅允许我们使用带有 NoSQL 接口的 MySQL，还允许我们构建混合解决方案，利用关系数据的稳定性和结构，同时增加 JSON 文档的灵活性。

在本章中，我们将了解 MySQL 如何支持 JSON 文档，包括如何添加、查找、更新和删除数据(通常分别称为创建、读取、更新和删除或简称为 CRUD)。我们从本章和下一章中你将会遇到的概念和技术的更多信息开始。然后我们将继续学习更多关于 MySQL 服务器中 JSON 数据类型和 JSON 函数的知识。虽然这一章的重点是在关系数据中使用 JSON，但是掌握 MySQL 文档存储 NoSQL 接口(X DevAPI)需要一个关于如何使用 JSON 的坚实基础。

在 MySQL 中使用文档存储和 JSON 时，我们会遇到一些新概念、新技术和相关术语。在这一节中，我们将看到这些概念和技术如何解释 JSON 数据类型和文档存储接口的组成。让我们从 JSON 使用的最基本的概念开始:键、价值机制。

起源:关键，价值机制

像这个世界上的大多数东西一样，没有什么东西是真正新的，因为它是完全原创的，没有以前出现过的某种形式，因此通常是以新的方式应用现有技术构建的。键、值机制是基础技术的一个典型例子。我们使用术语“机制”,因为键的使用允许您访问值。

当我们说 key，value 时，我们的意思是存在一些标签(通常是一个字符串)构成了键，并且每个键都与一个值相关联。例如，"name":"Charlie"是一个示例，其中 key ( name)具有一个值(Charlie)。虽然键值存储中的值通常是短字符串，但是值可以是复杂的；数字、字母数字、列表，甚至嵌套键、值集。

Key，value 机制最出名的是易于编程使用，同时仍然保持可读性。也就是说，通过大量使用空格，一个复杂的嵌套键，值数据结构可以被人类读取。下面显示了一个示例，其格式类似于一些开发人员格式化代码的方式。 ¹ 可以看出，很容易看出这一组键、值都存储了什么；姓名、地址和电话号码。

{ "name": {
    "first":"Charlie",
    "last":"Harrington"
  },
  "address": {
    "street":"123 Main Street",
    "city":"melborne",
    "state":"California",
    "zip":"90125"
  }
  "phone_numbers": [
      "800-555-1212",
      "888-212-1234"
    ]
  }
}

键、值机制(或存储)的一个例子是可扩展标记语言(XML)，它已经存在了一段时间。下面是一个使用上述数据的简单 XML 示例。它是 SQL SELECT查询的结果，输出(行)以 XML 格式显示。注意 XML 是如何使用 HTML 这样的标签(因为它是从 HTML 派生出来的)以及数据的键、值存储的。这里，键是<row>、<field>，值是开始和结束标记符号之间的内容(<field> </field>)。

<?xml version="1.0"?>
<resultset statement="select ∗ from thermostat_model limit 1;" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="model_id">acme123</field>
    <field name="brand">WeMakeItSoCo</field>
  </row>
</resultset>

有些系统是围绕键、值机制(称为键、值或关系存储)设计的，比如语义网。简而言之，语义网试图利用数据的关联来描述事物、事件等。有时，术语“关系存储”或“三重存储”被用来描述所采用的存储系统的类型。语义 Web 中使用了几种形式的关键值机制，包括资源描述框架(RDF)、Web 本体语言(OWL)和可扩展标记语言(XML)。还有其他一些关键值机制的例子，但是与文档存储最相关的是 JSON。

现在让我们看看文档库的另一个关键组件——从编程库开始的 NoSQL 接口。

应用界面

回想一下，应用编程接口(API)，有时简称为库或编程库，是一组支持一个或多个功能的操作的类和方法。通过这些类和方法，这些功能允许程序员使用这些类和方法来执行各种任务。

在 MySQL 文档存储的情况下，我们使用 X Developer API (X DevAPI)通过一组类和方法来访问服务器，这些类和方法提供了到服务器的连接，以及集合、表、SQL 操作等概念的抽象。这些结合起来允许一个 NoSQL 接口到 MySQL 服务器。

NoSQL 接口

关于 NoSQL，有几个有时相互矛盾的定义(如果不是例子的话)。出于本书和 MySQL 的目的，NoSQL 接口是一种不需要使用 SQL 语句来访问数据的 API。API 本身提供了到服务器的连接，以及创建、检索、更新和删除数据的类和方法。在上一章中，我们已经看到了这个范例的作用。

此时，您可能想知道 MySQL 如何处理将 JSON 文档与关系数据结合使用的混合选项。简单地说，MySQL 被设计成允许在关系数据中存储和检索 JSON 文档(通过 SQL 接口)。也就是说，服务器已经被修改来处理 JSON 文档。还有一组函数允许您对 JSON 数据做各种各样的事情，使得通过 SQL 接口使用 JSON 变得很容易。

然而，您也可以通过 NoSQL X DevAPI 使用 JSON 文档，或者通过 SQL 命令，或者使用 X DevAPI 的特殊类和方法作为纯文档存储。我们将在本章中看到使用 JSON 的两种方式的概述，并在下一章深入探讨通过 NoSQL 接口使用 JSON 文档。

文档存储

文档存储(也称为面向文档的数据库)是一个用于管理半结构化数据(即文档)的存储和检索系统。现代文档存储系统支持 XML 和 JSON 中的键、值结构。因此，文档存储系统有时被认为是关键值存储系统的一个子类。

文档存储系统也通常由实现为编程接口(API)的 NoSQL 接口来访问，该 API 允许开发者将文档的存储和检索合并到他们的程序中，而不需要第三方访问机制(API 实现访问机制)。

事实上，描述数据的元数据嵌入在数据本身中。粗略地说，这意味着键和键的布局(排列或嵌套)形成元数据，并且元数据对于存储机制变得不透明。更具体地说，数据如何排列(文档如何形成或描述数据)不反映在存储机制中，也不由存储机制管理。对半结构化数据的访问需要使用 NoSQL 接口访问为处理文档本身而设计的机制。

这两种特性，半结构化数据和 NoSQL 接口，是文档存储与关系数据的区别。关系数据需要不灵活的结构，迫使所有数据符合特定的结构。数据也以相同的结构分组在一起，通常很少考虑内容可能不同的数据。因此，我们通常不会看到通过传统关系数据机制访问文档存储。也就是说，直到现在。

使用文档存储有趣的一点是，学习如何使用文档存储并不需要成为 JavaScript 或 Python 专家。事实上，你要做的大部分事情并不需要掌握任何编程语言。也就是说，有很多关于如何做事的例子，所以你不需要学习所有关于这门语言的知识。事实上，你可以很快找到你需要的东西，然后随着你的需求的成熟，学习更多的语言知识。

数据

JSON 是一种人类和机器可读的基于文本的数据交换格式。它也是独立于平台的，这意味着不存在禁止它在几乎任何编程语言中使用的格式概念。此外，JSON 是互联网上广泛使用的一种格式。

JSON 允许您在不违反任何结构的情况下以任何方式描述数据。事实上，您可以按照自己的意愿设置数据的格式(布局)。唯一真正的限制是描述符(花括号、方括号、引号、逗号等)的正确使用，这些描述符必须对齐，在某些情况下必须正确配对。下面是一个有效 JSON 字符串的示例。

{
    "address": {
        "street": "123 First Street",
        "city": "Oxnard",
        "state": "CA",
        "zip": "90122"
    }
}

如果您认为这看起来很像之前的关键价值示例，那么您是对的，就是它！鉴于 JSON 是如何形成的，这并不是错误。然而，我们经常使用术语 string 来谈论 JSON，实际上有时我们看到 JSON 没有空格和换行符，如下所示。事实证明，大多数编程语言 JSON 机制可以正确解释空格和换行符。我们将在后面的章节中看到更多相关内容。

{"address": {"street": "123 First Street","city": "Oxnard","state": "CA","zip": "90122"}}

当编程语言支持时，开发人员可以通过键访问数据来轻松读取数据。更好的是，开发人员不需要知道键是什么(但这很有帮助！)因为它们可以使用语言支持机制来获取键并对它们进行迭代。这样，像 XML 一样，数据是自描述的。

现在，让我们深入了解什么是 JSON 文档，以及如何在 MySQL 中使用它们。

MySQL 中的 JSON 文档介绍

在 MySQL 5.7.8 和更高版本中，我们可以使用 JSON 数据类型将 JSON 文档存储在传统关系数据库表中存储的行的字段(列)中。有些人可能尝试(并成功)在 blob 或文本字段中存储 JSON。虽然这是可能的，但有几个很好的理由不这样做。最令人信服的原因是，它要求应用完成所有读取和写入 JSON 文档的繁重工作，从而使它变得更加复杂，并且可能容易出错。JSON 数据类型以两种方式克服了这个问题。

验证:JSON 数据类型提供文档验证。也就是说，只有有效的 JSON 才能存储在 JSON 列中。
高效访问:当一个 JSON 文档存储在一个表中时，存储引擎将数据打包成一种特殊的优化二进制格式，允许服务器快速访问数据元素，而不是每次访问数据时都解析数据。

这为以结构化形式(关系数据)存储非结构化数据开辟了一条全新的途径。然而，Oracle 并没有止步于简单地向 MySQL 添加 JSON 数据类型。Oracle 还增加了一个复杂的编程接口，以及将文档作为集合存储在数据库中的概念。我们将在下一章更多地讨论这些方面。在这一章中，我们将看到如何将 JSON 用于关系数据。

快速启动

如果您以前从未使用过 JSON，本节将帮助您入门。关于 JSON 及其在 MySQL 中的使用，您只需要了解一些东西，但最重要的是 JSON 格式化规则。

JSON 是由用括号括起来的字符串组成的，或者是用某些符号组织起来的。虽然我们已经讨论了与 JSON 相关的键、值机制，但是 JSON 属性有两种类型:由逗号分隔的列表形成的数组和由一组键、值对形成的对象。也可以嵌套 JSON 属性。例如，数组可以包含对象，对象键中的值可以包含数组或其他对象。JSON 数组和对象的组合称为 JSON 文档。

JSON 数组包含一个由逗号分隔并括在方括号([ ])中的值列表。例如，以下是有效的 JSON 数组。

["Cub Cadet", "Troy-Bilt", "John Deere", "Craftsman"]
[33,67,1,55,909]
[True, True, False, False]

注意，我们用方括号开始和结束数组，并用逗号分隔值。虽然我们没有使用空白，但是您可以使用空白，并且根据您的编程语言，您还可以使用换行符、制表符和回车符。例如，下面仍然是一个有效的 JSON 数组。

[
True,
12,
False,
33
]

JSON 对象是一组键/值对，其中每个键/值对都包含在左花括号和右花括号({ })中，并用逗号分隔。例如，以下是有效的 JSON 对象。注意，键地址有一个 JSON 对象作为它的值。

{"address": {
   "street": "123 First Street",
   "city": "Oxnard",
   "state": "CA",
   "zip": "90122"
}}

{"address": {
    "street":"4 Main Street",
    "city":"Melborne",
    "state":"California",
    "zip":"90125"
}}

JSON 数组通常用于包含相关(嗯，有时)事物的列表，JSON 对象用于描述复杂的数据。JSON 数组和对象可以包含标量值，比如字符串或数字、null文字(就像在关系数据中一样)，或者布尔文字true和false。请记住，键必须始终是字符串，并且通常用引号括起来。最后，JSON 值还可以包含时间信息(日期、时间或日期时间)。例如，下面显示了一个带有时间值的 JSON 数组。

["15:10:22.021100", "2019-03-23", "2019-03-23 08:51:29.012310"]

下一节将描述我们如何在 MySQL 中使用 JSON。在这种情况下，我们指的是关系数据，但是 JSON 文档的格式在文档存储中是相同的。

结合 SQL 和 JSON

对关系数据使用 JSON 可能看起来有点不寻常或反直觉。也就是说，为什么要在列中使用非结构化数据？这难道不违反一些关系数据库理论定律之类的吗？ ² 虽然在某种程度上这可能是真的，但在我们的关系数据中添加非结构化数据的能力打开了以前关闭的几扇门。

例如，假设您需要为已经部署了一段时间的应用向现有表中添加更多数据。如果添加一个新列，就有可能需要修改所有使用该数据的应用。 ^{3 目前为止没什么大不了的，对吧？但是，如果这些数据在类型和范围上都不同，也就是说，对于任何给定的一组行，添加的数据不能以相同的方式描述，或者每一行的数据都不同，该怎么办呢？这是非结构化数据的本质，它没有预定义的结构。因此，您不能轻松地扩展表，甚至不能创建新的引用(子)表。}

这就是拥有 JSON 专栏有所帮助的地方。您只需添加一个新列，并将非结构化数据存储为 JSON。当然，这不会改变您必须更改应用的可能性，但是这确实意味着您不必重新调整数据库本身(除了添加 JSON 列之外)或者将数据强行放入一组类型化的列中。

在本节中，我们将了解如何在 MySQL 中使用 JSON，包括在 SQL 语句中包含 JSON 字符串的机制、MySQL 中可用于 JSON 的一些特殊函数、如何在 SQL 语句中访问 JSON 文档的部分内容，以及如何在关系数据中使用 JSON 列。我们将使用 shell 来演示这些主题。我们把使用纯 JSON 文档的工作留到下一章。

小费

MySQL Shell 在使用 JSON 方面有一些增强，包括以人类可读的形式显示 JSON 的能力。我们可以看到原始 JSON (json/raw)或打印精美的 JSON (json)的结果。对漂亮打印的 JSON 使用--result-format=json命令行选项，或者对无格式的 JSON 输出使用--result-format=json/raw命令行选项。

在 MySQL 中格式化 JSON 字符串

在 MySQL 中使用时，JSON 文档被写成字符串。MySQL 解析 JSON 数据类型中使用的任何字符串来验证文档。如果文档无效(不是格式正确的 JSON 文档)，您将得到一个错误。您可以在任何合适的 SQL 语句中使用 JSON 文档，比如INSERT和UPDATE语句以及像WHERE子句这样的子句。

小费

正确格式化 JSON 文档可能有点困难。最需要记住的是平衡你的引号，正确使用逗号，平衡所有的花括号和方括号。

当您将键和值指定为字符串时，必须使用双引号字符(")，而不是单引号(')。因为 MySQL 期望 JSON 文档是字符串，所以您可以在整个 JSON 文档中使用单引号，而不是在文档中使用单引号。幸运的是，MySQL 提供了许多特殊的函数，您可以在 JSON 文档中使用，其中一个是JSON_VALID()函数，它允许您检查 JSON 文档的有效性。如果文档有效，则返回 1，否则返回 0。下面显示了使用单引号验证键和值的 JSON 文档与使用双引号验证格式正确的 JSON 文档的结果。

注意

此后，为了简洁起见，我们将省略 shell SQL 提示符。

> SELECT JSON_VALID("{'vendor': {'name': 'Craftsman','URL': 'http://www.craftsman.com','sources': 'Lowes'}}") AS IS_VALID \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
IS_VALID: 0
1 row in set (0.0005 sec)

> SELECT JSON_VALID('{"vendor": {"name": "Craftsman","URL": "http://www.craftsman.com","sources": "Lowes"}}') AS IS_VALID \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
IS_VALID: 1
1 row in set (0.0040 sec)

请注意，带双引号的字符串是有效的(函数返回 1)，而带单引号的字符串是无效的(函数返回 0)。这是大多数人第一次使用 JSON 时最容易犯的错误。

在 SQL 语句中使用 JSON 字符串

让我们看看如何在 SQL 语句中使用 JSON 文档。假设我们想在一个表中存储地址。对于这个例子，我们将保持简单，将数据插入一个非常简单的表中。清单 6-1 显示了从创建一个测试表开始，然后插入前两个地址的练习的抄本。

C:\Users\cbell> mysqlsh --uri root@localhost:33060 --sql
MySQL Shell 8.0.16
...
> CREATE DATABASE `testdb_6`;
Query OK, 1 row affected (0.0098 sec)

> USE `testdb_6`;
Query OK, 0 rows affected (0.0010 sec)

> CREATE TABLE `testdb_6`.`addresses` (`id` int(11) NOT NULL AUTO_INCREMENT, `address` json DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.0310 sec)

> INSERT INTO `testdb_6`.`addresses` VALUES (NULL, '{"address": {"street": "123 Second St","city": "Maynard","state": "CT","zip": "19023"}}');
Query OK, 1 row affected (0.0042 sec)

> INSERT INTO `testdb_6`.`addresses` VALUES (NULL, '{"address": {"street":"41 West Hanover","city":"Frederick","state":"Maryland","zip":"20445"}}');
Query OK, 1 row affected (0.0030 sec)

> SELECT ∗ FROM `testdb_6`.`addresses` \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
     id: 1
address: {"address": {"zip": "19023", "city": "Maynard", "state": "CT", "street": "123 Second St"}}
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 2\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
     id: 2
address: {"address": {"zip": "20445", "city": "Frederick", "state": "Maryland", "street": "41 West Hanover"}}
2 rows in set (0.0005 sec)

> DROP DATABASE `testdb_6`;
Query OK, 1 row affected (0.0132 sec)

Listing 6-1Using JSON with SQL Statements

注意，在CREATE语句中，我们使用了数据类型JSON。这通知 MySQL 在存储引擎中分配特殊的存储机制来处理 JSON。与一些报告相反，JSON 数据类型不仅仅是字符串的直接存储。相反，它是在内部组织的，以优化元素的检索。因此，正确格式化 JSON 非常重要。一个表中可以有多个 JSON 列。然而，一个表行中 JSON 文档的总和被限制为变量max_allowed_packet的值。

注意

JSON 列不能像其他列一样有默认值。

现在，让我们看看如果在 SQL 语句中使用无效的 JSON 文档(字符串)会发生什么。下面显示了插入上一个示例中的最后一个地址的尝试，只是没有在关键字周围加上正确的引号。请注意抛出的错误。

> INSERT INTO testdb_6.addresses VALUES (NULL, '{"address": {street:"173 Caroline Ave",city:"Monstrose",state:"Georgia",zip:31505}}');
ERROR: 3140: Invalid JSON text: "Missing a name for object member." at position 13 in value for column 'addresses.address'.

对于任何格式不正确的 JSON 文档，您都可能会看到这样或那样的错误。如果你想先测试你的 JSON，使用JSON_VALID()函数。然而，在构建 JSON 文档时，还有另外两个函数可能会有所帮助；JSON_ARRAY()和JSON_OBJECT()。

JSON_ARRAY()函数接受一个值列表，并返回一个有效的格式化 JSON 数组。下面显示了一个示例。注意，它返回了一个格式正确的 JSON 数组，带有正确的引号(双引号而不是单引号)和方括号。

> SELECT JSON_ARRAY(1, true, 'test', 2.4);
+----------------------------------------+
| JSON_ARRAY(1, true, 'test', 2.4) ------|
+----------------------------------------+
| [1, true, "test", 2.4]    ------       |
+----------------------------------------+
1 row in set (0.00 sec)

JSON_OBJECT()函数接受一个键、值对列表，并返回一个有效的 JSON 对象。下面显示了一个示例。注意这里我在调用函数时使用了单引号。这只是一个例子，我们可能会对使用哪种引语感到困惑。在这种情况下，函数的参数不是 JSON 文档；它们是普通的 SQL 字符串，可以使用单引号或双引号。

> SELECT JSON_OBJECT("street","4 Main Street","city","Melborne",'state','California','zip',90125) \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_OBJECT("street","4 Main Street","city","Melborne",'state','California','zip',90125): {"zip": 90125, "city": "Melborne", "state": "California", "street": "4 Main Street"}
1 row in set (0.0040 sec)

再次注意函数结果中引号的自动转换。如果您需要动态构建 JSON，这可能会很有帮助。

还有一个用于构造 JSON 文档的有用函数；JSON_TYPE()功能。这个函数获取一个 JSON 文档，并将其解析成一个 JSON 值。如果值有效，它将返回该值的 JSON 类型，如果无效，它将抛出一个错误。下面显示了该函数与前面语句的用法。

> SELECT JSON_TYPE('[1, true, "test", 2.4]');
+-------------------------------------------+
| JSON_TYPE('[1, true, "test", 2.4]')       |
+-------------------------------------------+
| ARRAY                                     |
+-------------------------------------------+
1 row in set (0.00 sec)

> SELECT JSON_TYPE('{"zip": 90125, "city": "Melborne", "state": "California", "street": "4 Main Street"}') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_TYPE('{"zip": 90125, "city": "Melborne", "state": "California", "street": "4 Main Street"}'): OBJECT
1 row in set (0.00 sec)

MySQL 提供了更多的函数来处理 JSON 数据类型。我们将在后面的章节中看到更多关于这些的内容。

本节只描述了在 SQL 语句中使用 JSON 和 MySQL 的基础知识。事实上，JSON 文档的格式化也适用于文档存储。然而，有一件事我们还没有谈到——如何访问 JSON 文档中的元素。

路径表达式

为了访问一个元素——通过它的键——我们使用一种叫做路径表达式的特殊符号。下面是一个简单的例子。请注意 WHERE 子句。这显示了一个路径表达式，其中我检查 address 列是否包含用特殊符号address->'$.address.city'引用的 JSON 键“city”。我们将在下一节看到更多关于路径表达式的细节。

> SELECT id, address->'$.address.city' FROM test.addresses
  WHERE address->'$.address.zip' = '90125';
+----+---------------------------+
| id | address->'$.address.city' |
+----+---------------------------+
|  2 | "Melborne"                |
+----+---------------------------+
1 row in set (0.00 sec)

如果您认为 JSON 文档可能是一组复杂的半结构化数据，并且在某些时候您需要访问文档中的某些元素，那么您可能还想知道如何从 JSON 文档中获得您想要的东西。幸运的是，有一种机制可以做到这一点，它被称为路径表达式。更具体地说，它是一种快捷表示法，您可以在 SQL 命令中使用它来获取元素，而无需额外的编程或脚本。

正如您将看到的，这是一种非常特殊的语法，虽然表达性不是很好(用英语读起来不是很好)，但这种符号可以让您无需大量额外输入就能得到您需要的东西。路径表达式以包含在字符串中的美元符号($)开始。但是这个符号必须有一个上下文。在 SQL 语句中使用路径表达式时，必须使用JSON_EXTRACT()函数，它允许您使用路径表达式从 JSON 文档中提取数据。这是因为，与 X DevAPI 类和方法不同，并非所有 SQL 语句都直接支持路径表达式(但是我们将看到有些语句支持路径表达式)。例如，如果您想要数组中的第三个项目(在本例中是数字 3)，您可以如下使用该函数。

> SELECT JSON_EXTRACT('[1,2,3,4,5,6]', '$[2]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_EXTRACT('[1,2,3,4,5,6]', '$[2]'): 3
1 row in set (0.0049 sec)

注意这是访问 JSON 数组中的数据。在这里，我们使用一个数组下标，并在索引周围加上方括号(元素从 0 开始)，就像在许多编程语言中使用数组一样。

小费

SQL 接口中路径表达式的使用仅限于其中一个 JSON 函数，或者仅用于已被修改为接受路径表达式的特定子句，如SELECT列列表或WHERE、HAVING、ORDER BY或GROUP BY子句。

现在假设你想通过键访问一个元素。你也可以这样做。在这种情况下，我们使用美元符号后跟一个句点，然后是键名。下面显示了如何检索包含个人姓名和地址的 JSON 对象的姓氏。

> SELECT JSON_EXTRACT('{"name": {"first":"Billy-bob","last":"Throckmutton"},"address": {"street":"4 Main Street","city":"Melborne","state":"California","zip":"90125"}}', '$.name.first') AS Name \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
Name: "Billy-bob"
1 row in set (0.0008 sec)

请注意，我必须使用两个级别的访问权限。也就是说，我想要名为 name 的对象中名为 first 的键的值。于是，我用了“$.name.first”。这演示了如何使用路径表达式深入 JSON 文档。这也是为什么我们称之为路径表达式，因为我们形成表达式的方式给了我们到元素的“路径”。

现在我们已经看到了一些例子，让我们回顾一下路径表达式的完整语法；两者都在 SQL 和 NoSQL 接口中使用。除非另有说明，否则语法方面适用于两种接口。

再说一次，路径表达式以美元符号开始，后面可以有几种叫做选择器的语法形式，允许我们请求文档的一部分。这些选择器包括以下内容。

一个句点后跟一个键名，引用该键的值。如果不带引号的名称无效(要求引号是有效的标识符，如带空格的键名)，则必须在双引号内指定键名。
使用带有整数索引([n])的方括号来选择数组中的元素。索引从 0 开始。
路径可以包含通配符*或**，如下所示。
- .[*]计算 JSON 对象中所有成员的值。
- [*]计算 JSON 数组中所有元素的值。
- 前缀∫后缀之类的序列计算以指定前缀开始并以指定后缀结束的所有路径。
可以使用句点作为分隔符来嵌套路径。在这种情况下，句点之后的路径在父路径上下文的上下文中进行评估。例如，$.name.first将名为 first 的键的搜索限制在名称 JSON 对象。

如果路径表达式被评估为 false 或无法定位数据项，服务器将返回 null。例如，以下返回 null，因为数组中只有 6 项。你能看出为什么吗？记住，计数从 0 开始。对于那些不熟悉使用路径表达式(或者编程语言中的数组)的人来说，这是一个常见的错误。

> SELECT JSON_EXTRACT('[1,2,3,4,5,6]', '$[6]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_EXTRACT('[1,2,3,4,5,6]', '$[6]'): NULL
1 row in set (0.0008 sec)

但是等等，路径表达式还有一个更好的选择。我们可以走捷径！也就是说，当按列访问 SQL 语句中的数据时，可以使用破折号和大于号(->)来代替JSON_EXTRACT()函数。多酷啊。使用->操作有时被称为“内嵌路径表达式”。例如，我们可以编写前面的示例，从一个表中查找 JSON 数组中的第三个项目，如下所示。

> CREATE TABLE testdb_6.ex1 (id int AUTO_INCREMENT PRIMARY KEY, recorded_data JSON);
Query OK, 0 rows affected (0.0405 sec)
> INSERT INTO testdb_6.ex1 VALUES (NULL, JSON_ARRAY(1,2,3,4,5,6));
Query OK, 1 row affected (0.0052 sec)
> INSERT INTO testdb_6.ex1 VALUES (NULL, JSON_ARRAY(7,8,9));
> SELECT ∗ FROM testdb_6.ex1 WHERE recorded_data->'$[2]' = 3 \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
           id: 1
recorded_data: [1, 2, 3, 4, 5, 6]
1 row in set (0.0045 sec)

注意，我只是使用了列名recorded_data，并在末尾添加了->，然后列出了路径表达式。太棒了！

但是等等，还有更多。这种捷径还有一种形式。如果->操作(JSON_EXTRACT)的结果是一个带引号的字符串，我们可以使用->>符号(称为内嵌路径操作符)来检索不带引号的值。这在处理数值时很有帮助。下面给出了两个例子。一个与->操作相同，一个与->>操作相同。

> INSERT INTO testdb_6.ex1 VALUES (NULL, '{"name":"will","age":"43"}');
Query OK, 1 row affected (0.00 sec)
> INSERT INTO testdb_6.ex1 VALUES (NULL, '{"name":"joseph","age":"11"}');
Query OK, 1 row affected (0.00 sec)
> SELECT ∗ FROM testdb_6.ex1 WHERE recorded_data->>'$.age' = 43 \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
           id: 3
recorded_data: {"age": "43", "name": "will"}
1 row in set (0.0014 sec)
> SELECT ∗ FROM testdb_6.ex1 WHERE recorded_data->'$.age' = '43' \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
           id: 3
recorded_data: {"age": "43", "name": "will"}
1 row in set (0.0009 sec)

注意，recorded_data值(年龄和姓名)被存储为一个字符串。但是如果数据存储为整数会怎么样呢？观察。

> INSERT INTO testdb_6.ex1 VALUES (NULL, '{"name":"amy","age":22}');
Query OK, 1 row affected (0.0075 sec)
> SELECT ∗ FROM testdb_6.ex1 WHERE recorded_data->'$.age' = 22 \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
           id: 5
recorded_data: {"age": 22, "name": "amy"}
1 row in set (0.0010 sec)
> SELECT ∗ FROM testdb_6.ex1 WHERE recorded_data->>'$.age' = 22 \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
           id: 5
recorded_data: {"age": 22, "name": "amy"}
1 row in set (0.0009 sec)

啊哈！因此，当值必须不加引号时，->>操作最有用。如果它们已经被取消引用(比如一个整数)，那么->>操作将返回与->操作相同的结果。

请注意，快捷方式(内嵌路径表达式)的使用并不能直接替代JSON_EXTRACT()功能。下面总结了这些限制。

数据源:在 SQL 语句中使用时，内嵌路径表达式只使用指定的字段(列)。该函数可以使用任何 JSON 类型的值。
路径表达式字符串:内嵌路径表达式必须使用普通字符串。该函数可以使用任何字符串类型的值。
表达式数量:内联路径表达式只能对单个字段(列)使用一个路径表达式。该函数可以对一个 JSON 文档使用多个路径表达式。

现在让我们看看可以用来处理 JSON 文档的各种 JSON 函数。

JSON 函数

在 MySQL 中有许多使用 JSON 的函数。我们不会列出所有的功能和过时的风险(似乎每个版本都会添加新的功能)，而是列出一些最常用的功能，让您了解哪些功能可用。虽然我们不会探究每个函数的细微差别，但我们会在后面的章节中看到其中一些函数的使用。表 6-1 列出了 MySQL 8 中可用的 JSON 函数。

掌握这些函数对于使用文档存储来说并不重要，但是在开发混合解决方案(在 SQL 语句中使用 JSON)时会有很大帮助。

这些功能可以根据它们的使用方式进行分类。我们将看到对添加数据有用的函数，检索(搜索)数据的函数，等等。下面用简单的例子说明如何使用这些功能。

表 6-1

MySQL 中常用的 JSON 函数

功能

描述和使用

创建 JSON 数据

创建 JSON 数据有几个有用的函数。我们已经看到了两个重要的函数:JSON_ARRAY()构建 JSON 数组类型，而JSON_OBJECT()构建 JSON 对象类型。本节讨论一些其他函数，这些函数可以用来帮助创建 JSON 文档，包括在 JSON 数组中聚合、追加和插入数据的函数。

JSON_ARRAYAGG()函数用于从几行中创建一个 JSON 文档数组。当您想要汇总数据或合并多行数据时，它会很有帮助。该函数接受一个列名，并将行中的 JSON 数据组合到一个新数组中。清单 6-2 展示了使用该函数的例子。这个示例获取表中的行，并将它们组合起来形成一个新的 JSON 对象数组。

> CREATE TABLE testdb_6.favorites (id int(11) NOT NULL AUTO_INCREMENT, preferences JSON, PRIMARY KEY (`id`));
> INSERT INTO testdb_6.favorites VALUES (NULL, '{"color": "red"}');
Query OK, 1 row affected (0.0077 sec)
> INSERT INTO testdb_6.favorites VALUES (NULL, '{"color": "blue"}');
Query OK, 1 row affected (0.0050 sec)
> INSERT INTO testdb_6.favorites VALUES (NULL, '{"color": "purple"}');
Query OK, 1 row affected (0.0034 sec)
> SELECT ∗ FROM testdb_6.favorites \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
         id: 1
preferences: {"color": "red"}
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 2\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
         id: 2
preferences: {"color": "blue"}
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 3\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
         id: 3
preferences: {"color": "purple"}
3 rows in set (0.0012 sec)
> SELECT JSON_ARRAYAGG(preferences) FROM testdb_6.favorites \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_ARRAYAGG(preferences): [{"color": "red"}, {"color": "blue"}, {"color": "purple"}]
1 row in set (0.0049 sec)

Listing 6-2Using the JSON_ARRAYARG Function

JSON_ARRAY_APPEND()是一个有趣的函数，它允许您将数据附加到 JSON 数组的末尾或紧接在给定路径表达式之后。该函数将 JSON 数组、路径表达式和要插入的值(包括 JSON 文档)作为参数。清单 6-3 展示了几个例子。

> SET @base = '["apple","pear",{"grape":"red"},"strawberry"]';
Query OK, 0 rows affected (0.0045 sec)
> SELECT JSON_ARRAY_APPEND(@base, '$', "banana") \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_ARRAY_APPEND(@base, '$', "banana"): ["apple", "pear", {"grape": "red"}, "strawberry", "banana"]
1 row in set (0.0009 sec)
> SELECT JSON_ARRAY_APPEND(@base, '$[2].grape', "green") \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_ARRAY_APPEND(@base, '$[2].grape', "green"): ["apple", "pear", {"grape": ["red", "green"]}, "strawberry"]
1 row in set (0.0012 sec)
> SET @base = '{"grape":"red"}';
Query OK, 0 rows affected (0.0004 sec)
> SELECT JSON_ARRAY_APPEND(@base, '$', '{"grape":"red"}') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_ARRAY_APPEND(@base, '$', '{"grape":"red"}'): [{"grape": "red"}, "{\"grape\":\"red\"}"]
1 row in set (0.0007 sec)

Listing 6-3Using the JSON_ARRAY_APPEND Function

请注意，第一个示例只是在数组末尾添加了一个新值。第二个示例将第三个索引中 JSON 对象的键值更改为一个数组，并添加一个新值。这是这个函数的一个有趣的副产品。在第三个例子中，我们再次看到了这一点，我们将一个基本的 JSON 对象更改为一个 JSON 对象的 JSON 数组。

JSON_ARRAY_INSERT()函数类似，只是它在路径表达式前插入值。该函数将 JSON 数组、路径表达式和要插入的值(包括 JSON 文档)作为参数。当包含多个路径表达式和值对时，当函数计算第一个路径表达式和值并将下一个对应用于结果时，效果是累积的，依此类推。清单 6-4 展示了一些使用新函数的例子，和前面的例子很相似。请注意，插入数据的位置在路径表达式之前。

> SET @base = '["apple","pear",{"grape":["red","green"]},"strawberry"]';
Query OK, 0 rows affected (0.0007 sec)
> SELECT JSON_ARRAY_INSERT(@base, '$[0]', "banana") \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_ARRAY_INSERT(@base, '$[0]', "banana"): ["banana", "apple", "pear", {"grape": ["red", "green"]}, "strawberry"]
1 row in set (0.0008 sec)
> SELECT JSON_ARRAY_INSERT(@base, '$[2].grape[0]', "white") \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_ARRAY_INSERT(@base, '$[2].grape[0]', "white"): ["apple", "pear", {"grape": ["white", "red", "green"]}, "strawberry"]
1 row in set (0.0009 sec)
> SET @base = '[{"grape":"red"}]';
Query OK, 0 rows affected (0.0004 sec)
> SELECT JSON_ARRAY_INSERT(@base, '$[0]', '{"grape":"red"}') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_ARRAY_INSERT(@base, '$[0]', '{"grape":"red"}'): ["{\"grape\":\"red\"}", {"grape": "red"}]
1 row in set (0.0007 sec)

Listing 6-4Using the JSON_ARRAY_INSERT Function

JSON_INSERT()函数被设计成获取一个 JSON 文档，并在指定的路径表达式中插入一个或多个值。也就是说，您可以一次传递成对的路径表达式和值。但是有一个问题。在这种情况下，路径表达式不能计算为文档中的元素。像上一个函数一样，当包含多个路径表达式时，效果是累积的，其中函数计算第一个路径表达式，将下一个路径表达式应用于结果，依此类推。清单 6-5 给出了一个例子。请注意，没有插入第三个路径表达式和值，因为路径表达式$[0]的计算结果是第一个元素 apple。

> SET @base = '["apple","pear",{"grape":["red","green"]},"strawberry"]';
Query OK, 0 rows affected (0.0007 sec)
> SELECT JSON_INSERT(@base, '$[9]', "banana", '$[2].grape[3]', "white", '$[0]', "orange") \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_INSERT(@base, '$[9]', "banana", '$[2].grape[3]', "white", '$[0]', "orange"): ["apple", "pear", {"grape": ["red", "green", "white"]}, "strawberry", "banana"]
1 row in set (0.0008 sec)

Listing 6-5Using the JSON_INSERT Function

JSON_MERGE_PATCH()和JSON_MERGE_PRESERVE()函数被设计成获取两个或更多的 JSON 文档并将它们组合起来。JSON_MERGE_PATH()功能替换重复键的值，而JSON_MERGE_PRESERVE()保留重复键的值。像最后一个函数一样，您可以包含任意多的 JSON 文档。注意我是如何使用这个函数从前面的例子中构建示例 JSON 文档的。清单 6-6 展示了一个使用这些方法的例子。

> SELECT JSON_MERGE_PATCH('["apple","pear"]', '{"grape":["red","green"]}', '["strawberry"]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_MERGE_PATCH('["apple","pear"]', '{"grape":["red","green"]}', '["strawberry"]'): ["strawberry"]
1 row in set (0.0041 sec)
> SELECT JSON_MERGE_PRESERVE('{"grape":["red","green"]}', '{"grape":["white"]}') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_MERGE_PRESERVE('{"grape":["red","green"]}', '{"grape":["white"]}'): {"grape": ["red", "green", "white"]}
1 row in set (0.0008 sec)

Listing 6-6Using the JSON_MERGE_PATCH and JSON_MERGE_PRESERVE Functions

如果向任何 JSON 函数传递了无效的参数、无效的 JSON 文档，或者路径表达式没有找到元素，则一些函数会返回 null，而其他函数可能会返回原始的 JSON 文档。清单 6-7 给出了一个例子。在这种情况下，位置 8 没有元素，因为数组只有 4 个元素。

> SET @base = '["apple","pear",{"grape":"red"},"strawberry"]' \G
Query OK, 0 rows affected (0.0007 sec)
> SELECT JSON_ARRAY_APPEND(@base, '$[7]', "flesh") \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_ARRAY_APPEND(@base, '$[7]', "flesh"): ["apple", "pear", {"grape": "red"}, "strawberry"]
1 row in set (0.0007 sec)

Listing 6-7Using the JSON_ARRAY_APPEND Function

现在让我们看看可以用来修改 JSON 数据的函数。

修改 JSON 数据

修改 JSON 数据有几个有用的函数。本节讨论了通过删除、替换和更新 JSON 文档中的元素来帮助修改 JSON 文档的函数。

JSON_REMOVE()函数用于删除匹配路径表达式的元素。您必须提供要操作的 JSON 文档以及一个或多个路径表达式，结果将是删除了元素的 JSON 文档。当包含多个路径表达式时，当函数计算第一个路径表达式并将下一个路径表达式应用于结果时，效果是累积的，依此类推。清单 6-8 给出了一个例子。注意，我必须想象中间结果是什么——也就是说，我使用了$[0]三次，因为该函数删除了第一个元素两次，留下 JSON 对象作为第一个元素。

 > SET @base = '["apple","pear",{"grape":["red","white"]},"strawberry"]';
Query OK, 0 rows affected (0.0008 sec)
> SELECT JSON_REMOVE(@base, '$[0]', '$[0]', '$[0].grape[1]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_REMOVE(@base, '$[0]', '$[0]', '$[0].grape[1]'): [{"grape": ["red"]}, "strawberry"]
1 row in set (0.0009 sec)

Listing 6-8Using the JSON_REMOVE Function (single)

这可能需要一点时间来适应，但您可以多次使用该函数或嵌套使用，如清单 6-9 中的示例所示。

 > SET @base = '["apple","pear",{"grape":["red","white"]},"strawberry"]';
Query OK, 0 rows affected (0.0007 sec)
> SET @base = JSON_REMOVE(@base, '$[0]');
Query OK, 0 rows affected (0.0009 sec)
> SET @base = JSON_REMOVE(@base, '$[0]');
Query OK, 0 rows affected (0.0006 sec)
> SELECT JSON_REMOVE(@base, '$[0].grape[1]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_REMOVE(@base, '$[0].grape[1]'): [{"grape": ["red"]}, "strawberry"]
1 row in set (0.0007 sec)
> SET @base = '["apple","pear",{"grape":["red","white"]},"strawberry"]';
Query OK, 0 rows affected (0.0004 sec)
> SELECT JSON_REMOVE(JSON_REMOVE(JSON_REMOVE(@base, '$[0]'), '$[0]'), '$[0].grape[1]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_REMOVE(JSON_REMOVE(JSON_REMOVE(@base, '$[0]'), '$[0]'), '$[0].grape[1]'): [{"grape": ["red"]}, "strawberry"]
1 row in set (0.0005 sec)

Listing 6-9Using the JSON_REMOVE Function (nested)

JSON_REPLACE()函数接受一个 JSON 文档和一对路径表达式和值，用新值替换匹配路径表达式的元素。同样，结果是累积的，并且从左到右按顺序工作。这个函数也有一个问题。它会忽略任何新值或评估为新值的路径表达式。清单 6-10 给出了一个例子。请注意，第三对没有被删除，因为没有第十个元素。

 > SET @base = '["apple","pear",{"grape":["red","white"]},"strawberry"]';
Query OK, 0 rows affected (0.0008 sec)
> SELECT JSON_REPLACE(@base, '$[0]', "orange", '$[2].grape[0]', "green", '$[9]', "waffles") \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_REPLACE(@base, '$[0]', "orange", '$[2].grape[0]', "green", '$[9]', "waffles"): ["orange", "pear", {"grape": ["green", "white"]}, "strawberry"]
1 row in set (0.0040 sec)

Listing 6-10Using the JSON_REPLACE Function

JSON_SET()函数用于修改 JSON 文档元素。像其他函数一样，您传递一个 JSON 文档作为第一个参数，然后传递一对或多对要替换的路径表达式和值。但是，该函数还会插入文档中不存在的任何元素(找不到路径表达式)。清单 6-11 给出了一个例子。注意，最后一个元素并不存在，所以它将它添加到文档中。

 > SET @base = '["apple","pear",{"grape":["red","white"]},"strawberry"]';
Query OK, 0 rows affected (0.0007 sec)
> SELECT JSON_SET(@base, '$[0]', "orange", '$[2].grape[1]', "green", '$[9]', "123") \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_SET(@base, '$[0]', "orange", '$[2].grape[1]', "green", '$[9]', "123"): ["orange", "pear", {"grape": ["red", "green"]}, "strawberry", "123"]
1 row in set (0.0009 sec)

Listing 6-11Using the JSON_SET Function

现在让我们看看可以用来在文档中查找元素的 JSON 函数。

搜索 JSON 数据

处理 SQL 和 JSON 数据的另一个重要操作是在 JSON 文档中搜索数据。在本章的前面，我们发现了如何用特殊符号(路径表达式)引用文档中的数据，我们还了解到可以使用 JSON 函数来搜索数据。事实上，我们在上一节中看到了这两个概念一起使用。在这一节中，我们将回顾 JSON 数据搜索机制，因为您可能会比其他任何函数更多地使用这些函数，尤其是在查询中。

有四个 JSON 函数允许您搜索 JSON 文档。与前面的函数一样，这些函数使用一个或多个参数对 JSON 文档进行操作。我称它们为搜索函数，不是因为它们允许您在数据库或表格中搜索 JSON 数据，而是因为它们允许您在 JSON 文档中查找东西。这些函数包括检查文档中是否存在值或元素、路径表达式是否有效(使用它可以找到一些东西)以及从文档中检索信息的函数。

JSON_CONTAINS()函数有两个选项:您可以使用它来返回一个值是否存在于文档中的任何地方，或者是否存在使用路径表达式的值(路径表达式是一个可选参数)。该函数返回 0 或 1，其中 0 表示未找到该值。如果文档参数不是有效的 JSON 文档，或者路径参数不是有效的路径表达式，或者包含∗或∗∗通配符，则会出现错误。还有一个问题。传入的值必须是有效的 JSON 字符串或文档。清单 6-12 展示了使用该函数搜索 JSON 文档的几个例子。

> SET @base = '{"grapes":["red","white","green"],"berries":["strawberry","raspberry","boysenberry","blackberry"]}';
Query OK, 0 rows affected (0.0007 sec)
> SELECT JSON_CONTAINS(@base,'["red","white","green"]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS(@base,'["red","white","green"]'): 0
1 row in set (0.0010 sec)
> SELECT JSON_CONTAINS(@base,'{"grapes":["red","white","green"]}') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS(@base,'{"grapes":["red","white","green"]}'): 1
1 row in set (0.0006 sec)
> SELECT JSON_CONTAINS(@base,'["red","white","green"]','$.grapes') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS(@base,'["red","white","green"]','$.grapes'): 1
1 row in set (0.0004 sec)
> SELECT JSON_CONTAINS(@base,'"blackberry"','$.berries') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS(@base,'"blackberry"','$.berries'): 0
1 row in set (0.0004 sec)
> SELECT JSON_CONTAINS(@base,'blackberry','$.berries') \G
ERROR: 3141: Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 0.
> SELECT JSON_CONTAINS(@base,'"red"','$.grapes') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS(@base,'"red"','$.grapes'): 1
1 row in set (0.0004 sec)

Listing 6-12Using the JSON_CONTAINS Function

正如你所看到的，这是一个非常有用的函数，但是要正确使用它需要一点小心。也就是说，您必须确保该值是有效的字符串。在所有的例子中，除了一个例子，我使用路径表达式在 JSON 文档中搜索 JSON 文档(这样可以更容易地搜索嵌套数据)或单个值。记住，函数搜索的是值，而不是键。

注意倒数第二个例子。这将返回一个错误，因为该值不是有效的 JSON 字符串。您必须用双引号将它括起来，如下例所示。

JSON_CONTAINS_PATH()函数使用的参数策略略有不同。该函数搜索 JSON 文档以查看路径表达式是否存在，但它也允许您查找第一个或所有的匹配项。它还可以采用多个路径，并根据您作为第二个参数传递的值，将它们作为“或”或“与”条件进行计算，如下所示。

如果您传递一个，如果至少找到一个路径表达式(或)，函数将返回 1。
如果传递 all，则只有找到所有路径表达式时，函数才会返回 1(和)。

该函数返回 0 或 1，以指示 JSON 文档在给定的一个或多个路径中是否包含数据。请注意，如果任何路径表达式或文档为 null，它可能会返回 null。如果 JSON 文档或任何路径表达式无效，或者第二个参数不是 one 或 all，则会发生错误。清单 6-13 展示了使用该函数的几个例子。

> SET @base = '{"grapes":["red","white","green"],"berries":["strawberry","raspberry","boysenberry","blackberry"],"numbers":["1","2","3","4","5"]}';
Query OK, 0 rows affected (0.0004 sec)
> SELECT JSON_CONTAINS_PATH(@base,'one','$') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS_PATH(@base,'one','$'): 1
1 row in set (0.0005 sec)
> SELECT JSON_CONTAINS_PATH(@base,'all','$') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS_PATH(@base,'all','$'): 1
1 row in set (0.0005 sec)
> SELECT JSON_CONTAINS_PATH(@base,'all','$.grapes','$.berries') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS_PATH(@base,'all','$.grapes','$.berries'): 1
1 row in set (0.0004 sec)
> SELECT JSON_CONTAINS_PATH(@base,'all','$.grapes','$.berries','$.numbers')\G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS_PATH(@base,'all','$.grapes','$.berries','$.numbers'): 1
1 row in set (0.0004 sec)
> SELECT JSON_CONTAINS_PATH(@base,'all','$.grapes','$.berries','$.num') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS_PATH(@base,'all','$.grapes','$.berries','$.num'): 0
1 row in set (0.0005 sec)
> SELECT JSON_CONTAINS_PATH(@base,'one','$.grapes','$.berries','$.num') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS_PATH(@base,'one','$.grapes','$.berries','$.num'): 1
1 row in set (0.0005 sec)
> SELECT JSON_CONTAINS_PATH(@base,'one','$.grapes') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS_PATH(@base,'one','$.grapes'): 1
1 row in set (0.0004 sec)
> SELECT JSON_CONTAINS_PATH(@base,'all','$.grape') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS_PATH(@base,'all','$.grape'): 0
1 row in set (0.0004 sec)
> SELECT JSON_CONTAINS_PATH(@base,'one','$.berries') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS_PATH(@base,'one','$.berries'): 1
1 row in set (0.0004 sec)
> SELECT JSON_CONTAINS_PATH(@base,'all','$.berries') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_CONTAINS_PATH(@base,'all','$.berries'): 1
1 row in set (0.0005 sec)

Listing 6-13Using the JSON_CONTAINS_PATH Function

花些时间浏览这些例子，这样你就能明白它们是如何工作的。注意在前两个例子中，我使用了一个美元符号的路径表达式。这只是整个文档的路径表达式，所以它自然存在。还要注意最后两个例子中使用 one 或 all 的区别。

JSON_EXTRACT()功能是最常用的功能之一。它允许你提取一个值或 JSON 数组或 JSON 对象等。使用一个或多个路径表达式从 JSON 文档中。我们已经看到了几个例子。Recall 函数返回 JSON 文档中与路径表达式匹配的部分。清单 6-14 展示了更多使用复杂路径表达式的例子。

> SET @base = '{"grapes":["red","white","green"],"berries":["strawberry","raspberry","boysenberry","blackberry"],"numbers":["1","2","3","4","5"]}';
Query OK, 0 rows affected (0.0004 sec)
> SELECT JSON_EXTRACT(@base,'$') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_EXTRACT(@base,'$'): {"grapes": ["red", "white", "green"], "berries": ["strawberry", "raspberry", "boysenberry", "blackberry"], "numbers": ["1", "2", "3", "4", "5"]}
1 row in set (0.0006 sec)
> SELECT JSON_EXTRACT(@base,'$.grapes') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_EXTRACT(@base,'$.grapes'): ["red", "white", "green"]
1 row in set (0.0005 sec)
> SELECT JSON_EXTRACT(@base,'$.grapes[∗]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_EXTRACT(@base,'$.grapes[∗]'): ["red", "white", "green"]
1 row in set (0.0005 sec)
> SELECT JSON_EXTRACT(@base,'$.grapes[1]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_EXTRACT(@base,'$.grapes[1]'): "white"
1 row in set (0.0005 sec)
> SELECT JSON_EXTRACT(@base,'$.grapes[4]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_EXTRACT(@base,'$.grapes[4]'): NULL
1 row in set (0.0006 sec)
> SELECT JSON_EXTRACT(@base,'$.berries') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_EXTRACT(@base,'$.berries'): ["strawberry", "raspberry", "boysenberry", "blackberry"]
1 row in set (0.0009 sec)
> SELECT JSON_EXTRACT(@base,'$.berries[2]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_EXTRACT(@base,'$.berries[2]'): "boysenberry"
1 row in set (0.0005 sec)
> SELECT JSON_EXTRACT(@base,'$.berries[2]','$.berries[3]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_EXTRACT(@base,'$.berries[2]','$.berries[3]'): ["boysenberry", "blackberry"]
1 row in set (0.0006 sec)

Listing 6-14Using the JSON_EXTRACT Function

注意当我们使用一元符号时会发生什么。该函数返回整个文档。另外，注意当我们使用一个路径表达式时会发生什么，尽管它的语法是有效的，但它并不等于文档中的一个元素(参见第五个例子)。

还要注意最后一个例子，我们传入了两个路径表达式。注意它是如何返回一个 JSON 数组的，而之前只有一个路径表达式的例子返回一个 JSON 字符串值。这是该函数的一个更棘手的方面。只要您记得它返回一个有效的 JSON 字符串、数组或对象，您就可以毫无问题地使用该函数。

JSON_SEARCH()函数很有趣，因为它与JSON_EXTRACT()函数相反。更具体地说，它接受一个或多个值，如果在文档中找到这些值，则返回这些值的路径表达式。这使得验证路径表达式或动态构建路径表达式变得更加容易。

像JSON_CONTAINS_PATH()函数一样，JSON_SEARCH()函数也允许您根据作为第二个参数传递的值来查找返回路径表达式的第一个或所有匹配项，如下所示。

如果传递一个，函数将返回第一个匹配。
如果通过 all，该函数将返回所有匹配项。

但是这里也有一个技巧。该函数接受第三个参数，该参数构成一个特殊的搜索字符串，其工作方式类似于 SQL 语句中的LIKE操作符。也就是说，搜索字符串参数可以像使用LIKE操作符一样使用%和_字符。注意，要将%或_用作文字，必须在它前面加上\(转义)字符。

该函数返回 0 或 1，以指示 JSON 文档是否包含这些值。请注意，如果任何路径表达式或文档为 null，它可能会返回 null。如果 JSON 文档或任何路径表达式无效，或者第二个参数不是 one 或 all，则会发生错误。清单 6-15 展示了使用该函数的几个例子。

> SET @base = '{"grapes":["red","white","green"],"berries":["strawberry","raspberry","boysenberry","blackberry"],"numbers":["1","2","3","4","5"]}';
Query OK, 0 rows affected (0.0011 sec)
> SELECT JSON_SEARCH(@base,'all','red') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_SEARCH(@base,'all','red'): "$.grapes[0]"
1 row in set (0.0006 sec)
> SELECT JSON_SEARCH(@base,'all','gr____') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_SEARCH(@base,'all','gr____'): NULL
1 row in set (0.0004 sec)
> SELECT JSON_SEARCH(@base,'one','%berry') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_SEARCH(@base,'one','%berry'): "$.berries[0]"
1 row in set (0.0005 sec)
> SELECT JSON_SEARCH(@base,'all','%berry') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_SEARCH(@base,'all','%berry'): ["$.berries[0]", "$.berries[1]", "$.berries[2]", "$.berries[3]"]
1 row in set (0.0006 sec)

Listing 6-15Using the JSON_SEARCH Function

现在我们来看最后一组 JSON 函数；这些工具本质上是实用的，允许您获得关于 JSON 文档的信息，并执行简单的操作来帮助处理 JSON 文档。

效用函数

最后，有几个函数可以返回关于 JSON 文档的信息，帮助添加或删除引号，甚至查找文档中的键。我们已经看到了几个实用程序JSON_TYPE()和JSON_VALID()函数。以下是在使用 JSON 文档时可能会发现有用的其他实用函数。

JSON_DEPTH()函数返回 JSON 文档的最大深度。如果文档是空数组、对象或标量值，则该函数返回深度 1。仅包含深度为 1 的元素的数组或仅包含深度为 1 的成员值的非空对象返回深度为 2 的值。清单 6-16 展示了几个例子。

> SELECT JSON_DEPTH('8') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_DEPTH('8'): 1
1 row in set (0.0017 sec)
> SELECT JSON_DEPTH('[]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_DEPTH('[]'): 1
1 row in set (0.0007 sec)
> SELECT JSON_DEPTH('{}') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_DEPTH('{}'): 1
1 row in set (0.0007 sec)
> SELECT JSON_DEPTH('[12,3,4,5,6]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_DEPTH('[12,3,4,5,6]'): 2
1 row in set (0.0008 sec)
> SELECT JSON_DEPTH('[[], {}]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_DEPTH('[[], {}]'): 2
1 row in set (0.0004 sec)
> SET @base = '{"grapes":["red","white","green"],"berries":["strawberry","raspberry","boysenberry","blackberry"],"numbers":["1","2","3","4","5"]}';
Query OK, 0 rows affected (0.0004 sec)
> SELECT JSON_DEPTH(@base) \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_DEPTH(@base): 3
1 row in set (0.0004 sec) 

> SELECT JSON_DEPTH(JSON_EXTRACT(@base, '$.grapes')) \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_DEPTH(JSON_EXTRACT(@base, '$.grapes')): 2
1 row in set (0.0005 sec)

Listing 6-16Using the JSON_DEPTH Function

JSON_KEYS()函数用于以 JSON 数组的形式从 JSON 对象的顶层值返回一个键列表。该函数还允许您传递路径表达式，这将产生所选路径表达式值的顶级键列表。如果json_doc参数不是有效的 JSON 文档，或者路径参数不是有效的路径表达式，或者包含∗或∗∗通配符，则会出现错误。如果所选对象为空，则结果数组为空。

有一个限制。如果顶层值有嵌套的 JSON 对象，则返回的数组不包括这些嵌套对象的键。清单 6-17 显示了使用该功能的几个例子。

> SET @base = '{"grapes":["red","white","green"],"berries":["strawberry","raspberry","boysenberry","blackberry"],"numbers":["1","2","3","4","5"]}';
Query OK, 0 rows affected (0.0004 sec)
> SELECT JSON_KEYS(@base) \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_KEYS(@base): ["grapes", "berries", "numbers"]
1 row in set (0.0039 sec)
> SELECT JSON_KEYS(@base,'$') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_KEYS(@base,'$'): ["grapes", "berries", "numbers"]
1 row in set (0.0005 sec)
> SELECT JSON_KEYS('{"z":123,"x":{"albedo":50}}') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_KEYS('{"z":123,"x":{"albedo":50}}'): ["x", "z"]
1 row in set (0.0004 sec)
> SELECT JSON_KEYS('{"z":123,"x":{"albedo":50}}', '$.x') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_KEYS('{"z":123,"x":{"albedo":50}}', '$.x'): ["albedo"]
1 row in set (0.0004 sec)

Listing 6-17Using the JSON_KEYS Function

JSON_LENGTH()函数返回传递的 JSON 文档的长度。它还允许您传入路径表达式，如果提供了路径表达式，将返回与路径表达式匹配的值的长度。如果json_doc参数不是有效的 JSON 文档，或者路径参数不是有效的路径表达式，或者包含*或**通配符，则会出现错误。但是，返回值有如下几个约束。

标量的长度为 1。
数组的长度等于数组元素的数量。
对象的长度等于对象成员的数量。

然而，有一个令人惊讶的限制:返回的长度不包括嵌套数组或对象的长度。因此，使用嵌套文档的路径表达式时，必须小心使用该函数。清单 6-18 显示了使用该函数的几个例子。

> SET @base = '{"grapes":["red","white","green"],"berries":["strawberry","raspberry","boysenberry","blackberry"],"numbers":["1","2","3","4","5"]}';
Query OK, 0 rows affected (0.0006 sec)
> SELECT JSON_LENGTH(@base,'$') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_LENGTH(@base,'$'): 3
1 row in set (0.0005 sec)
> SELECT JSON_LENGTH(@base,'$.grapes') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_LENGTH(@base,'$.grapes'): 3
1 row in set (0.0005 sec)
> SELECT JSON_LENGTH(@base,'$.grapes[1]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_LENGTH(@base,'$.grapes[1]'): 1
1 row in set (0.0005 sec)
> SELECT JSON_LENGTH(@base,'$.grapes[4]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_LENGTH(@base,'$.grapes[4]'): NULL
1 row in set (0.0004 sec)
> SELECT JSON_LENGTH(@base,'$.berries') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_LENGTH(@base,'$.berries'): 4
1 row in set (0.0004 sec)
> SELECT JSON_LENGTH(@base,'$.numbers') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_LENGTH(@base,'$.numbers'): 5
1 row in set (0.0005 sec)

Listing 6-18Using the JSON_LENGTH Function

请注意，第四个示例返回 null，因为路径表达式虽然是有效的语法，但并不等于值或嵌套的 JSON 数组或对象。

JSON_QUOTE()函数是一个方便的函数，可以帮助你在适当的地方添加引号。也就是说，该函数通过用双引号字符将字符串括起来并转义内部引号和其他字符来将字符串作为 JSON 字符串引用，并返回结果。注意，这个函数并不对 JSON 文档进行操作，而是只对一个字符串进行操作。

您可以使用这个函数生成一个有效的 JSON 字符串文字，以包含在 JSON 文档中。清单 6-19 展示了几个使用函数引用 JSON 字符串的简短例子。

> SELECT JSON_QUOTE("test") \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_QUOTE("test"): "test"
1 row in set (0.0012 sec)
> SELECT JSON_QUOTE('[true]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_QUOTE('[true]'): "[true]"
1 row in set (0.0007 sec)
> SELECT JSON_QUOTE('90125') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_QUOTE('90125'): "90125"
1 row in set (0.0008 sec)
> SELECT JSON_QUOTE('["red","white","green"]') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_QUOTE('["red","white","green"]'): "[\"red\",\"white\",\"green\"]"
1 row in set (0.0007 sec)

Listing 6-19Using the JSON_QUOTE Function

注意最后一个例子。这里，函数添加了转义符()，因为传递的字符串包含引号。为什么会这样？记住，这个函数接受一个字符串，而不是一个 JSON 数组作为参数。

JSON_UNQUOTE()功能与JSON_QUOTE()功能相反。JSON_UNQUOTE()函数删除引号中的 JSON 值，并将结果作为 utf8mb4 字符串返回。该函数旨在识别而不是改变标记序列，如下所示。

\":双引号(")字符
\b:退格字符
\f:换页符
\n:换行符
\r:回车符
\t:制表符
\\:反斜杠(\)字符

清单 6-20 显示了使用该函数的例子。

> SELECT JSON_UNQUOTE("test 123") \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_UNQUOTE("test 123"): test 123
1 row in set (0.0005 sec)
> SELECT JSON_UNQUOTE('"true"') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_UNQUOTE('"true"'): true
1 row in set (0.0007 sec)
> SELECT JSON_UNQUOTE('\"true\"') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_UNQUOTE('\"true\"'): true
1 row in set (0.0007 sec)
> SELECT JSON_UNQUOTE('9\t0\t125\\') \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_UNQUOTE('9\t0\t125\\'): 9  0       125\
1 row in set (0.0006 sec)

Listing 6-20Using the JSON_UNQUOTE Function

JSON_PRETTY()函数格式化 JSON 文档以便于查看。您可以用它来生成一个输出发送给用户，或者让 JSON 在 shell 中看起来更好一些。清单 6-21 显示了一个不带功能和带功能的例子。注意当使用JSON_PRETTY()时，阅读变得容易多了。

> SET @base = '{"name": {"last": "Throckmutton", "first": "Billy-bob"}, "address": {"zip": "90125", "city": "Melborne", "state": "California", "street": "4 Main Street"}}';
Query OK, 0 rows affected (0.0005 sec)
> SELECT @base \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
@base: {"name": {"last": "Throckmutton", "first": "Billy-bob"}, "address": {"zip": "90125", "city": "Melborne", "state": "California", "street": "4 Main Street"}}
1 row in set (0.0005 sec)
> SELECT JSON_PRETTY(@base) \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
JSON_PRETTY(@base): {
  "name": {
    "last": "Throckmutton",
    "first": "Billy-bob"
  },
  "address": {
    "zip": "90125",
    "city": "Melborne",
    "state": "California",
    "street": "4 Main Street"
  }
}
1 row in set (0.0004 sec)

Listing 6-21Using the JSON_PRETTY Function

然而，有一件事这个例子没有涉及到。如果 JSON 数据元素是一个字符串，您必须使用JSON_UNQUOTE()函数从字符串中删除引号。假设我们想要为颜色数据元素添加一个生成的列。如果我们添加带有ALTER TABLE语句的列和索引而不删除引号，我们将得到一些不寻常的结果，如清单 6-22 所示。

> CREATE TABLE `testdb_6`.`thermostats` (`model_number` char(20) NOT NULL,`manufacturer` char(30) DEFAULT NULL,`capabilities` json DEFAULT NULL,PRIMARY KEY (`model_number`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.0225 sec)
> INSERT INTO `testdb_6`.`thermostats` VALUES ('ODX-123','Genie','{"rpm": 3000, "color": "white", "modes": ["ac", "furnace"], "voltage": 220, "capability": "fan"}') \G
Query OK, 1 row affected (0.0037 sec)
> INSERT INTO `testdb_6`.`thermostats` VALUES ('AB-90125-C1', 'Jasper', '{"rpm": 1500, "color": "beige", "modes": ["ac"], "voltage": 110, "capability": "auto fan"}') \G
Query OK, 1 row affected (0.0041 sec)
> ALTER TABLE `testdb_6`.`thermostats` ADD COLUMN color char(20) GENERATED ALWAYS AS (capabilities->'$.color') VIRTUAL;
Query OK, 0 rows affected (0.0218 sec)
Records: 0  Duplicates: 0  Warnings: 0
> SELECT model_number, color FROM `testdb_6`.`thermostats` WHERE color = "beige" \G
Empty set (0.0006 sec)
> SELECT model_number, color FROM `testdb_6`.`thermostats` LIMIT 2 \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
model_number: AB-90125-C1
       color: "beige"
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 2\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
model_number: ODX-123
       color: "white"
2 rows in set (0.0006 sec)
> ALTER TABLE `testdb_6`.`thermostats` DROP COLUMN color;
Query OK, 0 rows affected (0.0206 sec)
Records: 0  Duplicates: 0  Warnings: 0
> ALTER TABLE `testdb_6`.`thermostats` ADD COLUMN color char(20) GENERATED ALWAYS AS (JSON_UNQUOTE(capabilities->'$.color')) VIRTUAL;
Query OK, 0 rows affected (0.0172 sec)
Records: 0  Duplicates: 0  Warnings: 0
> SELECT model_number, color FROM `testdb_6`.`thermostats` WHERE color = 'beige' LIMIT 1 \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
model_number: AB-90125-C1
       color: beige
1 row in set (0.0006 sec)

Listing 6-22Removing Quotes for Generated Columns on JSON Strings

注意，在第一个SELECT语句中，没有返回任何内容。这是因为虚拟生成的列使用了带引号的 JSON 字符串。当混合 SQL 和 JSON 数据时，这通常是混淆的来源。注意在第二个SELECT语句中，我们看到应该有几行被返回。还要注意，在我们删除该列并用JSON_UNQUOTE()函数再次添加它之后，SELECT返回正确的数据。

小费

有关使用 JSON 函数的更多信息，请参见在线 MySQL 参考手册( https://dev.mysql.com/doc/refman/8.0/en/json-functions.html )中的“JSON 函数”一节。

摘要

MySQL 增加了 JSON 数据类型，这为我们如何使用 MySQL 带来了一个范式转变。第一次，我们可以在关系数据(表)中存储半结构化数据。这不仅为我们提供了前所未有的灵活性，还意味着我们可以利用现代编程技术来访问我们应用中的数据，而无需付出巨大努力和增加复杂性。JSON 是一种众所周知的格式，在许多应用中广泛使用。

理解 JSON 数据类型是理解文档存储的关键。这是因为 JSON 数据类型虽然是为处理关系数据而设计的，但却形成了我们在文档存储中存储数据的模式——在 JSON 文档中！

在本章中，我们更详细地探讨了 JSON 数据类型。我们看到了如何通过 MySQL 中提供的大量内置 JSON 函数来处理关系表中的 JSON 数据的例子。

在下一章中，我们将通过 garage 示例应用并将其转换为文档存储来更详细地探索 MySQL 文档存储。也就是说，我们将看到如何通过使用 MySQL Shell 迁移数据和开发代码来构建带有 JSON 文档的 NoSQL 解决方案，从而将关系数据库解决方案迁移到文档存储中。

众所周知，关于如何格式化代码的辩论变成了关于间距的宗教狂热，但大多数人都同意不同意第一个花括号应该出现在哪里——在第一行单独出现，在同一行，还是在下一行。

有些人会说是的。

有聪明的方法可以避免这一点，但是对于这个论点，假设这是不可能的。

七、示例：文档存储开发

现在我们已经有了 JSON 的坚实基础，并且已经了解了如何使用 MySQL Shell 开发关系数据库代码，我们准备开始编写示例应用的 NoSQL 版本。

正如您将看到的，应用从纯关系模型到文档模型的演变演示了我们如何避免使用关系数据的一些混乱方面。可能会让您感到惊讶的一个因素是，文档存储(NoSQL)版本代码的复杂性降低，使代码更容易理解。还有什么更好的理由来考虑使用 MySQL 文档库编写未来的应用呢？

在上一章中，我们通过创建一个或多个 JSON 列，并使用 JSON 函数在 JSON 文档中创建、操作、搜索和访问数据，探索了如何在关系数据库中使用 JSON 数据。

在本章中，我们使用 MySQL 文档库将 garage 示例应用(关系数据库解决方案)迁移到一个纯 NoSQL 应用 ¹ 。我们可以通过 Python 使用许多 X DevAPI 特性来做到这一点——所有这些都是在 MySQL Shell 的帮助下开发的！让我们开始吧。

入门指南

我们没有把文档存储的解释留在最后一章，而是用例子来支持它，解释使用文档存储通过代码处理数据的新方法的好处。我们将以互动的方式演示这些概念，以便通过示例来证明这些概念，而不是简单地展示它是如何实现的。在本节中，我们将了解一个致力于此的示例应用——证明如何使用 shell 来开发自己的文档存储。

然而，为了以这种方式展示 shell 的全部功能，样本必须足够复杂，具有足够的深度(和宽度)来完成它的角色。因此，对于这一章，我们将使用第五章中的 garage 示例应用作为起点，并将其迁移到文档存储中。

回想一下，示例应用是一个工具，您可以用它来组织车库或车间中的工具。然而，与关系数据库实现不同，我们将对数据采取稍微不同的观点。我们开始吧。

示例应用概念

就像我们在第五章看到的示例应用一样，这个示例应用完全是关于组织的。在第五章中，数据是以大多数关系数据库专家使用的典型方式组织的——他们构建表格来保存具有相同布局的东西，或者简单地说，表格代表数据的主要类别。回想一下，我们有放手动工具、电动工具、储物空间等的桌子。–所有你能在车库或车间找到的东西。

对于 garage 应用的这个版本，我们将把我们的关注点从将相似的东西组织成表，转变为通过集合来组织东西。例如，工具箱包含工具，存储单元包含箱子、盒子、箱子等。这似乎是一个非常细微的差别(也许确实如此)，但它改变了数据的整个焦点。我们可以打开工具箱或查看搁架单元，看看那里存放了什么工具，而不是通过寻找工具来找到它的存放位置。通过这种方式，我们创造了一个更加用户友好的版本。现在，我们模仿大多数人寻找工具时的做法——一次打开一个抽屉(你知道你也会这么做)。

这是文档存储的优势之一——数据固有的灵活性允许您选择想要使用(或需要)的视图，并使数据及其访问层工作。这是一个挑战，一些关系数据库应用失败得很惨。 ²

此外，与关系数据库版本不同，我们为每个主要存储设备创建集合，并在表示存储设备的文档中维护每个设备(例如，工具)的内容列表。我们将所有工具归入一个集合，并通过文档 id 在存储设备集合中引用它们。同样，这听起来可能很奇怪，但是随着您的阅读，您将会看到它是如何工作的。

在此之前，让我们花点时间来看看这个版本的示例应用的修改后的用户界面。我们将这个应用命名为mygarage_v2。由于我们将模式设计的重点放在了存储设备上，因此我们在用户界面中的视图将从这个角度来看。我们保留了列表视图的概念，只是我们为每个存储设备使用了主要部分。图 7-1 显示了一个显示工具箱细节视图的新界面示例。

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

图 7-1

工具箱详细视图

注意

当使用文档存储或 NoSQL 模型时，我们使用“模式”,对于关系数据库模型，我们使用“数据库”。

请注意，这里我们看到了工具箱的详细信息，以及每个存放位置(抽屉、架子)和其中的工具列表。虽然我们可以对关系数据库版本采用类似的视图， ³ 但是文档存储使得编码变得相当容易。

小费

我们不会解释示例应用的每一个细微差别，而是将重点放在最适合用来证明使用 shell 开发代码的实用性的部分——模式集合代码模块。

与关系数据库版本不同，该版本背后的代码易于理解，并且在某些方面不太复杂。但是在我们看到模式设计之前，让我们在下面的列表中讨论一下这个版本的应用中使用的集合。这将帮助您理解与关系数据库版本(版本 1)的区别。

*橱柜:*带门的储物设备，可能有一个或多个搁板，用于存放各种工具和收纳件
*工具箱:*有零个或多个架子和一个或多个抽屉的存储设备——用于存储较小的工具
*工作台:*有一个或多个架子和零个或多个抽屉的存储设备——用于存储较大的工具
*搁架单元:*没有门和一个或多个搁架的存储设备——用于存储较大的箱子和类似的组织者
*组织者:*可以容纳一个或多个工具但需要存放在存储设备中的容器
*工具:*手动和电动工具
*供应商:*工具和设备的制造商

注意这里有一点词汇上的变化。在应用的第一个版本中，我们使用单数形式的表名。文档存储通常使用复数，因为每个集合通常包含多个项目(文档)。此外，当使用文档存储时，我们应该始终使用术语“模式”,而不是“数据库”。虽然有些人认为它们是同义词，但是 X DevAPI 做出了明确的区分，所以我们将采用相同的方法，使用术语“模式”。

你可能想知道我们怎样才能得到工具箱和工具之间的联系，就像前面提到的那样。这是通过简单地将工具的文档 id 和每个存储位置(重命名为工具位置)存储在工具箱文档中来实现的。

清单 7-1 是一个样本清单，可以帮助形象化地展示如何为organizers系列完成这项工作。正如您将看到的，我们使用 shell 连接到模式，然后获取集合，并按照文档 id 键值获取工具列表(名为tool_ids的数组)。这类似于在关系数据库中查找东西，但是在这种情况下，我们不必编写特殊的 SQL 命令(或者更糟，邪恶的 SQL 连接)来获取数据。稍后我们将更多地讨论那些_id字段。

> garage_v2 = my_session.get_schema('garage_v2')
> tc = garage_v2.get_collection('toolchests')
> tc.find().execute().fetch_one()
{
    "_id": "00005cc33db4000000000000025f",
    "depth": 22,
    "description": "Kobalt 3000 Steel Rolling Tool Cabinet (Black)",
    "height": 54,
    "location": "Rear wall right of workbench",
    "tool_locations": [
        "00005cc33db40000000000000260",
        "00005cc33db40000000000000261",
        "00005cc33db40000000000000268"
    ],
    "vendorid": "00005cc33db40000000000000130",
    "width": 48
}
> locs = garage_v2.get_collection('locations')
> locs.find('_id = :param1').bind('param1', '00005cc33db40000000000000260').execute().fetch_one()
{
    "_id": "00005cc33db40000000000000260",
    "depth": 17,
    "description": "Left 1",
    "height": 2,
    "tool_ids": [
        "00005cc33db40000000000000146",
        "00005cc33db40000000000000147",
        "00005cc33db40000000000000148",
        "00005cc33db40000000000000149",
        "00005cc33db4000000000000014a",
        "00005cc33db4000000000000015a"
    ],
    "type": "Drawer",
    "width": 21
}

Listing 7-1Sample Toolchest JSON Document

此时，您可能想知道 JSON 文档的无忧无虑的无格式规则功能发生了什么变化。简而言之，它仍然存在，但是我们的代码要求每个文档都有一组特定的属性。正如您将看到的，我们定义的属性将在代码中直接用来访问文档中的代码。

这并不排除使用可以随时添加的附加属性，但是它要求您的代码支持这样的更改。这就是以代码为中心的无模式嵌入式数据设计(或简单的代码驱动数据)的含义。随着应用的发展，我们可以使用我们的代码来扩充我们的文档(和集合)。

例如，如果我们将来需要添加一个新属性，我们可以添加代码来处理呈现的新数据，这必须包括如何处理没有新属性的文档，但也可能包括根据需要将属性添加到旧文档的代码。与需要修改表的关系数据库应用不同，关系数据库应用会迫使代码发生变化(可能是以不愉快的方式)，我们可以让文档存储的代码来实现这些变化。一切都归结于代码。

让我们进一步讨论模式设计以及如何将关系数据库迁移到文档存储中。

模式设计

您可能会认为我们可以使用 MySQL Workbench 这样的工具来创建模式和集合(您可以这样做),就像您创建关系数据库一样，但是您不需要这样做。您应该使用代码来执行创建事件。更具体地说，如果您要将文档存储的模式导入到 MySQL Workbench 这样的工具中，您不会看到太多感兴趣的内容。这是因为 SQL 模式下的 MySQL Shell(或 Workbench)将集合视为数据库。例如，清单 7-2 显示了这个版本的示例应用(garage_v2)的数据库(模式)的CREATE TABLE语句。

> EXPLAIN toolchests \G
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 1\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
  Field: doc
   Type: json
   Null: YES
    Key:
Default: NULL
  Extra:
∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗ 2\. row ∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗
  Field: _id
   Type: varbinary(32)
   Null: NO
    Key: PRI
Default: NULL
  Extra: STORED GENERATED
2 rows in set (0.0025 sec)

Listing 7-2Sample CREATE statement from SQL for garage_v2.

很明显，这是行不通的，它只不过是一种暂时的兴趣。请注意 _id 字段。这是文档 id。

当使用文档存储时，我们使用代码来创建模式和集合。回想一下，我们想要将模式命名为garage_v2并创建集合(cabinets、toolchests、workbenches、shelving units、organizers、tools和vendors)。我们还有一个名为locations的集合，它将各种类型的存储位置(称为工具位置)存储为一个文档，如架子、抽屉等。locations集合不能从用户界面的主菜单访问，因为焦点在存储设备集合上。但是，每个存储设备详细视图允许您修改该存储设备的工具位置，以完成该集合的创建、读取、更新和删除(CRUD)操作。

我们将使用 shell 和 X DevAPI 来创建这些对象，如清单 7-3 所示。注意，我们首先连接到服务器，请求会话，然后创建模式和集合。就这样——我们的模式完成了，我们有了文档存储的基础！

from getpass import getpass
try:
    input = raw_input
except NameError:
    pass
# Get user id and password
userid = input("User Id: ")
passwd = getpass("Password: ")

user_info = {
    'host': 'localhost',
    'port': 33060,
    'user': userid,
    'password': passwd,
}
# Connect to the database garage_v1
my_session = mysqlx.get_session(user_info)
# Create the schema for garage_v2
my_session.drop_schema('garage_v2')
garage_v2 = my_session.create_schema('garage_v2')
# Create the collections
cabinets = garage_v2.create_collection('cabinets')
organizers = garage_v2.create_collection('organizers')
shelving_units = garage_v2.create_collection('shelving_units')
tools = garage_v2.create_collection('tools')
toolchests = garage_v2.create_collection('toolchests')
locations = garage_v2.create_collection('locations')
workbenches = garage_v2.create_collection('workbenches')
vendors = garage_v2.create_collection('vendors')
# Show the collections
print(garage_v2.get_collections())

Listing 7-3Creating the Document Store

当您运行代码时，您应该会看到如下所示的输出。

> mysqlsh --py -f listing7-3.py
User Id: root
Password: ∗∗∗∗∗∗∗∗∗∗∗
[<Collection:cabinets>, <Collection:locations>, <Collection:organizers>, <Collection:shelving_units>, <Collection:toolchests>, <Collection:tools>, <Collection:vendors>, <Collection:workbenches>]

这难道不比苦读一堆 SQL CREATE语句容易得多吗？如果你想从头开始创建模式和集合，你可以在你的机器上运行这个，但是如果你把我们在第五章中看到的数据从一个关系数据库转换到一个文档存储，你可能想按照设置和配置部分中的例子转换。

在此之前，让我们再来谈谈这些系列。这一次，我们将看到每个集合的样本文档。

注意

我们使用“工具位置”来代替版本 1 中“存储位置”的概念，以便区分和更好地描述 JSON 文档中的属性。

橱柜系列

cabinets集合存储描述橱柜的文档，橱柜可以包含一个或多个搁板和一个或多个门。因此，我们希望存储集合中每个橱柜的信息，包括它的物理大小和位置，以及它包含的工具位置(架子)。清单 7-4 展示了这个集合中的一个 JSON 文档示例。

> garage_v2 = my_session.get_schema('garage_v2')
> cabinets = garage_v2.get_collection('cabinets')
> cabinets.find().execute().fetch_one()
{
    "_id": "00005cae74150000000000000161",
    "depth": 24,
    "description": "Kobalt Steel Freestanding Garage Cabinet",
    "height": 72,
    "location": "Right wall",
    "numdoors": 2,
    "tool_locations": [
        "00005cae74150000000000000162",
        "00005cae74150000000000000163",
        "00005cae74150000000000000164",
        "00005cae74150000000000000165",
        "00005cae74150000000000000166"
    ],
    "vendorid": "00005cae74150000000000000001",
    "width": 48
}

Listing 7-4Cabinets Collection Example JSON Document

注意我们有一个名为_id的属性。当我们创建文档时，我们没有指定这个属性，如果您没有指定，MySQL 将为您创建一个唯一的值。这称为文档 id。如果需要，您可以为_id指定自己的值，但是通常不鼓励这样做，因为内部机制将确保文档是惟一的。把它想象成一个主键。 ⁴ 有关文档 id 的更多信息，请参见 https://dev.mysql.com/doc/x-devapi-userguide/en/working-with-document-ids.html 。

我们还为描述、大小(深度、高度、宽度)、物理位置、门的数量和工具位置的数组添加了属性。最后，注意我们有一个包含供应商文档 id 的属性。请注意，tool_locations数组没有施加任何限制，比如只允许使用盘架。这是因为在文档存储中，这些类型的约束被添加到代码中。文档存储只是存储文档。

注意

在 JSON 文档中，我们对集合和属性使用小写名称。这不是绝对必要的，但确实遵循一种熟悉的模式。

位置集合

locations集合存储描述工具存储位置的文档，如架子或抽屉。也就是说，我们希望存储关于集合中每个位置的信息，包括它的物理大小和位置，以及它包含的工具位置(架子或抽屉)。清单 7-5 展示了这个集合中的一个示例 JSON 文档。

> garage_v2 = my_session.get_schema('garage_v2')
> locations = garage_v2.get_collection('locations')
> locations.find().execute().fetch_one()
{
    "_id": "00005cae74150000000000000146",
    "depth": 17,
    "description": "Left 1",
    "height": 2,
    "tool_ids": [
        "00005cae74150000000000000141",
        "00005cae74150000000000000142",
        "00005cae74150000000000000139"
    ],
    "type": "Drawer",
    "width": 21
}

Listing 7-5Locations Collection Example JSON Document

注意，我们有一个工具 id 数组，它是存储在这个位置的每个工具的文档 id。还要注意，我们有一个 type 属性来存储刀具位置的类型。对这些值的控制也被转移到代码中，这允许您通过代码来更改允许的类型，而不是修改底层数据存储，这可能会在开发和发布期间造成额外的麻烦。

等等，为什么不把工具放在工具箱里？

一些文档存储开发人员可能会告诉您，使用单独的集合并通过 id 引用文档是不好的。他们会说，“只需将所有工具作为一个数组转储到存储设备集合中，不要弄乱位置集合。”这是一种有效的设计选择，但是数据中的一些严格性(例如以这种方式提取刀具位置到刀具的映射)同样有效，并且在某些情况下可能会使数据的转换更容易完成和可视化。我们将在本章后面看到这样一个例子。是的，JSON 可以让你把所有的东西放在一起，但是在数据组织上有一点严格性并不能验证 NoSQL 的目标。不要被愚弄，认为没有严谨就等同于更好的 NoSQL 设计。

组织者收藏

organizers集合存储描述组织者的文档，如箱子、盒子、盒子等。，它可以包含一个或多个工具。因此，我们希望存储集合中每个组织者的信息，包括其物理大小和位置，以及它包含的工具。清单 7-6 展示了这个集合中的一个示例 JSON 文档。

> garage_v2 = my_session.get_schema('garage_v2')
> organizers = garage_v2.get_collection('organizers')
> organizers.find().execute().fetch_one()
{
    "_id": "00005cae7415000000000000013b",
    "depth": 14,
    "description": "SAE/Metric Socket Set",
    "height": 4,
    "tool_ids": [
        "00005cae741500000000000000b2",
        "00005cae741500000000000000b9",
        "00005cae741500000000000000c7"
    ],
    "type": "Case",
    "width": 12
}

Listing 7-6Organizers Collection Example JSON Document

像 locations 集合一样，我们也有一个在代码中控制的 type 属性。除此之外，我们还添加了描述其物理大小和描述的属性。还有一个工具 id 数组。这就是我们所需要的。

对于示例应用，这里有一点需要注意。因为组织器可以很小，所以它们可以放置在工具位置。因此，应用需要额外的代码来处理查找，以区分组织者和工具。这是在应用中考虑灵活性的一个例子。关系数据库设计永远不会允许这样做，因为类型(组织者与工具位置)是不同的。因为我们用代码控制数据接口，所以我们需要做的就是编写代码来处理这种情况！我们将在下一节的代码设计中看到更多。

搁架单元系列

shelving_units集合存储描述搁架单元的文档，搁架单元可以包含一个或多个架子。因此，我们希望存储关于集合中每个搁架单元的信息，包括它的物理大小和位置，以及它包含的工具位置(搁架)。清单 7-7 展示了这个集合中的一个 JSON 文档示例。

> garage_v2 = my_session.get_schema('garage_v2')
> shelving_units = garage_v2.get_collection('shelving_units')
> shelving_units.find().execute().fetch_one()
{
    "_id": "00005cae7415000000000000014f",
    "depth": 24,
    "description": "Wire shelving #1",
    "height": 72,
    "location": "Right wall",
    "tool_locations": [
        "00005cae74150000000000000150",
        "00005cae74150000000000000152",
        "00005cae74150000000000000153"
    ],
    "vendorid": "00005cae74150000000000000015",
    "width": 48
}

Listing 7-7Shelving Units Collection Example JSON Document

你开始看到这里的趋势了吗？是的，存储设备系列具有非常相似的属性。这是有意的。为什么呢？因此，我们可以将文档隔离到离散的集合中，使集合变浅(更少的文档)。诚然，这将导致一个非常小的性能问题，但使用。随着应用的成熟或发展，它还允许您更改每个集合中的属性集。

例如，如果添加一种新型的搁架单元，它有门、抽屉、工具架等等，您可以更改代码来处理新添加的内容，而无需重做集合中的任何文档，也无需对其他集合强制进行更改。

回想一下，在关系数据库版本中，我们有一个用于存储门数量的字段，并不是所有存储设备类型都需要它。JSON 文档是这个 SQL 难题的答案。

工具箱系列

toolchests集合存储描述工具箱的文档，工具箱可以包含零个或多个架子和一个或多个抽屉。因此，我们希望存储集合中每个工具箱的信息，包括它的物理大小和位置，以及它包含的工具位置(架子和抽屉)。清单 7-8 展示了这个集合中的一个 JSON 文档示例。

> garage_v2 = my_session.get_schema('garage_v2')
> toolchests = garage_v2.get_collection('toolchests')
> toolchests.find().execute().fetch_one()
{
    "_id": "00005cae74150000000000000145",
    "depth": 22,
    "description": "Kobalt 3000 Steel Rolling Tool Cabinet (Black)",
    "height": 54,
    "location": "Rear wall right of workbench",
    "tool_locations": [
        "00005cae74150000000000000146",
        "00005cae74150000000000000147",
        "00005cae7415000000000000014e"
    ],
    "vendorid": "00005cae74150000000000000001",
    "width": 48
}

Listing 7-8Toolchests Collection Example JSON Document

工具集合

tools集合存储描述工具的文档。我们将包括所有工具；不仅仅是手动工具或电动工具。我们在文档中存储了每个工具的特性，如描述、类别、大小等。清单 7-9 展示了这个集合中的一个示例 JSON 文档。

> garage_v2 = my_session.get_schema('garage_v2')
> tools = garage_v2.get_collection('tools')
> tools.find().execute().fetch_one()
{
    "_id": "00005cae74150000000000000024",
    "category": "Handtool",
    "description": "1/8-in X 1-1/2-in",
    "size": "Slotted",
    "type": "Screwdriver",
    "vendorid": "00005cae74150000000000000002"
}

Listing 7-9Tools Collection Example JSON Document

关于这个系列有一点需要注意。回想一下，我们在版本 1 的关系数据库中有两个表，因为一些字段有惟一的枚举值。因为 JSON 允许我们存储我们想要的属性，所以这个集合中的文档可能有也可能没有一个或多个属性。例如，并非所有工具都有大小属性。像其他集合一样，类别和类型的值在代码中处理。

供应商集合

vendors集合存储描述供应商的文档。我们在版本 1 的关系数据库中存储了相同的特征，包括名称、来源和 URL。清单 7-10 展示了这个集合中的一个示例 JSON 文档。

> garage_v2 = my_session.get_schema('garage_v2')
> vendors = garage_v2.get_collection('vendors')
 MySQL  Py > vendors.find().execute().fetch_one()
{
    "_id": "00005cae74150000000000000001",
    "name": "Kobalt",
    "sources": "Lowes",
    "url": "https://www.kobalttools.com/"
}

Listing 7-10Vendors Collection Example JSON Document

工作台集合

workbenches集合存储描述工作台的文档，工作台可以包含零个或多个货架。因此，我们希望存储集合中每个工作台的信息，包括它的物理大小和位置，以及它包含的工具位置(架子)。清单 7-11 展示了这个集合中的一个 JSON 文档示例。

> garage_v2 = my_session.get_schema('garage_v2')
> workbenches = garage_v2.get_collection('workbenches')
> workbenches.find().execute().fetch_one()
{
    "_id": "00005cae74150000000000000167",
    "depth": 24,
    "description": "Large metal workbench",
    "height": 42,
    "location": "Rear wall",
    "tool_locations": [
        "00005cae74150000000000000168",
        "00005cae74150000000000000169",
        "00005cae7415000000000000016a"
    ],
    "vendorid": "00005cae74150000000000000002",
    "width": 72
}

Listing 7-11Workbenches Collection Example JSON Document

现在我们已经看到了集合是如何形成的，让我们看看如何使用 shell 来编写实现集合的 CRUD 操作所需的代码。

代码设计

示例应用的代码与版本 1 中的代码非常相似；这是一个 Python Flask 应用，具有组织代码模块的相同选择。与版本 1 一样，您可能会发现有些事情您会做得不同，但出于演示的目的，它仍然可以以当前的形式使用。为示例应用做出的代码设计选择包括以下内容。

将 Flask 框架用于基于 web 的界面。
使用一个类来表示数据库中的每个表。
将单个类放在它自己的代码模块中。
将所有模式代码模块放在它自己的文件夹(名为 schema)中。
使用类封装到数据库服务器的连接。
使用类模块测试每个表/视图类。
使用从 shell 运行的代码模块来测试类模块。

我们将在演示中看到这些约束中的大部分。如前所述，用户界面的描述包含在附录中。

幸运的是，我们已经通过使用文档存储使编写代码变得稍微容易了一些。这是因为集合允许您指定属性，所以除了为有效值列表(关系数据库版本中的枚举列)添加约束和检查错误之外，创建、读取、更新和删除(CRUD)的代码是相同的。因此，我们将使用带有基类的对象层次结构，该基类包含 CRUD 操作的主要代码，并允许子类添加特定于每个集合的约束代码。

让我们花点时间列出我们将使用的代码模块。在这个示例应用中，我们将创建一个名为 schema 的文件夹，并将以下模块放在该文件夹中。然后，我们可以根据需要在应用代码中导入它们。表 7-1 显示了所需的代码模块。

表 7-1

模式代码模块

代码模块

类别名

描述

当我们为示例应用编写代码以使用这些代码模块时，我们将使用MyGarage类连接到数据库服务器，并且当被请求时，使用与每个集合相关联的类来调用每个集合上的 CRUD 操作。

现在，我们已经了解了示例应用及其设计的目标，让我们开始编写示例应用的模式代码。

设置和配置

以下演示的设置不需要安装任何东西，甚至不需要使用示例应用，相反，我们只需要加载示例模式，因为我们将只使用模式代码模块。虽然图像用于描述示例应用的某些方面，但在本章中并不一定需要它。同样，关于如何设置和使用完整的示例应用，请参见附录。

由于我们在第五章中使用了来自示例应用的相同数据，我们将看看如何将关系数据库数据转换成文档存储。之后，我们将看到如何为基类和集合类编写代码。

将关系数据转换为文档存储

我们可以将关系数据库转换成文档存储，而不是从头开始。如果您还没有运行第五章中的示例应用，并且您想了解转换是如何工作的，那么您可以首先运行本书源代码中的Ch05/mygarage/database/garage_v1.sql文件，然后跟随我们了解如何将数据库表转换为集合以及将数据转换为文档。即使您不打算使用示例应用，本节也可以帮助您将来将其他数据库转换为文档存储。

我们将采取逐步的方法来形成一个脚本来转换数据。这是因为有几个挑战会使转换代码变得有点棘手。幸运的是，当我们检查转换数据所必需的代码时，我们将遍历每一个。从关系数据库转换到文档存储的挑战在于如何将数据传递到新的集合中。这意味着在尝试转换之前，您必须计划您的收藏以及您希望如何使用它们。否则，随着模式设计的成熟，您可能会发现自己需要重新编写转换代码。 ⁵

将示例应用(版本 1)转换为文档存储的挑战包括以下内容。

我们不能使用自动增量值。因此，我们需要创建一个从旧的自动增量值到新的文档 id 的映射。
我们将手动工具和电动工具合并为一个系列。
我们将存储表分割成单独的集合。
我们必须遍历位置表，并在新的集合中保留工具位置。

让我们从第一步开始，即转换代码的前同步码或设置。如果您计划将您自己的关系数据库转换为文档存储，您可以使用一组类似的步骤来编写您自己的转换代码，但是您可能不需要所有的步骤。

步骤 1:转换设置代码

这一步很简单。我们只需导入我们需要的模块，然后连接到服务器，获得一个garage_v1数据库的实例，并创建garage_v2模式和集合，就像我们之前在清单 7-3 中所做的那样。清单 7-12 显示了设置转换脚本的代码。

import json
from getpass import getpass
try:
    input = raw_input
except NameError:
    pass

try:
    import mysqlx
except Exception:
    from mysqlsh import mysqlx

# Get user id and password
userid = input("User Id: ")
passwd = getpass("Password: ")

user_info = {
    'host': 'localhost',
    'port': 33060,
    'user': userid,
    'password': passwd,
}

# Connect to the database garage_v1
my_session = mysqlx.get_session(user_info)
garage_v1 = my_session.get_schema('garage_v1')
# Get the tables
handtool_tbl = garage_v1.get_table('handtool')
organizer_tbl = garage_v1.get_table('organizer')
place_tbl = garage_v1.get_table('place')
powertool_tbl = garage_v1.get_table('powertool')
storage_tbl = garage_v1.get_table('storage')
vendor_tbl = garage_v1.get_table('vendor')

# Create the schema for garage_v2
my_session.drop_schema('garage_v2')
garage_v2 = my_session.create_schema('garage_v2')
# Create the collections
cabinets = garage_v2.create_collection('cabinets')
organizers = garage_v2.create_collection('organizers')
shelving_units = garage_v2.create_collection('shelving_units')
tools = garage_v2.create_collection('tools')
toolchests = garage_v2.create_collection('toolchests')
locations = garage_v2.create_collection('locations')
workbenches = garage_v2.create_collection('workbenches')
vendors = garage_v2.create_collection('vendors')

Listing 7-12Conversion Setup Code

步骤 2:助手函数

下一步需要一些解释。它接下来出现在脚本中(但也可以放在代码的前面)。在这一步中，我们创建了几个助手函数，用于处理数据库表，并重建原始表和新文档 id 映射之间的链接。表 7-2 列出了新的助手功能及其描述。我们也将看到函数的代码。

表 7-2

转换脚本的帮助函数

名字

因素

描述

get_∗函数全部用于查询关系数据库表，以找到匹配自动增量值的行，并获得电动工具和手工工具的自动增量 id 列表。这些用来获取一行，这样就可以将它们转换成 JSON 文档。find_∗函数用于在集合中搜索与工具 id 匹配的 JSON 文档，这样我们就可以填充 locations 集合。

此外，为了将工具存储位置与工具相关联，我们需要一种收集工具的方法。我们可以在文档中存储一个工具 id 数组，而不是创建一个引用、连接或查找表。因此，我们以这种方式创建链接，使其更加直观——我们打开一个工具箱，想看看里面有什么。

现在让我们看看这些函数的代码。清单 7-13 显示了函数的代码。我们不解释它们各自做什么，而是给出代码，并在后面的上下文中讨论它们。如果你看不到它们是如何工作的或者为什么要写它们，不要担心；当你看到它们在上下文中使用时，它们会更有意义。

# Display the documents in a collection
def show_collection(col_object):
    print("\nCOLLECTION: {0}".format(col_object.name))
    results = col_object.find().execute().fetch_all()
    for document in results:
        print(json.dumps(json.loads(str(document)),
              indent=4, sort_keys=True))

# Get the storage places that match this storage id
def get_places(storage_id):
    return place_tbl.select('Type', 'Description', 'Width', 'Depth',
                            'Height', 'Id')\
                    .where("StorageId = {0}".format(storage_id)).execute()

# Get the list of organizer ids at the storage place
def get_organizer_ids(place_id):
    organizer_ids = []
    org_results = organizer_tbl.select('Id')\
                     .where("PlaceId = {0}".format(place_id)).execute()
    for org in org_results.fetch_all():
        organizer_ids.append(get_mapping(org[0], organizer_place_map)[0])
    return organizer_ids

# Get the list of handtool ids at the storage place
def get_handtool_ids(place_id):
    handtool_ids = []
    ht_results = handtool_tbl.select('Id')\
                 .where("PlaceId = {0}".format(place_id)).execute()
    for ht in ht_results.fetch_all():
        handtool_ids.append(ht[0])
    return handtool_ids

# Get the list of powertool ids at the storage place
def get_powertool_ids(place_id):
    powertool_ids = []
    pt_results = powertool_tbl.select('Id')\
                 .where("PlaceId = {0}".format(place_id)).execute()
    for pt in pt_results.fetch_all():
        powertool_ids.append(pt[0])
    return powertool_ids

# Get the new docid for the old vendor id
def get_mapping(old_id, mapping):
    for item in mapping:
        if item[0] == old_id:
            return item
    return None

# Search the organizers collection for the tool
def find_tool_in_organizers(tool_id):
    # organizers contain no shelves or drawers so fetch only the tool ids
    organizers = garage_v2.get_collection('organizers')
    results = organizers.find().fields("_id", "tool_ids", "type",
                                       "description").execute().fetch_all()
    for result in results:
        if (result["tool_ids"]) and (tool_id in result["tool_ids"]):
            return ("{0}, {1}".format(result["type"], result["description"]),
                                      'organizers', result["_id"])
    return None

# Search for a tool in a given collection
def find_tool(collection_name, tool_id):
    collection = garage_v2.get_collection(collection_name)
    storage_places = collection.find()\
      .fields("_id", "description", "tool_locations").execute().fetch_all()
    for storage_place in storage_places:
        if storage_place["tool_locations"]:
            for location in storage_place["tool_locations"]:
                loc_data = locations.find('_id = :param1') \
                                    .bind('param1',
                                          location).execute().fetch_all()
                if loc_data:
                    loc_dict = dict(loc_data[0])
                    tool_ids = loc_dict.get("tool_ids", [])
                    if tool_id in tool_ids:
                        return ("{0}, {1} - {2}"
                                "".format(storage_place["description"],
                                          loc_dict["description"],
                                          loc_dict["type"]),
                                          collection_name,
                                          storage_place["_id"])
    return None

# Find the location document id for a tool.
def get_tool_location(tool_id):
    loc_found = find_tool_in_organizers(tool_id)
    if loc_found:
        return loc_found
    storage_collections = [
        'toolchests', 'shelving_units', 'workbenches', 'cabinets'
    ]
    for storage_collection in storage_collections:
        loc_found = find_tool(storage_collection, tool_id)
        if loc_found:
            return loc_found
    return None

Listing 7-13
Helper Functions

步骤 3:填充集合

下一步是填充集合。我们可以用数据填充集合，但是必须按照特定的顺序。例如，代表存储设备和工具的集合中的每个文档都是供应商的文档 id。因此，我们需要首先执行vendors集合，从表中创建旧 id 列到 vendors 集合中新文档 id 的映射。让我们看看如何做到这一点。清单 7-14 显示了将vendor表转换为vendors集合的代码。我们将更详细地研究这段代码，因为它构成了处理其他表和集合的模板。

# Get the vendors
my_results = vendor_tbl.select('Id', 'Name', 'URL', 'Sources').execute()
vendor_id_map = []
for v_row in my_results.fetch_all():
    new_item = {
        'name': v_row[1],
        'url': v_row[2],
        'sources': v_row[3]
    }
    last_docid = vendors.add(new_item).execute().get_generated_ids()[0]
    vendor_id_map.append((v_row[0], last_docid))
show_collection(vendors)

Listing 7-14Populate the Vendors Collection

注意这里我们打开了vendor表并读取了所有数据。然后，我们创建一个空映射(数组),我们将使用它来记录从表到集合中新文档 id 的自动增量 id。这将允许我们将其他表中供应商列的自动增量 id 替换为集合中供应商的新文档 id。这是在转换过程中保留关系链接的一种很好的方式。

我们使用一个循环来读取查询的结果，并使用表行中的数据形成一个包含属性(小写)的字典。然后我们使用vendors集合来添加供应商文档。

我们可以将add()方法与get_generated_ids()调用链接起来，以获得生成的最后一个文档 id。然后我们将它添加到名为vendor_id_map的新映射中，稍后我们将使用它在其他集合的其他文档中插入供应商的正确文档 id。

为了帮助可视化结果，我们使用show_collection()函数来打印集合的内容。

我们转换的下一个集合是tools集合。回想一下，我们将把handtool和powertool表合并到工具集合中。因此，我们必须读取这些表，并将它们插入到tools集合中。清单 7-15 显示了这个转换的代码。花点时间熟悉一下代码。

# Get the tools combining the handtool and powertool tables
ht_results = handtool_tbl.select('Id', 'VendorId', 'Description', 'Type', 'Toolsize', 'PlaceId').execute()
tool_place_map = []
for ht_row in ht_results.fetch_all():
    new_item = {
        'category': 'Handtool',
        'vendorid': get_mapping(ht_row[1], vendor_id_map)[1],
        'description': ht_row[2],
        'type': ht_row[3],
        'size': ht_row[4],
    }
    last_docid = tools.add(new_item).execute().get_generated_ids()[0]
    tool_place_map.append((ht_row[0], last_docid))
pt_results = powertool_tbl.select('Id', 'VendorId', 'Description', 'Type', 'PlaceId').execute()
for pt_row in pt_results.fetch_all():
    new_item = {
        'category': 'Powertool',
        'vendorid': get_mapping(pt_row[1], vendor_id_map)[1],
        'description': pt_row[2],
        'type': pt_row[3],
    }
    last_docid = tools.add(new_item).execute().get_generated_ids()[0]
    tool_place_map.append((pt_row[0], last_docid))
show_collection(tools)

Listing 7-15Populate the Tools Collection

正如您所看到的，这段代码遵循与前面的代码相同的模式，为工具的自动增量 id 到生成的新文档 id 创建一个映射。请注意，电动工具没有大小属性，但手动工具有。因此，我们为手动工具而不是电动工具添加了该属性。这以一种小的方式展示了我们如何在同一个集合中使用具有不同属性(键)的文档。

为了帮助可视化结果，我们使用show_collection()函数来打印集合的内容。

我们转换的下一个集合是organizers集合。像以前一样，我们只是读取表中的行，并将它们插入到集合中。清单 7-16 显示了将organizer表转换为organizers集合的代码。

# Get organizers
org_results = organizer_tbl.select('Id', 'Description', 'Type', 'Width', 'Depth', 'Height', 'PlaceId').execute()
organizer_place_map = []
for org_row in org_results.fetch_all():
    tool_ids = get_handtool_ids(org_row[0])
    tool_ids.extend(get_powertool_ids(org_row[0]))
    tool_docids = [get_mapping(item, tool_place_map)[1] for item in tool_ids]
    new_item = {
        'description': org_row[1],
        'type': org_row[2],
        'width': org_row[3],
        'depth': org_row[4],
        'height': org_row[5],
    }
    if tool_docids:
        new_item.update({'tool_ids': tool_docids})
    last_docid = organizers.add(new_item).execute().get_generated_ids()[0]
    # We also need to save the mapping of organizers to storage places
    organizer_place_map.append((org_row[0], last_docid))
show_collection(organizers)

Listing 7-16Populate the Organizers Collection

虽然这段代码也遵循与前面相同的模式，但是我们创建了组织者 id 到新文档 id 的映射。然而，由于数据库中的 organizer 表通过place表引用了工具，我们使用 helper 函数从表中检索与该 organizer 匹配的工具 id。然后，我们构建一个工具 id 数组，并将其存储在属性tool_ids中。花点时间看看这是如何工作的。

为了帮助可视化结果，我们使用show_collection()函数来打印集合的内容。

我们转换的下一个集合是toolchests集合。这是我们将从存储表中分离出来的第一个集合，为每个存储设备创建一个单独的集合。由于我们在storage表中有不止一个存储设备，我们将把结果限制在那些type设置为toolchest的设备上。像以前一样，我们只是读取表中的行，并将它们插入到集合中。清单 7-17 显示了将storage表转换成toolchests集合的代码。

# Get the toolchests
tc_results = storage_tbl.select('Id', 'VendorId', 'Description', 'Width', 'Depth', 'Height', 'Location').where("Type = 'Toolchest'").execute()
# For each toolbox, get its storage places and insert into the collection
for tc_row in tc_results.fetch_all():
    new_tc = {
        'vendorid': get_mapping(tc_row[1], vendor_id_map)[1],
        'description': tc_row[2],
        'width': tc_row[3],
        'depth': tc_row[4],
        'height': tc_row[5],
        'location': tc_row[6],
    }
    _id = toolchests.add(new_tc).execute().get_generated_ids()[0]
    # Now, generate the tool locations for this document
    tool_locations = []
    for pl_row in get_places(tc_row[0]).fetch_all():
        # Get all organizers and tools that are placed here
        tool_ids = get_handtool_ids(pl_row[5])
        tool_ids.extend(get_powertool_ids(pl_row[5]))
        tool_docids = []
        org_ids = get_organizer_ids(pl_row[5])
        if org_ids:
            for org_id in org_ids:
                map_found = get_mapping(org_id, organizer_place_map)
                if map_found:
                    tool_docids.append(map_found[1])
        for item in tool_ids:
            map_found = get_mapping(item, tool_place_map)
            if map_found:
                tool_docids.append(map_found[1])
        if pl_row[0] == 'Shelf':

            new_item = {
                'type': 'Shelf',
                'description': pl_row[1],
                'width': pl_row[2],
                'depth': pl_row[3],
                'height': pl_row[4],
            }
            if tool_docids:
                new_item.update({'tool_ids': tool_docids})
            loc_id = locations.add(new_item).execute().get_generated_ids()[0]
            tool_locations.append(loc_id)
        else: # drawer is the only other value for type
            new_item = {
                'type': 'Drawer',
                'description': pl_row[1],
                'width': pl_row[2],
                'depth': pl_row[3],
                'height': pl_row[4],
            }
            if tool_docids:
                new_item.update({'tool_ids': tool_docids})
            loc_id = locations.add(new_item).execute().get_generated_ids()[0]
            tool_locations.append(loc_id)
    if len(tool_locations) > 0:
        toolchests.modify('_id = :param1') \
                  .bind('param1', _id) \
                  .set('tool_locations', tool_locations).execute()
show_collection(toolchests)

Listing 7-17Populate the Toolchests Collection

这段代码和前面一样，从表中获取行，并为集合创建一个新文档。然而，这变得有点复杂，因为我们必须将places表条目转换成tool_locations数组。这需要使用助手函数来构建来自数据库handtool和powertool表的 id 列表，以及来自organizer表的 id 列表，因为根据经验，我们知道可以将一个组织者放在工具箱中。

然而，我们还需要检查 places 表，从数据库中找到存储位置，并将它们转换成新的locations集合。我们使用找到的工具 id 用新的工具 id 列表更新集合中的文档。这听起来很复杂，但是如果你花点时间研究一下代码，你会发现我们用助手函数来做这件事更容易。

为了帮助可视化结果，我们使用show_collection()函数来打印集合的内容。

为了简洁起见，我们将省略其他集合(cabinets、shelving_units和workbenches)的代码，因为它们遵循与toolchests转换代码相同的模式。像以前一样，我们只是读取表中的行，并将它们插入到新的集合中。

步骤 4:添加位置

最后一步用于填充每个工具和管理器的位置。回想一下数据库表，我们使用了一个表引用来查找位置。然而，由于我们有一个文档存储，我们可以简单地使用代码中内置的字符串。这保存了一个我们不需要维护的引用，相反，我们在创建和更新操作中设置位置时设置它。

为此，我们使用另一个助手函数来构建位置字符串。我们更新了工具和组织者集合中的所有文档。清单 7-18 显示了构建位置字符串的代码。

# Add the location for each tool
tool_results = tools.find().execute().fetch_all()
for tool in tool_results:
    _id = tool["_id"]
    try:
        location = get_tool_location(_id)
        if location:
            r = tools.modify('_id = :param1').bind('param1', _id).set('location', location[0]).execute()
    except Exception as err:
        print(err)
        exit(1)
show_collection(tools)
# Add the location for each organizer
org_results = organizers.find().execute().fetch_all()
for org in org_results:
    _id = org["_id"]
    try:
        location = get_tool_location(_id)
        if location:
            r = organizers.modify('_id = :param1').bind('param1', _id).set('location', location[0]).execute()
    except Exception as err:
        print(err)
show_collection(organizers)
show_collection(locations)

Listing 7-18Build Location String and Update the Tools and Organizer Collections

注意，我们只是获取每个集合中的所有文档，并用新字符串更新文档。最后，我们打印集合中的文档(用于调试)。

现在我们已经看到了所有的步骤，我们可以执行代码了。由于这是一个很长的脚本，我们将使用 Python 来执行代码，但是您可以使用 shell 来一次单独执行一个步骤。事实上，如果您以前从未编写过这样的代码，这将是首选方法。

您可能认为这需要做大量的工作，但是在开发应用时，它会派上用场。尤其是当您要替换仍在使用的旧的关系数据库应用时。更具体地说，您可以在开发过程中多次运行这个脚本，以改进它和新的应用。更好的是，您可以在切换到新应用的过程中使用该脚本。

幸运的是，您可以在图书网站上名为convert_rdb.py的文件中找到完整的代码。清单 7-19 显示了运行脚本的摘录。

C:\Users\cbell\MySQL Shell\source\Ch07> mysqlsh --py -f convert_rdb.py
User Id: root
Password: ∗∗∗∗∗∗∗∗∗
COLLECTION: vendors
{
    "_id": "00005cae7415000000000000016e",
    "name": "Kobalt",
    "sources": "Lowes",
    "url": "https://www.kobalttools.com/"
}
{
    "_id": "00005cae7415000000000000016f",
    "name": "Craftsman",
    "sources": "Lowes, Ace",
    "url": "https://www.craftsman.com/"
}
{
    "_id": "00005cae74150000000000000170",
    "name": "Irwin",
    "sources": "Lowes",
    "url": "https://www.irwin.com/"
}
...
{
    "_id": "00005cae74150000000000000e41",
    "depth": 12,
    "description": "Top",
    "height": 24,
    "tool_ids": [
        "00005cae74150000000000000df5",
        "00005cae74150000000000000e0d"
    ],
    "type": "Shelf",
    "width": 96
}
{
    "_id": "00005cae74150000000000000e42",
    "depth": 48,
    "description": "Bottom",
    "height": 42,
    "tool_ids": [
        "00005cae74150000000000000e0a"
    ],
    "type": "Shelf",
    "width": 96
}

Listing 7-19Executing the Conversion Script

这将完全填充garage_v2模式和集合。但是，如果您想知道是否需要为每次转换或数据生成创建这样的脚本。答案是你可能不会。

将数据导入文档存储

MySQL Shell 中有一个漂亮的实用程序，可以帮助将 JSON 文档导入到您的集合中。shell 有一个名为 JSON import 的实用程序，它允许您将 JSON 文档直接导入到您的集合中。因此，如果您有 JSON 格式的数据，或者您可以编写一个脚本将其转换成 JSON 格式，那么您可以使用 JSON import 实用程序一次性导入文档。这有多酷？

例如，假设您有从文件或其他输入流中读取的数据，并生成了 JSON 文档。如果您将它们写到一个文件中(文档之间没有逗号)，您可以使用该实用程序一次性导入所有文档。让我们看看如何使用前面文本中的供应商数据来实现这一点。

我们从一个文件开始，文件中的每个文档都用 JSON 字符串表示，如下所示。请注意，文档之间没有逗号。还要注意我们没有_id属性(但是如果您想自己生成文档 id，可以添加它)。

{
    "name": "Kobalt",
    "sources": "Lowes",
    "url": "https://www.kobalttools.com/"
}
{
    "name": "Craftsman",
    "sources": "Lowes, Ace",
    "url": "https://www.craftsman.com/"
}
{
    "name": "Irwin",
    "sources": "Lowes",
    "url": "https://www.irwin.com/"
}
...

要导入文档，可以使用 shell 连接到服务器，然后使用util内置类和import_json()方法指定要导入的文件的路径和一个包含模式和集合的选项字典。清单 7-20 演示了将一个带有 JSON 文档的文件导入到garage_v2模式中的vendors集合中。注意，对于导入大量(JSON)数据来说，import 是一个非常方便的实用工具。

> mysqlsh --py --uri root@localhost:33060
> options = {
          ->   'schema': 'garage_v2',
          ->   'collection': 'vendors',
          -> }
          ->
> util.import_json('vendors.json', options)
Importing from file "vendors.json" to collection `garage_v2`.`vendors` in MySQL Server at localhost:33060

.. 35
Processed 4.68 KB in 35 documents in 0.0161 sec (2.17K documents/s)
Total successfully imported documents 35 (2.17K documents/s)

Listing 7-20Running the JSON Import Utility in the Shell

如您所见，该实用程序将读取文档并将它们插入到您指定的模式和集合中。该实用程序还会在插入之前验证 JSON 文档；因此，如果有错误，您将看到它们被报告，导入将停止。

通过在命令行上指定导入参数和连接，该实用程序还允许在命令行模式下执行，如下文所示。还有一些其他选项可以使用，包括将数据导入到关系表中的 JSON 列，以及支持导入二进制 JSON (BSON)数据。很好！有关 JSON 导入实用程序的更多信息，请参见 https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-json.html 。

> mysqlsh --py --uri root@localhost:33060 --schema=garage_v2 \
          --import vendors.json vendors
Please provide the password for 'root@localhost:33060': ∗∗∗∗
Importing from file "vendors.json" to collection `garage_v2`.`vendors` in MySQL Server at localhost:33060

.. 35
Processed 4.68 KB in 35 documents in 0.0097 sec (3.61K documents/s)
Total successfully imported documents 35 (3.61K documents/s)

好了，现在我们有了一个完整的文档存储，我们可以看看如何为模式类编写代码了。

示范

示例应用的执行与第五章中的示例应用相同。不同之处在于，我们将为 CRUD 操作使用一个基类，为每个集合使用一个子类来处理该集合特有的验证。我们可以这样做，因为我们将数据布局(属性集)从数据中提取出来并放入代码中。因此，CRUD 方法的基本操作对于所有集合都是相同的。我们将在本节的后面看到这是如何工作的。

更具体地说，我们将看到如何首先创建基类(GarageCollection)然后继续创建其他一些类的演示。正如你将看到的，它们遵循相同的设计模式/布局，所以一旦你看到一两个，其他的就很容易预测了。因此，我们将看到使用两个类的详细演练，为了简洁起见，其余的将被演示和呈现较少的细节。

如果您想跟进，请确保已经加载了示例模式，并且 MySQL Shell 已经准备就绪。您可能还想使用代码或文本编辑器来编写代码模块。更重要的是，您应该创建一个名为schema的文件夹，并从父文件夹启动 shell。

例如，您应该创建一个名为mygarage_v2的文件夹，并在该文件夹中创建schema文件夹。然后我们将执行来自mygarage_v2的 shell。为什么呢？因为会使用 Python 导入指令，并使用文件夹名命名代码模块的路径(如from schema import Locations)。我们还将创建单元测试，因此需要一个名为unittests的文件夹来存储测试文件。

让我们从MyGarage类开始。回想一下，这是一个实用程序类，它处理与服务器的连接并获取模式。

我的车库班

该类旨在通过提供登录(连接)到服务器的机制和封装一些常见操作(如获取会话、当前数据库、检查到 MySQL 的连接是否活动、断开连接等)来简化 MySQL 服务器的工作。我们还将包括将 SQL 结果或 select 结果转换为 Python 列表(数组)的方法，以便于处理。表 7-3 显示了我们将为这个类创建的方法的完整列表，包括所需的参数(一些方法不需要它们)。

表 7-3

我的车库类方法

方法

参数

描述

编写源代码

这些方法中的大部分和我们在第五章中看到的一样。但是，后三种不一样。我们需要这些来管理选择放置工具的位置，获取特定工具位置中所有工具的列表，并实现对vendors集合的删除操作的引用完整性。也就是说，我们使用代码来确保没有人删除从另一个文档引用的供应商。

清单 7-21 显示了MyGarage类的代码。虽然这段代码看起来比版本 1 长(确实如此)，但这是因为我们将位置字符串处理和供应商参照完整性移到了代码中。

注意

为简洁起见，本章源代码清单中的注释和非必要行已被删除。

class MyGarage(object):
    def __init__(self, mysqlx_sh=None):
        self.session = None
        if mysqlx_sh:
            self.mysqlx = mysqlx_sh
            self.using_shell = True
        else:
            self.mysqlx = mysqlx
            self.using_shell = False
        self.schema = None
    def connect(self, username, passwd, host, port):
        config = {
            'user': username,
            'password': passwd,
            'host': host,
            'port': port,
        }
        try:
            self.session = self.mysqlx.get_session(∗∗config)
        except Exception as err:
            print("CONNECTION ERROR:", err)
            self.session = None
            raise
        self.schema = self.session.get_schema('garage_v2')
    def get_session(self):
        return self.session
    def get_schema(self):
        return self.schema
    def is_connected(self):
        return self.session and (self.session.is_open())
    def disconnect(self):

        try:
            self.session.close()
        except Exception as err:
            print("WARNING: {0}".format(err))
    def get_locations(self, include_organizers=True):
        tool_locations = []
        if include_organizers:
            organizers = self.schema.get_collection('organizers').find().\
                fields("_id", "type", "description").execute().fetch_all()
            for organizer in organizers:
                list_item_str = "{0} - {1}"\
                  .format(organizer["type"], organizer["description"])
                tool_locations.append((list_item_str, list_item_str))
        storage_collections = ['toolchests', 'shelving_units',
                               'workbenches', 'cabinets']
        for storage_collection in storage_collections:
            collection = self.schema.get_collection(storage_collection)
            items = collection.find().fields("_id", "description",
                             "tool_locations").execute().fetch_all()
            for item in items:
                locations_found = item["tool_locations"]
                if locations_found:
                    for tool_loc_id in locations_found:
                        tool_location = self.schema\
                           .get_collection("locations").\
                            find('_id = :param1')\
                           .bind('param1', tool_loc_id).execute().fetch_all()
                        if tool_location:
                            list_item_str = "{0}, {1} - {2}"\
                                "".format(item["description"],
                                tool_location[0]["description"],
                                          tool_location[0]["type"])
                            tool_locations.append((list_item_str,
                                                   list_item_str))
        return tool_locations
    def build_storage_contents(self, tool_locations):
        storage_places = []
        tools = self.schema.get_collection('tools')
        organizers = self.schema.get_collection('organizers')
        locations = self.schema.get_collection('locations')
        if not tool_locations:
            return storage_places

        list_of_tools = []
        for loc_id in tool_locations:
            tool_location = locations.find("_id = :param1").\
                bind("param1", loc_id).execute().fetch_all()
            if not tool_location or tool_location == []:
                organizer = organizers.find("_id = :param1").\
                    bind("param1", loc_id).execute().fetch_all()
                if not organizer or organizer == []:
                    continue # This is an error!
                description = organizer[0]['description']
                loc_type = organizer[0]['type']
                list_of_tools.append(('organizers', loc_type,
                                     description, 'organizer', ' '))
                continue
            else:
                try:
                    tool_id_list = tool_location[0]['tool_ids']
                except KeyError:
                    tool_id_list = []
                description = tool_location[0]['description']
                loc_type = tool_location[0]['type']
            tool_list_str = '_id in [{0}]'.format(
                ', '.join(['"{0}"'.format(t_id) for t_id in tool_id_list]))
            found_tools = tools.find(tool_list_str).execute().fetch_all()
            for tool in found_tools:
                size = dict(tool).get('size', ' ')
                list_of_tools.append(('tools', tool['type'],
                                      tool['description'],
                                      tool['category'], size))
            storage_places.append((loc_type, description, list_of_tools))
            list_of_tools = []
        return storage_places
    def vendor_in_use(self, vendor_id):
        collections = ['cabinets', 'shelving_units', 'toolchests',
                       'tools', 'workbenches']
        for collection_name in collections:
            collection = self.schema.get_collection(collection_name)
            res = collection.find('vendorid = :param1').\
                bind('param1', vendor_id).execute().fetch_all()
            if res:
                return True
        return False

Listing 7-21MyGarage Class Code

这个代码模块garage_v2还包含一个名为make_list()的助手函数，我们可以用它从一个读操作中生成一个 Python 数组列表。清单 7-22 显示了这个函数的代码。花点时间通读它，你会发现它是转换结果的简单代码。我们可以在集合类中使用这个方法来帮助处理来自模式的结果。

def make_list(results, key_list):
    """Build list of Python arrays from results

    Return a Python array for the list of documents returned from a read
    operation.
    """
    result_list = []
    for result in results:
        item_values = []
        for key in key_list:
            try:
                item_values.append(result[key])
            except KeyError:
                # If key not found, create a placeholder
                item_values.append(“)
        result_list.append(item_values)
    return result_list

Listing 7-22
Helper Function

测试类

在我们开始测试这个类之前，我们必须设置 Python path 变量(PYTHONPATH)来包含我们想要运行测试的文件夹。这是因为我们使用的模块没有安装在系统级，而是位于与我们测试的代码相关的文件夹中。在 Windows 中，可以使用以下命令将执行路径添加到 Python 路径中。

C:\Users\cbell\Documents\my_garage_v1> set PYTHONPATH=%PYTHONPATH%;c:\users\cbell\Documents\mygarage_v1

小费

如果路径中有空格，请确保在路径两边使用引号。

或者，在 Linux 和 macOS 上，您可以使用这个命令来设置 Python 路径。

export PYTHONPATH=$(pwd);$PYTHONPATH

现在我们可以运行 shell 了。为此，我们将使用--py选项在 Python 模式下启动。让我们在课堂上练习一些方法。我们可以尝试所有的方法，除了make_rows()方法。我们稍后会看到这些。清单 7-23 展示了如何在 shell 中导入类，初始化(创建)一个名为mygarage的类实例，然后连接connect()并执行一些方法。最后，我们调用disconnect()来关闭与服务器的连接。

C:\Users\cbell\Documents\mygarage_v2>mysqlsh --py
> from schema.garage_v2 import MyGarage
Running from MySQL Shell. Provide mysqlx in constructor.
> myg = MyGarage(mysqlx)
> myg.connect('root', 'root', 'localhost', 33060)
> schema = myg.get_schema()
> s = myg.get_session()
> myg.is_connected()
true
> myg.disconnect()
> myg.is_connected()
false

Listing 7-23Testing MyGarage using MySQL Shell

接下来，我们将为这个类创建一个单元测试，方法与我们在第五章中所做的相似。事实上，我们将在unittests文件夹中创建一个名为garage_v2_test.py的测试，它将使用与第五章中几乎相同的代码，只改变导入语句中出现的database到 s chema和v1到v2。因此，我们在清单 7-24 中给出了代码，没有进一步的解释。

from __future__ import print_function

from getpass import getpass
from schema.garage_v2 import MyGarage

print("MyGarage Class Unit test")
mygarage = MyGarage(mysqlx)
user = raw_input("User: ")
passwd = getpass("Password: ")
print("Connecting...")
mygarage.connect(user, passwd, 'localhost', 33060)
print("Getting the schema...")
schema = mygarage.get_schema()
print(schema)
print("Getting the session...")
session = mygarage.get_session()
print(session)
print("Connected?")
print(mygarage.is_connected())
print("Disconnecting...")
mygarage.disconnect()
print("Connected?")
print(mygarage.is_connected())

Listing 7-24
garage_v2_test.py

执行单元测试也像我们在第五章中所做的那样。清单 7-25 展示了这个从 shell 运行的测试。

> mysqlsh --py -f unittests\garage_v2_test.py
Running from MySQL Shell. Provide mysqlx in constructor.
MyGarage Class Unit test
User: root
Password:
Connecting...
Getting the schema...
<Schema:garage_v2>
Getting the session...
<Session:root@localhost:33060>
Connected?
True
Disconnecting...
Connected?
False

Listing 7-25Running the garage_v1_test Unit Test

现在，让我们看看构成集合类基础的基类。

集合基类

如前所述，我们将创建一个包含集合的所有 CRUD 操作的基类。我们可以对所有集合使用相同方法的原因是，操作的格式、布局或简单的字段列表是由 JSON 文档本身控制的。因此，通过简单地使用集合类的方法，我们可以通过使用一个基类来简化我们的开发，这个基类完成所有的 CRUD 操作，并使用子类来处理数据上的任何约束。回想一下，我们将强加的约束与必填字段有关，在某些情况下与引用完整性有关。

基类中的方法应该是熟悉的，因为它们与我们在第五章中使用的方法相同，只是为了方便和验证增加了一些。表 7-4 显示了基类中的每个方法，包括每个方法的简短描述。

表 7-4

GarageCollection 基类的方法

方法

参数

描述

注意，我们有预期的 CRUD 操作方法，但是我们也看到了检查创建和更新操作的先决条件的方法。默认情况下，这些方法被设置为返回 True，期望需要这些方法的子类将根据特定于集合的要求填充它们。

还要注意，我们有助手函数来检索生成的最后一个文档 id，这有助于创建操作和获取工具位置列表的方法。最后一个方法不是绝对必要的，因为您可以使用路径表达式来访问数组，但是它使类更加整洁，并且在代码中更容易使用(和阅读)。

编写源代码

基类的代码使用构造函数中传递的集合名称实现前面文本中的方法，以从架构中获取集合的实例。这允许我们对每个 CRUD 操作使用相同的 X DevAPI 调用，不管我们使用哪个集合。事实上，由于我们将 JSON 文档的结构移到了用户界面代码中，所以我们甚至不需要直接使用它，除非我们希望验证必填字段或引用完整性。

因此，我们将创建前面文本中描述的先决条件函数，默认情况下返回 True，这样，如果子类(集合)不需要它们，如果先决条件函数没有在子类中被覆盖，代码将不会停止。

例如，如果我们不需要在更新操作中进行验证，我们就不需要在子类定义中包含该函数，这意味着当从删除或更新操作中调用它时，它仍然可以工作。

此外，由于我们使用了基类，子类继承了基类的方法，这再次意味着我们只需要为 CRUD 操作编写一次代码。让我们看看代码，你可以看到这是如何工作的。清单 7-26 显示了新基类的代码。

class GarageCollection(object):
    def __init__(self, mygarage, collection_name):
        self.mygarage = mygarage
        self.schema = mygarage.get_schema()
        self.collection_name = collection_name
        self.col = self.schema.get_collection(collection_name)
        self.docid = None
    def check_create_prerequisites(self, doc_data):
        return True
    def check_update_prerequisites(self, doc_data):
        return True
    def create(self, doc_data):
        if not self.check_create_prerequisites(doc_data):
            return (False, "Required fields missing.")
        try:
            json_str = {}
            for key in doc_data.keys():
                json_str.update({key: doc_data[key]})
            self.docid = self.col.add(json_str).\
                         execute().get_generated_ids()[0]
        except Exception as err:
            print("ERROR: Cannot add {0}: {1}"
                  "".format(err, self.collection_name))
            return (False, err)
        return (True, None)
    def read(self, _id=None):
        if not _id:
            res = self.col.find().execute().fetch_all()
        else:
            res = self.col.find('_id = :param1').\
                  bind('param1', _id).execute().fetch_all()
        return res

    def update(self, doc_data):
        _id = doc_data.get("_id", None)
        assert _id, "You must supply an Id to update the {0}."\
            "".format(self.collection_name.rstrip('s'))
        if not self.check_update_prerequisites(doc_data):
            return (False, "Required fields missing.")
        try:
            for key in doc_data.keys():
                # Skip the _id key
                if key != '_id':
                    self.col.modify('_id = :param1') \
                        .bind('param1', _id) \
                        .set(key, doc_data[key]).execute()
        except Exception as err:
            print("ERROR: Cannot update {0}: {1}".format(
                self.collection_name.rstrip('s'), err))
            return (False, err)
        return (True, None)
    def delete(self, _id=None):
        assert _id, "You must supply an Id to delete the {0}."\
            "".format(self.collection_name.rstrip('s'))
        try:
            self.col.remove('_id = :param1').bind('param1', _id).execute()
        except Exception as err:
            print("ERROR: Cannot delete {0}: {1}"
                  "".format(self.collection_name.rstrip('s'), err))
            return (False, err)
        return (True, None)
    def get_last_docid(self):
        docid = self.docid
        self.docid = None # Clear it after it was read
        return docid
    def get_tool_locations(self, _id=None):
        assert _id, "You must supply an Id to get the tool locations."
        results = []
        if _id:
            places = self.col.find('_id = :param1').bind('param1', _id).\
                fields("tool_locations").execute().fetch_all()
            try:
                tool_locations = places[0]["tool_locations"]
                if tool_locations:
                    locations = self.mygarage.get_schema().\
                               get_collection("locations")
                    tool_ids = ', '.join(['"{0}"'
                     “.format(tool_id) for tool_id in tool_locations])
                    tool_loc_str = '_id in [{0}]'.format(tool_ids)
                    results = locations.find(tool_loc_str).\
                              execute().fetch_all()
            except KeyError:
                results = []
        return results

Listing 7-26The GarageCollection Base Class Code

让我们看看其中的一个子类。在这个例子中，我们将看到Vendors类，它在garage_v2模式中对 vendors 集合进行建模。我们将这段代码放在名为vendors.py的schema文件夹中的代码文件中。下面显示了Vendors类的代码。注意，它比我们在第五章的示例应用版本 1 中使用的Vendor类代码要少得多。

from schema.garage_collection import GarageCollection
class Vendors(GarageCollection):
    def __init__(self, mygarage):
        """Constructor - set collection name"""
        GarageCollection.__init__(self, mygarage, 'vendors')
    def check_create_prerequisites(self, doc_data):
        """Check prerequisites for the create operation."""
        vendor_name = doc_data.get("name", None)
        assert vendor_name, "You must supply a name for the vendor."
        return True

集合的其余类同样简短，只包含适用于集合的验证代码方法。清单 7-27 显示了集合代码模块的组成(每个模块保存在一个单独的代码模块中),为了简洁起见，去掉了注释。花一些时间来看看使用基类如何通过允许您将特定于集合的代码放在子类中而使编写集合类变得更容易。每个集合的部分以粗体突出显示。为了简洁起见，注释和多余的行再次被删除。

注意

我们将重用第五章中的技术，用单元测试来测试类模块，而不是带你测试每个类模块。

# Cabinets collection - cabinets.py

class Cabinets(GarageCollection):
...
def check_create_prerequisites(self, doc_data):
        vendor_id = doc_data.get("vendorid", None)
        description = doc_data.get("description", None)
        location = doc_data.get("location", None)
        numdoors = doc_data.get("numdoors", None)
        assert vendor_id, "You must supply a vendor id for the cabinet."
        assert description, "You must supply a description for the cabinet."
        assert numdoors, "You must supply the number of doors "\
                         "for the cabinet."
        assert location, "You must supply a location for the cabinet."
        return True

# Locations collection - locations.py

class Locations(GarageCollection):
...
    def check_create_prerequisites(self, doc_data):
        loc_type = doc_data.get("type", None)
        description = doc_data.get("description", None)
        assert loc_type, "You must supply a type for the location."

        assert description, "You must supply a description for the location."
        return True
    def remove_tool(self, tool_id):
        location = self.col.find(':param1 in $.tool_ids').\
            bind('param1', tool_id).execute().fetch_all()
        if location:
            tool_locations = location[0]['tool_ids']
            tool_locations.remove(tool_id)

# Organizers collection - organisers.py

ORGANIZER_TYPES = [
    ('Bag', 'Bag'), ('Basket', 'Basket'), ('Bin', 'Bin'),
    ('Box', 'Box'), ('Case', 'Case'), ('Crate', 'Crate')
]
class Organizers(GarageCollection):
...
    def check_create_prerequisites(self, doc_data):
        description = doc_data.get("description", None)
        org_type = doc_data.get("type", None)
        assert description, "You must supply a description for "\
                            "the organizer."
        assert org_type, "You must supply type for the organizer."
        return True
    def remove_tool(self, tool_id):
        location = self.col.find(':param1 in $.tool_ids').\
            bind('param1', tool_id).execute().fetch_all()
        if location:
            tool_locations = location[0]['tool_ids']
            tool_locations.remove(tool_id)

# Shelving Units collection - shelving_units.py

class ShelvingUnits(GarageCollection):
...
    def check_create_prerequisites(self, doc_data):
        vendor_id = doc_data.get("vendorid", None)
        description = doc_data.get("description", None)
        location = doc_data.get("location", None)
        assert vendor_id, "You must supply a vendor id for "\
                          "the shelving_unit."
        assert description, "You must supply a description for "\
                            "the shelving_unit."
        assert location, "You must supply a location for the shelving_unit."
        return True

# Toolchests collection - toolchests.py

class Toolchests(GarageCollection):
...
    def check_create_prerequisites(self, doc_data):
        vendor_id = doc_data.get("vendorid", None)
        description = doc_data.get("description", None)
        location = doc_data.get("location", None)
        assert vendor_id, "You must supply a vendor id for the toolchest."
        assert description, "You must supply a description for "\
                            "the toolchest."
        assert location, "You must supply a location for the toolchest."
        return True

# Tools collection - tools.py

TOOL_TYPES = [
    ('Adjustable Wrench', 'Adjustable Wrench'), ('Awl',  'Awl'),
    ('Clamp', 'Clamp'), ('Crowbar', 'Crowbar'), ('Drill Bit', 'Drill Bit'),
    ('File', 'File'), ('Hammer', 'Hammer'), ('Knife', 'Knife'),
    ('Level', 'Level'), ('Nutdriver', 'Nutdriver'), ('Pliers', 'Pliers'),
    ('Prybar', 'Prybar'), ('Router Bit', 'Router Bit'), ('Ruler', 'Ruler'),
    ('Saw', 'Saw'), ('Screwdriver', 'Screwdriver'), ('Socket', 'Socket'),
    ('Socket Wrench', 'Socket Wrench'), ('Wrench', 'Wrench'),
    ('Corded', 'Corded'), ('Cordless', 'Cordless'), ('Air', 'Air')
]

class Tools(GarageCollection):
...
    def check_create_prerequisites(self, doc_data):
        vendor_id = doc_data.get("vendorid", None)
        description = doc_data.get("description", None)
        tool_type = doc_data.get("type", None)
        category = doc_data.get("category", None)
        assert vendor_id, "You must supply a vendor id for the tool."
        assert description, "You must supply a description for the tool."
        assert category, "You must supply the category of tool "\
                         "(handtool or powertool) for the tool."
        assert tool_type, "You must supply category for the tool."
        return True

# Workbenches collection - workbenches.py

class Workbenches(GarageCollection):
...
    def check_create_prerequisites(self, doc_data):
        vendor_id = doc_data.get("vendorid", None)
        description = doc_data.get("description", None)
        location = doc_data.get("location", None)
        assert vendor_id, "You must supply a vendor id for the workbench."
        assert description, "You must supply a description for "\
                            "the workbench."
        assert location, "You must supply a location for the workbench."
        return True

Listing 7-27Collection Classes for MyGarage V2

请注意，每个集合都有定制，主要是针对从基类派生的验证方法。但是有些类还添加了额外的方法来允许特定于集合的选项。

例如，我们在 Locations 和 Organizers 类中看到了方法remove_tool()。此方法允许我们通过文档 id 从位置或组织者中删除工具。通过这种方式，我们可以确保在从集合中删除工具时将其删除。

还要注意，我们为集合中的属性添加了数组，这些属性具有一组已知的值(版本 1 中关系表中的枚举值)。在这种情况下，它们出现在organizers和tools集合中。回想一下，我们提到过这些是用代码处理的。在本章的示例应用中，我们使用 Python 数组在用户界面的下拉列表中使用。因此，我们使用代码来建立一组有效值。图 7-2 就是这样一个例子。

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

图 7-2

工具类型的下拉列表

现在，让我们回顾一下在编写应用的其余部分之前如何测试类模块。

测试类模块

我们也将使用第五章中相同的单元测试机制。为了简洁，我们将只检查其中一个单元测试代码模块来提醒我们代码。然后我们使用我们在第五章中使用的相同的run_all.py代码模块机制来执行单元测试。

回想一下，我们在unittests/crud_test.py代码模块中创建了一个名为CRUDTest的基类，它实现了启动(或设置)测试的相同方法，一个显示返回行的通用方法，以及一个我们想要运行的测试用例。然后我们用一个类创建了一个代码模块来测试其中一个集合类(或者我们在第五章中称之为表类)。

例如，我们通过创建一个名为VendorTests的新类来为Vendors类创建一个测试，这个新类是从CRUDTest派生的，并存储在文件unittests/vendor_test.py中。清单 7-28 显示了新类的代码。正如你将看到的，它与我们在第五章中写的测试非常相似，这也展示了为 SQL 和 NoSQL 接口开发代码是多么容易——代码非常相似。

from __future__ import print_function

from unittests.crud_test import CRUDTest
from schema.vendors import Vendors

class VendorTests(CRUDTest):
    """Test cases for the Vendors class"""

    def __init__(self):
        """Constructor"""
        CRUDTest.__init__(self)
        self.vendors = None
        self.last_id = None
        self.vendors = None

    def set_up(self, mysql_x, user=None, passwd=None):
        """Setup the test cases"""
        self.mygarage = self.begin(mysql_x, "Vendors", user, passwd)
        self.vendors = Vendors(self.mygarage)

    def create(self):
        """Run Create test case"""
        print("\nCRUD: Create test case")
        vendor_data = {
            "name": "ACME Bolt Company",
            "url": "www.acme.org",
            "sources": "looney toons"
        }
        self.vendors.create(vendor_data)
        self.last_id = self.vendors.get_last_docid()
        print("\tLast insert id = {0}".format(self.last_id))

    def read_all(self):
        """Run Read(all) test case"""
        print("\nCRUD: Read (all) test case")
        docs = self.vendors.read()
        self.show_docs(docs, 5)

    def read_one(self):
        """Run Read(record) test case"""
        print("\nCRUD: Read (doc) test case")
        docs = self.vendors.read(self.last_id)
        self.show_docs(docs, 1)

    def update(self):
        """Run Update test case"""
        print("\nCRUD: Update test case")
        vendor_data = {
            "_id": self.last_id,
            "name": "ACME Nut Company",
            "url": "www.weesayso.co",
        }
        self.vendors.update(vendor_data)

    def delete(self):
        """Run Delete test case"""
        print("\nCRUD: Delete test case")
        self.vendors.delete(self.last_id)
        docs = self.vendors.read(self.last_id)
        if not docs:
            print("\tNot found (deleted).")

Listing 7-28Code for the VendorTests Class

这项技术的强大之处在于，我们可以继续为每个以类命名的数据库类创建新的测试，并将它们存储在同一个unittests文件夹中。由于测试使用了与第五章中相同的类签名(相同的方法)，我们可以重用它们，用schema代替database，并在导入语句中使用复数形式的集合名称，然后根据需要对数据进行微小的修改，以匹配集合模块的新属性集。

一旦编写了所有的集合类测试代码模块，我们就可以编写一个驱动程序脚本，在一个循环中运行所有的测试。回想一下，驱动程序脚本名为run_all.py，也存储在unittests文件夹中。清单 7-29 显示了这个模块的代码。

from __future__ import print_function

from getpass import getpass
from unittests.cabinet_test import CabinetTests
from unittests.location_test import LocationTests
from unittests.organizer_test import OrganizerTests
from unittests.shelving_unit_test import ShelvingUnitTests
from unittests.toolchest_test import ToolchestTests
from unittests.tool_test import ToolTests
from unittests.vendor_test import VendorTests
from unittests.workbench_test import WorkbenchTests

print("CRUD Tests for all classes...")
crud_tests = []
cabinets = CabinetTests()
crud_tests.append(cabinets)
locations = LocationTests()
crud_tests.append(locations)
shelving_units = ShelvingUnitTests()
crud_tests.append(shelving_units)
toolchests = ToolchestTests()
crud_tests.append(toolchests)
tools = ToolTests()
crud_tests.append(tools)
organizers = OrganizerTests()
crud_tests.append(organizers)
vendors = VendorTests()
crud_tests.append(vendors)
workbenches = WorkbenchTests()
crud_tests.append(workbenches)
user = raw_input("User: ")
passwd = getpass("Password: ")
for test in crud_tests:
    test.set_up(mysqlx, user, passwd)
    test.create()
    test.read_one()
    test.read_all()
    test.update()
    test.read_one()
    test.delete()
    test.tear_down()

Listing 7-29
Test Driver run_all.py

要执行这个测试，您可以使用清单 7-30 中所示的命令以及预期的输出。这里，为了简洁起见，我们只看到输出的一部分。

C:\Users\cbell\Documents\mygarage_v2>mysqlsh --py -f unittests\run_all.py
Running from MySQL Shell. Provide mysqlx in constructor.
CRUD Tests for all classes...
User: root
Password: ∗∗∗∗∗∗∗∗∗∗∗∗

∗∗∗ Cabinets Class Unit test ∗∗∗

Connecting...

CRUD: Create test case
        Last insert id = 00005cc4bca00000000000000001

CRUD: Read (doc) test case

First 1 docs:
--------------------------
{
    "_id": "00005cc4bca00000000000000001",
    "depth": 11,
    "description": "Large freestanding cabinet",
    "height": 11,
    "location": "Read wall next to compressor",
    "numdoors": 2,
    "shelves": [
        {
            "depth": 20,
            "description": "Middle",
            "height": 18,
            "width": 48
        }
    ],
    "vendorid": "00005cae74150000000000000cd6",
    "width": 11
}
...
CRUD: Update test case

CRUD: Read (doc) test case

First 1 docs:
--------------------------
{

    "_id": "00005cc4bca00000000000000001",
    "depth": 11,
    "description": "Cold Storage",
    "height": 11,
    "location": "3rd floor basement",
    "numdoors": 2,
    "shelves": [
        {
            "depth": 20,
            "description": "Top",
            "height": 18,
            "width": 48
        },
        {
            "depth": 20,
            "description": "Bottom",
            "height": 18,
            "tool_ids": [
                "00005cafa3eb00000000000007c5",
                "00005cafa3eb00000000000007c6",
                "00005cafa3eb00000000000007c7"
            ],
            "width": 48
        }
    ],
    "vendorid": "00005cae74150000000000000cd6",
    "width": 11
}

CRUD: Delete test case
        Not found (deleted).

Disconnecting...
...

Listing 7-30Executing the test driver