Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MySQL and MongoDB import and export methods", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL and MongoDB import and export methods" bar!

1. MySQL import and export

(1), mysqlimport

This tool is located in the mysql/bin directory and is a very effective tool for MySQL to load (or import) data. This is a command line tool. There are two parameters and a large number of options to choose from. This tool imports an article (textfile) into the database and table you specify. For example, you want to import the data from the file student.txt into table student in the database class:

Mysqlimportclass.studentstudent.txt

(2), loaddatainfile

This command is very similar to mysqlimport, but this method can be used on the MySQL command line. Like the mysqlimport tool, this command has some optional parameters. For example, if you need to import data from your computer to a remote database server, you can use the following command:

Loaddatalocalinfile "d:\ student.txt" intotablestudent

The above local parameter indicates that the file is a local file and the server is the server you logged in to. This eliminates the need to use ftp to upload files to the server, which mysql does for you.

(3), mysqldump

The mysqldump tool is similar in many ways to the counterproductive tool mysqlimport. They have some of the same options. But mysqldump can do more. It can load the entire database into a separate article. This file contains all the SQL commands you need to rebuild your database. This command takes all the schemas and converts them to DDL syntax, takes all the data, and creates INSERT statements from that data. This tool reverses all the designs in your database. Because everything is included in one article. In this article, this article can be imported back to MySQL with a simple batch and a suitable SQL statement. This tool is incredibly simple and fast. There will never be the slightest headache. Therefore, if you want to load the contents of the entire database mydb into a file, you can use the following command:

Bin/mysqldump-pmydb > mydb.txt

2. MongoDB import and export

(1), mongoexport Export tool

MongoDB provides mongoexport tools that can export a collection to a file in json format or csv format. You can specify which data items to export, or you can export data based on a given condition. The tool help information is as follows:

[chinastor.com-root@localhostbin] #. / mongoexport--helpoptions:--helpproducehelpmessage-v [--verbose] bemoreverbose (includemultipletimesformoreverbositye.g.-vvvvv)-h [--host] argmongohosttoconnectto (/ S1 force s2forsets)-- portargserverport.Canalsouse--hosthostname:port--ipv6enableIPv6support (disabledbydefault)-u [--username] argusername-p [--password] argpassword--dbpathargdirectlyaccessmongoddatabasefilesinthegivenpath,insteadofconnectingtoamongodserver-needstolockthedatadirectory,socannotbeusedifamongodiscurrentlyaccessingthesamepath--directoryperdbifdbpathspecified,eachdbisinaseparatedirectory-d [--db] argdatabasetouse-c [--collection] argcollectiontouse (somecommands)-f [--fields] argcommaseparatedlistoffieldnamese.g.-fname Age--fieldFileargfilewithfieldsnames-1perline-q [--query] argqueryfilter,asaJSONstring--csvexporttocsvinsteadofjson-o [--out] argoutputfile Ifnotspecified,stdoutisused--jsonArrayoutputtoajsonarrayratherthanoneobjectperline [chinastor.com-root@localhostbin] #

How to use MySQL to learn the Import and Export of MongoDB

Let's use a practical example to illustrate the use of this tool:

Export table T1 from the foo library to json format:

[chinastor.com-root@localhostbin] #. / mongoexport-dfoo-ct1-o/data/t1.jsonconnectedto:127.0.0.1exported1records [chinastor.com-root@localhostbin] #

After the export is successful, let's take a look at the style of the / data/t1.json file to see if it is what we want:

[chinastor.com-root@localhostdata] # moret1.json {"_ id": {"$oid": "4f927e2385b7a6814a0540a0"}, "age": 2} [chinastor.com-root@localhostdata] #

Through the above description, the export is successful, but there is a problem. For example, what if you want to import MongoDB data into MySQL? MongoDB provides a csv export format, which can solve the problem of heterogeneous database migration. The age and name columns of the T2 table of the foo library are exported as follows:

[chinastor.com-root@localhostbin] #. / mongoexport-dfoo-ct2--csv-fage,name-o/data/t2.csvconnectedto:127.0.0.1exported1records [chinastor.com-root@localhostbin] #

View the export results of / data/t2.csv:

[chinastor.com-root@localhostdata] # moret2.csvage,name1, "wwl" [chinastor.com-root@localhostdata] #

It can be seen that MongoDB provides us with a strong data export tool.

(2), mongoimport Import tool

MongoDB provides mongoimport tools to import the contents of a specific format file into a collection. The tool help information is as follows:

[chinastor.com-root@localhostbin] #. / mongoimport--helpoptions:--helpproducehelpmessage-v [--verbose] bemoreverbose (includemultipletimesformoreverbositye.g.-vvvvv)-h [--host] argmongohosttoconnectto (/ S1 force s2forsets)-- portargserverport.Canalsouse--hosthostname:port--ipv6enableIPv6support (disabledbydefault)-u [--username] argusername-p [--password] argpassword--dbpathargdirectlyaccessmongoddatabasefilesinthegivenpath,insteadofconnectingtoamongodserver-needstolockthedatadirectory,socannotbeusedifamongodiscurrentlyaccessingthesamepath--directoryperdbifdbpathspecified,eachdbisinaseparatedirectory-d [--db] argdatabasetouse-c [--collection] argcollectiontouse (somecommands)-f [--fields] argcommaseparatedlistoffieldnamese.g.-fname Age--fieldFileargfilewithfieldsnames-1perline--ignoreBlanksifgiven,emptyfieldsincsvandtsvwillbeignored--typeargtypeoffiletoimport.default:json (json,csv,tsv)-fileargfiletoimportfrom Ifnotspecifiedstdinisused--dropdropcollectionfirst--headerlineCSV,TSVonly-usefirstlineasheaders--upsertinsertorupdateobjectsthatalreadyexist--upsertFieldsargcomma-separatedfieldsforthequerypartoftheupsert.Youshouldmakesurethisisindexed--stopOnErrorstopimportingatfirsterrorratherthancontinuing--jsonArrayloadajsonarray,notoneitemperline.Currentlylimitedto4MB.

Below we will illustrate the use of this tool with an actual example of one person:

First take a look at the T1 table data in the foo library:

> db.t1.find (); {"_ id": ObjectId ("4f937a56450beadc560feaa9"), "age": 5} >

T1 has a record of age=5. Let's take a look at what the data in the json file looks like:

[chinastor.com-root@localhostdata] # moret1.json {"_ id": {"$oid": "4f937a56450beadc560feaa7"}, "age": 8} [chinastor.com-root@localhostdata] #

What you see is the data of an age=8 in the t1.json file. Let's use the mongoimport tool to import the records from the json file into the T1 table:

[chinastor.com-root@localhostbin] #. / mongoimport-dfoo-ct1/data/t1.jsonconnectedto:127.0.0.1imported1objects

The tool returns a message indicating that a record has been inserted into the table. Let's go into the library and verify it:

[chinastor.com-root@localhostbin] #. / mongoMongoDBshellversion:1.8.1connectingto:test > usefooswitchedtodbfoo > db.t1.find (); {"_ id": ObjectId ("4f937a56450beadc560feaa9"), "age": 5} {"_ id": ObjectId ("4f937a56450beadc560feaa7"), "age": 8} >

Thank you for reading, the above is the content of "the import and export methods of MySQL and MongoDB". After the study of this article, I believe you have a deeper understanding of the import and export methods of MySQL and MongoDB, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.