将数据库从 mysql 转换为 mongoDb
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6251548/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
converting database from mysql to mongoDb
提问by user737767
is there any easy way to change the database from mysql to mongoDB ?
有什么简单的方法可以将数据库从 mysql 更改为 mongoDB 吗？
or better any one suggest me good tutorial do it
或者更好的人建议我做一个好的教程
采纳答案by Gates VP
is there any easy way to change the database from mysql to mongoDB ?
有什么简单的方法可以将数据库从 mysql 更改为 mongoDB 吗？
Method #1: export from MySQL in a CSV format and then use the mongoimport tool. However, this does not always work well in terms of handling dates of binary data.
方法#1：从 MySQL 以 CSV 格式导出，然后使用mongoimport 工具。然而，这在处理二进制数据的日期方面并不总是很好。
Method #2: script the transfer in your language of choice. Basically you write a program that reads everything from MySQL one element at a time and then inserts it into MongoDB.
方法#2：用您选择的语言编写传输脚本。基本上，您编写了一个程序，一次从 MySQL 一个元素中读取所有内容，然后将其插入到 MongoDB 中。
Method #2 is better than #1, but it is still not adequate.
方法#2 比#1 好，但仍然不够。
MongoDB uses collections instead of tables. MongoDB does not support joins. In every database I've seen, this means that your data structure in MongoDB is different from the structure in MySQL.
MongoDB 使用集合而不是表。MongoDB 不支持连接。在我见过的每个数据库中，这意味着您在 MongoDB 中的数据结构与在 MySQL 中的结构不同。
Because of this, there is no "universal tool" for porting SQL to MongoDB. Your data will need to be transformed before it reaches MongoDB.
因此，没有将 SQL 移植到 MongoDB 的“通用工具”。您的数据在到达 MongoDB 之前需要进行转换。
回答by Andrew K
If you're using Ruby, you can also try: Mongify
如果你使用 Ruby，你也可以试试：Mongify
It's a super simple way to transform your data from a RDBS to MongoDB without losing anything.
这是一种将数据从 RDBS 转换为 MongoDB 而不会丢失任何内容的超级简单方法。
Mongify will read your mysql database, build a translation file for you and all you have to do is map how you want your data transformed.
Mongify 将读取您的 mysql 数据库，为您构建一个翻译文件，您所要做的就是映射您希望如何转换数据。
It supports:
它支持：
- Auto updating IDs (to BSON ObjectID)
- Updating referencing IDs
- Type Casting values
- Embedding tables into other documents
- Before save filters (to allow changes to the data manually)
- and much much more...
- 自动更新 ID（到 BSON ObjectID）
- 更新参考 ID
- 类型转换值
- 将表格嵌入其他文档
- 保存过滤器之前（允许手动更改数据）
- 还有更多……
Read more about it at: http://mongify.com/getting_started.html
阅读更多相关信息：http: //mongify.com/getting_started.html
There is also a short 5 min video on the homepage that shows you how easy it is.
主页上还有一个 5 分钟的短视频，向您展示了它是多么容易。
回答by umutm
回答by benkamin
Here's what I did it with Node.js for this purpose:
为此，我使用 Node.js 执行了以下操作：
var mysql = require('mysql');
var MongoClient = require('mongodb').MongoClient;
function getMysqlTables(mysqlConnection, callback) {
mysqlConnection.query("show full tables where Table_Type = 'BASE TABLE';", function(error, results, fields) {
if (error) {
callback(error);
} else {
var tables = [];
results.forEach(function (row) {
for (var key in row) {
if (row.hasOwnProperty(key)) {
if(key.startsWith('Tables_in')) {
tables.push(row[key]);
}
}
}
});
callback(null, tables);
}
});
}
function tableToCollection(mysqlConnection, tableName, mongoCollection, callback) {
var sql = 'SELECT * FROM ' + tableName + ';';
mysqlConnection.query(sql, function (error, results, fields) {
if (error) {
callback(error);
} else {
if (results.length > 0) {
mongoCollection.insertMany(results, {}, function (error) {
if (error) {
callback(error);
} else {
callback(null);
}
});
} else {
callback(null);
}
}
});
}
MongoClient.connect("mongodb://localhost:27017/importedDb", function (error, db) {
if (error) throw error;
var MysqlCon = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
port: 8889,
database: 'dbToExport'
});
MysqlCon.connect();
var jobs = 0;
getMysqlTables(MysqlCon, function(error, tables) {
tables.forEach(function(table) {
var collection = db.collection(table);
++jobs;
tableToCollection(MysqlCon, table, collection, function(error) {
if (error) throw error;
--jobs;
});
})
});
// Waiting for all jobs to complete before closing databases connections.
var interval = setInterval(function() {
if(jobs<=0) {
clearInterval(interval);
console.log('done!');
db.close();
MysqlCon.end();
}
}, 300);
});
回答by ali habib
If anyone's still looking for a solution, i found that the easiest way is to write a PHP script to connect to your SQL DB, retrieve the information you want using the usual Select statement, transform the information into JSON using the PHP JSON Encode functions and simply output your results to file or directly to MongoDB. It's actually pretty simple and straight forward, the only thing to do is to double check your output against a Json validator, you may have to use functions such as explode to replace certain characters and symbols to make it valid. I have done this before however i currently do not have the script at hand but from what i can remember it was literally half a page of code.
如果有人仍在寻找解决方案，我发现最简单的方法是编写一个 PHP 脚本来连接到您的 SQL DB，使用通常的 Select 语句检索您想要的信息，使用 PHP JSON Encode 函数将信息转换为 JSON，然后只需将结果输出到文件或直接输出到 MongoDB。它实际上非常简单直接，唯一要做的就是根据 Json 验证器仔细检查您的输出，您可能需要使用诸如 expand 之类的函数来替换某些字符和符号以使其有效。我以前这样做过，但是我目前手头没有脚本，但据我所知，它实际上是半页代码。
Oh also remember Mongo is a document store so some data mapping is required to get it to be acceptable with mongo.
哦还记得 Mongo 是一个文档存储，所以需要一些数据映射才能让它被 mongo 接受。
回答by Danny Sofftie
For those coming to this with the same problem, you can check out this Github project. This is an ongoing development that will help you migrate data from MySQL database to MongoDB by simply running a simple command.
对于那些遇到同样问题的人，您可以查看这个Github 项目。这是一项持续开发，只需运行一个简单的命令，即可帮助您将数据从 MySQL 数据库迁移到 MongoDB。
It will generate MongoDB Schemas in TypeScript so you can use them later in your project. Each MySQL table will be a MongoDB collection, and datatypes will be efficiently converted to their MongoDB compatibles.
它将在 TypeScript 中生成 MongoDB 模式，以便您稍后可以在您的项目中使用它们。每个 MySQL 表将是一个 MongoDB 集合，数据类型将有效地转换为它们的 MongoDB 兼容类型。
The documentation for the same can be found in the project's README.md. Feel free to come in and contribute. Would like to help if need be.
相同的文档可以在项目的README.md 中找到。请随意加入并做出贡献。如果需要，愿意提供帮助。
回答by Peter Tillemans
I am kind of partial to TalendOpenStudiofor those kind of migration jobs. It is an eclipse based solution to create data migration "scripts" in a visual way. I do not like visual programming, but this is a problem domain I make an exception.
对于这类迁移工作，我有点偏爱TalendOpenStudio。它是一种基于 Eclipse 的解决方案，以可视化方式创建数据迁移“脚本”。我不喜欢可视化编程，但这是一个我例外的问题领域。
Adrien Mogenet has create a MongoDBConnectionplugin for mongodb.
Adrien Mogenet为 mongodb创建了一个MongoDBConnection插件。
It is probably overkill for a "simple" migration but ut is a cool tool.
对于“简单”迁移来说，这可能有点矫枉过正，但 ut 是一个很酷的工具。
Mind however, that the suggestion of Nix will probably save you time if it is a one-of migration.
但是请注意，如果是一次性迁移，Nix 的建议可能会节省您的时间。
回答by Vaibhav Kaushal
You can use QCubed (http://qcu.be) framework for that. The procedure would be something like this:
您可以为此使用 QCubed ( http://qcu.be) 框架。程序将是这样的：
- Install QCubed (http://www.thetrozone.com/qcubed-installation)
- Do the codegen on your database. (http://www.thetrozone.com/php-code-generation-qcubed-eliminating-sql-hassle)
- Take your database offline from the rest of the world so that only one operation runs at a time.
- Now write a script which will read all rows from all tables of the database and use the getJson on all objects to get the json. You can then use the data to convert to array and push it into the mongoDB!
- 安装 QCubed ( http://www.thetrozone.com/qcubed-installation)
- 在您的数据库上执行代码生成。（http://www.thetrozone.com/php-code-generation-qcubed-elimating-sql-hassle）
- 使您的数据库与世界其他地方脱机，以便一次只运行一项操作。
- 现在编写一个脚本，该脚本将从数据库的所有表中读取所有行，并在所有对象上使用 getJson 来获取 json。然后，您可以使用数据转换为数组并将其推送到 mongoDB 中！
回答by Virtimus
Try this: Automated conversion of MySQL dump to Mongo updates using simple r2n mappings. https://github.com/virtimus/mysql2mongo
试试这个：使用简单的 r2n 映射将 MySQL 转储自动转换为 Mongo 更新。 https://github.com/virtimus/mysql2mongo
回答by sathis
You can use the following project.It requires solr like configuration file to be written.Its very simple and straight forward.
可以使用下面的项目，需要写solr之类的配置文件，非常简单直接。
