mysql– Export data into CSV

There are a lot of options here, and here's a quick look:

1. Into outfile

1 SELECT *  frommytable2  intoOUTFILE'/tmp/mytable.csv'  3Fields TERMINATED by ','  4Optionally enclosed by '"'  5LINES TERMINATED by '\ n';

In my use of the process found a particularly serious problem, which can not be inserted into the query conditions, such as where these, that is, only the whole table export, do not know if I have a problem with the writing, have to know friends please give me a message.

The second problem is that the outfile path must have write permissions, our MySQL process permissions are generally MySQL users, so it is best to export to the/tmp directory below.

2. By combining SED

" Select IP from server where a.name like '%abc% ' "  sed ' s/^/"/g;s/$/" \n/g '; >/tmp/test.csv

This first uses the MySQL command's-e parameter to execute the SQL statement, and then through-N to remove the output of the column name,-s to remove the output of the various dashes.

It then replaces all the relevant data in the output of the SED command, replacing it with three, 1. At the beginning of the line, add ", increase at row end" and wrap, adding "," to separate each field.

3. Through mysqldump to achieve

Mysqldump-u username-p  -t-t/path/to/directory dbname table_name--fields-terminated-by= ', '

It's similar to the 1 scheme.

Just take it as a note.

mysql– Export data into CSV