Export the MongoDB data specified field and then specify the field to import the MySQL instance and solve the related problem

Requirements: Export the MONGODB data specified field and then specify the field to import into the MySQL table

Go straight,

Finally, you will encounter a few problems and solutions, so that a similar problem can be resolved quickly (the specific usage of the commands used during the period is not covered in this article)

1. Export the specified field of MONGODB data, and use the command Mongoexport to check the official documents;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/80/4B/wKiom1c9XeOyTg1tAABDbpOx7js007.png "style=" float: none; "title=" 1.2--export.png "alt=" Wkiom1c9xeoytg1taabdbpox7js007.png "/>

Export the specified level two field Mongodb

/opt/mongodb/bin/mongoexport--host yourip--port yourport-d app_form-c application_data--csv--out export2.csv --fields Applicant.name,applicant. Id_card_num, Applicant. Cell_phone_num, Applicant. home_addr

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/80/4B/wKiom1c9XeOQqhaiAABqe_aZhCY411.png "style=" float: none; "title=" 1--export.png "alt=" Wkiom1c9xeoqqhaiaabqe_azhcy411.png "/>

2. On the MySQL machine rsync transmission over, RYSNC usage will not speak, took

RSYNC-CHAVZP--stats [Email protected]:/home/mongodata/export.csv/data/mysql/

3. Import MySQL, there are two cases, one is to import a new table, the other is to import the existing table, but you need to specify the table specific fields

1. New table:

2. LOAD DATA INFILE '/data/mysql/export2.csv ' into TABLE temp_test CHARACTER SET UTF8 fields TERMINATED by ', ' enclosed by ‘"‘;

1. Cousin the specified field:

2. LOAD DATA INFILE '/data/mysql/export2.csv ' into TABLE temp_test2 CHARACTER SET utf8mb4 fields TERMINATED by ', ' enclosed By ' "' (name,idcard,tellnumber, address);

Result validation:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/80/4B/wKiom1c9Xu7jiC-VAABk6y__R68477.png "title=" Yanzheng1.png "alt=" Wkiom1c9xu7jic-vaabk6y__r68477.png "/>

Import success;

4. Difficult diseases 1:mongo the correct method for exporting level two fields when exporting

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/80/4C/wKiom1c9X5KiPBEmAABPNA1iDW0386.png "title=" 111111 --erjiziduan.png "alt=" Wkiom1c9x5kipbemaabpna1idw0386.png "/>

4. Incurable Diseases 2:mysql Import Error 1366 (HY000): Incorrect string value: ' \xf0\xa5\x8c\x93\xe5\x85 ... ' for column ' address ' at row 452 93 workaround

This problem is difficult to solve, it is generally believed that UTF8MB4 caused, specific reasons to see below, you need to modify the table's default character set, and the specified UTF8MB4 import;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/80/4C/wKiom1c9YDOwx0fyAADCyDOumRk173.png "title=" Error1--solutions.png "alt=" Wkiom1c9ydowx0fyaadcydoumrk173.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/80/4C/wKiom1c9YEWiy-c2AAA8UJbd7bM809.png "title=" 22222- -mb4.png "alt=" Wkiom1c9yewiy-c2aaa8ujbd7bm809.png "/>

ALTER TABLE temp_test2 CONVERT to CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Modified 1

4. Incurable diseases 3:mongo Import error 1262 (01000): Row 1066659 was truncated; It contained more data than there were input columns solution, sql_mode problem, need to be modified;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/80/4C/wKiom1c9YPjTLYVFAAC-Ptk_o0k693.png "title=" 3333-- Ssss.png "alt=" Wkiom1c9ypjtlyvfaac-ptk_o0k693.png "/>

5. Other problems, such as primary key duplication, the field size does not meet the requirements, and so on the small problem self-pondering solution

The final normal results show:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/80/49/wKioL1c9YjSghTDVAABxq1qFmJs092.png "title=" Success.png "alt=" Wkiol1c9yjsghtdvaabxq1qfmjs092.png "/>

Baidu found in the post can solve this kind of problem is very few, and is not a complete example, here put the whole operation of the example, I hope to help you;

This article is from the "Linux-centos notes" blog, so be sure to keep this source http://fuyuan2015.blog.51cto.com/8678344/1775054

Export the MongoDB data specified field and then specify the field to import the MySQL instance and solve the related problem