使用 Python 将 MongoDB 数据导到 MySQL

MySQL Shell 8.0.13(GA)引入了一项新功能，让你可以轻松地将 JSON 文档导入到 MySQL。这篇文章将介绍这项功能的实际用例：如何将 MongoDB 中的 JSON 数据导入 MySQL。这些方法同样适用于将数据从其他文档存储数据库导入 MySQL，只要它们能够生成或导出 JSON 数据。

无论出于何种原因，在迁移到不同的数据库时都不应该掉以轻心，在某些情况下还可能会非常复杂。在其他方面，它取决于数据库服务器的数量及它们在系统中的组织方式（单一服务器还是复杂的复制拓扑）、数据大小、执行迁移任务的要求和可用资源。为了确保迁移成功，需要制定全面详细的计划，计划中应该包括所有必需的步骤和相应的操作和工具。

不同的场景需要不同的迁移计划，但它们可能会包括一些通用的步骤，例如更新应用程序，让它们使用新的数据库，以及导出和导入数据库数据。MySQL Shell 提供的用于导入 JSON 数据的新功能有助于我们以更简单的方式完成这些任务。接下来让我们看看如何使用 Python 将数据从 MongoDB 导入到 MySQL。

将数据从 MongoDB 导出到 JSON

MongoDB 允许你将所有数据导出到 JSON，但它会生成遵循严格模式表示的MongoDB Extended JSON。好在 MySQL Shell JSON 导入功能为我们提供了“convertBsonOid”选项，可以轻松转换 BSON ObjectId 类型。

注意：如果你的数据包含除 ObjectId 之外的其他 BSON 类型，它们以严格模式表示（例如，BinData、Date、Timestamp、NumberLong、NumberDecimal 等），那么你可能需要根据你的需求手动转换这些类型（例如，在导入/导出过程中使用自定义转换脚本，或在应用程序级别转换它们）。有关 BJSON 类型及其在严格模式下的表示的更多信息，请参阅：BSON数据类型和相关表示。

首先，你需要将所有 MongoDB 数据导出到 JSON 文档。例如，假设你在“test”数据库中有两个集合，名为 restaurants 和 neighboors。你可以使用以下命令将 MongoDB 数据导出到 JSON 文档（每个集合对应一个文件）：

$ mongoexport --db test --collection restaurants --out restaurants_mongo.json2018-10-08T18:38:19.104+0100 connected to: localhost2018-10-08T18:38:19.633+0100 exported 25359 records $ mongoexport --db test --collection neighborhoods --out neighborhoods_mongo.json2018-10-08T18:38:45.923+0100 connected to: localhost2018-10-08T18:38:46.382+0100 exported 195 records

你将得到两个包含导出数据的 JSON 文件“restaurants_mongo.json”和“neighborhoods_mongo.json”。

使用 Python 将 JSON 数据导入 MySQL

接下来，你只需使用 MySQL Shell 的新功能将生成的 JSON 文件导入到 MySQL 数据库。我们将在下一个示例中使用 Python 函数“util.import_json”。

命令：

> \py > util.import_json("/path_to_file/neighborhoods_mongo.json", {"schema": "test", "collection": "neighborhoods", "convertBsonOid": True}) > util.import_json("/path_to_file/restaurants_mongo.json", {"schema": "test", "table": "restaurants", "convertBsonOid": True})

“convertBsonOid”选项被设置为 true，导入的两个文件中的 MongoDB Object Id（OID）被转换为正确的值。在将 JSON 数据导入到集合时，这是必需的，因为 OID 被转换为 MySQL 用于标识每个文档的集合 Id（更具体地说，“_id”列和“_id”字段用于标识集合中的每个文档），否则在尝试将包含 OID 的字段导入到集合中的 MySQL 文档 Id（“_id”）时将发出错误。

使用 Python 函数 util.import_json()将第一个文件“neighborhoods_mongo.json”导入集合“neighborhoods”。使用相同的函数将第二个文件“restaurants_mongo.json”导入到表“restaurants”中。对于这两种情况，目标集合和表都不存在，因此它们是由 JSON 导入函数自动创建的。

有关 JSON 导入辅助程序的更多详细信息，请参见https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-json.html。你也可以直接使用 MySQL Shell 的帮助信息，只需输入：“\h import_json”。

将 JSON 导入集合和表的区别

在使用不同的目标对象（集合或表）时，在导入结果方面存在一些细微的差别。让我们看一下导入数据的结构，看看它们有什么差别。

命令：

> \use test> db.get_collections() > session.sql('SHOW TABLES') > session.sql('SHOW CREATE TABLE test.neighborhoods') > session.sql('SHOW CREATE TABLE test.restaurants')

首先，正如预期的那样，我们可以看到“restaurants”数据未被列为集合，因为它是专门导入到表中的。但是，所有导入的文件都列为表，甚至是导入到集合的 JSON 文档。这是因为在内部集合也被存储为 MySQL 表，只是具有非常特定的结构。

再看一下“SHOW CREATE TABLE”命令的输出结果，可以看出它们之间的差别。对于这两种情况，JSON 文档实际上是存储在 JSON 类型的“doc”列中，区别在于标识列。对于“neighborhoods”集合，标识列为“_id”，该列与 JSON 文档中的“_id”字段匹配，该字段包含导入期间转换的 OID。因此，OID 实际上与导入到 MySQL 中的 JSON 文档的 Id 相匹配，作为主键。对于“restaurants”表，标识列为“id”，该列是一个自动增量的整数，为每个 JSON 文档（行）生成一个新的标识。因此，原始 OID 将被忽略，有就是不会作为主键，尽管每个 JSON 文档中的_id 字段将包含导入期间转换的 OID。

在实际当中，这种差异会影响你使用导入数据的方式。如果你选择集合作为导入目标，你就可以使用所有 MySQL 文档存储功能（NoSQL + SQL）。另一方面，如果你选择表作为导入目标，你将只能使用可用的 JSON 类型功能和 MySQL 提供的原生 JSON 函数来操作你的 JSON 数据（仅限 SQL）。

如何为某些导入的数据创建新列

从前面的示例中可以看到，所有导入的数据都存储在 JSON 类型的列中。但是，在某些情况下，可能需要将数据的一部分放在另一列中，例如，创建索引以提升某些查询的执行速度。下面让我们来看一个简单的示例。假设我们将一些额外的 JSON 数据从“primer-dataset.json”导入到新的“my_restaurants”表中，并且我们的应用程序需要查询所有餐馆的名称，以便获取特定类型的菜肴。

命令：

> util.import_json("/path_to_file/primer-dataset.json", {"schema": "test", "table": "my_restaurants"}) > shell.options.outputFormat = "vertical" > session.sql('EXPLAIN SELECT doc->>"$.name" AS name FROM test.my_restaurants WHERE doc->>"$.cuisine" = "Italian"')

使用 EXPLAIN 命令查看查询执行计划，我们可以验证查询是否会执行全表扫描，我们不建议使用这种全表扫描的查询方式（特别是对于包含大量数据的表）。幸运的是，我们可以为用于过滤结果的属性添加新列和索引。我们可以使用 ALTER TABLE 语句在生成的列上创建二级索引，从而轻松解决这个问题。

命令：

> session.sql('ALTER TABLE test.my_restaurants ADD COLUMN cuisine VARCHAR(80) GENERATED ALWAYS AS (doc->>"$.cuisine") VIRTUAL, WITH VALIDATION') > session.sql('ALTER TABLE test.my_restaurants ADD INDEX cuisine_idx (cuisine)') > session.sql('EXPLAIN SELECT doc->>"$.name" AS name FROM test.my_restaurants WHERE cuisine = "Italian"')

第一个 ALTER TABLE 语句将新列“cuisine”添加到“my_restaurants”表中，它创建了一个虚拟列，并执行了验证，确保生成的值不超出合法范围。然后，执行第二个 ALTER TABLE 语句，在新的“cuisine”列上添加索引。

现在，分析 EXPLAIN 的结果，我们可以验证查询执行成本是否会低得多，因为使用了索引（没有全表扫描）。

注意：在上图中，我们展示了从 MySQL Workbench 获得的查询执行计划的截图，以便更直观地说明每种情况的成本。

动手尝试

MySQL Shell 可从以下链接下载。

• MySQL 开发者网站：https://dev.mysql.com/downloads/shell/
  

• MySQL Shell 也可以在 GitHub 上找到：https://github.com/mysql/mysql-shell
  

可以在此处找到 MySQL Shell 的文档：https://dev.mysql.com/doc/mysql-shell/8.0/en/

以上示例中使用的 JSON 数据可通过以下链接获取。

• Restaurants 和 NeighborhoodsJSON 文档：

https://raw.githubusercontent.com/mongodb/docs-assets/geospatial/restaurants.json

https://raw.githubusercontent.com/mongodb/docs-assets/geospatial/neighborhoods.json

• 基础数据集 JSON 文档：

https://raw.githubusercontent.com/mongodb/docs-assets/primer-dataset/primer-dataset.json

英文原文：https://mysqlserverteam.com/importing-data-from-mongodb-to-mysql-using-python/