当前位置：首页 > news >正文

使用Python将MongoDB数据导到MySQL

news 来源：原创 2024/5/2 7:16:08

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”。

$\"\"$

命令：

\u0026gt; \\py \u0026gt; util.import_json(\u0026quot;/path_to_file/neighborhoods_mongo.json\u0026quot;, {\u0026quot;schema\u0026quot;: \u0026quot;test\u0026quot;, \u0026quot;collection\u0026quot;: \u0026quot;neighborhoods\u0026quot;, \u0026quot;convertBsonOid\u0026quot;: True}) \u0026gt; util.import_json(\u0026quot;/path_to_file/restaurants_mongo.json\u0026quot;, {\u0026quot;schema\u0026quot;: \u0026quot;test\u0026quot;, \u0026quot;table\u0026quot;: \u0026quot;restaurants\u0026quot;, \u0026quot;convertBsonOid\u0026quot;: 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导入集合和表的区别

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

$\"\"$

命令：

\u0026gt; \\use test\u0026gt; db.get_collections() \u0026gt; session.sql('SHOW TABLES') \u0026gt; session.sql('SHOW CREATE TABLE test.neighborhoods') \u0026gt; 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”表中，并且我们的应用程序需要查询所有餐馆的名称，以便获取特定类型的菜肴。

命令：

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

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

命令：

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

第一个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