MySQL to CSV

Export MySQL data into CSV format for fast import and export, both locally and remotely

MySQL comes with data export commands (local export)

Export: SELECT * from TableA to outfile '/tmp/aa.csv ' fields terminated by ', ' optionally enclosed by ' "' lines terminated by ' \ n ' "

Import: Load Data infile "/tmp/file.csv" into the table money fields terminated by ', ' optionally enclosed by ' "' lines terminated By ' \ n '

Related parameters:

field TERMINATED by ' string ': Sets the delimiter between the fields for the string, which can be single or multiple characters. The default value is "\ t".

Optionally enclosed by ' character ': sets characters to enclose character-type fields such as char, varchar, and text. No symbols are used by default

LINES TERMINATED by ' string ': Sets the character at the end of each line of data, which can be single or multiple characters. The default value is "\ n"

Export CSV format remotely:

/usr/local/mysql/bin/mysql-h 192.168.1.1-p3307-udlan-proot123-ss-e "SELECT * FROM gjol_log_ob3.money_20160821 limit 2 "|sed ' s/\t/", "/g;s/^/"/;s/$/"/;s/\n//g" >/tmp/file1.csv

# #ss为导出的时候去掉表头字段

Import data: Load infile "/tmp/file1.csv" into table money1 fields terminated by ', ' optionally enclosed by ' "' lines Termina Ted by ' \ n '

# # #sed command using:

Common options:

-N: Use Quiet (silent) mode. In the usage of general sed, all data from stdin is generally listed on the screen. However, if you add the-n parameter, only the line (or action) that is specially processed by SED is listed.

-E: The Action of SED is edited directly on the instruction list mode;

-F: The action of SED is written directly in a file, and-f filename can perform the SED action within filename;

-r:sed's actions support the syntax of extended formal notation. (Presupposition is the basic formal notation of French law)

-I: Directly modify the contents of the read file instead of the screen output.

Common commands:

A: New, a can be followed by a string, and these strings will appear in a new line (the current next line) ~

C: Replace, C can be followed by strings, these strings can replace the line between N1,N2!

D: Delete, because it is deleted ah, so d usually do not pick up any boom;

I: Insert, I can be followed by the string, and these strings will appear on a new line (the current line);

P: Print, that is, print out a selected material. Normally p will work with parameter Sed-n ~

S: Replace, can be directly replaced by work! Usually this s action can be paired with formal notation! For example 1,20s/old/new/g is!

Data export to CSV format, can be better applied to other platforms, such as Hadoop,inf and other scenarios, speed up the speed of data I/O

This article is from the "DBSpace" blog, so be sure to keep this source http://dbspace.blog.51cto.com/6873717/1886470

MySQL to CSV