MySQL export data to CSV format _mysql

There are many kinds of programs, I would simply say here:

1. Into outfile

Copy Code code as follows:

SELECT * FROM MyTable
into outfile '/tmp/mytable.csv '
FIELDS terminated by ', '
Optionally enclosed by ' "'
LINES terminated by ' \ n ';

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

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

2. By combining SED

Copy Code code as follows:

Mysql-uroot test-e "Select IP from server where a.name like '%abc% '"-n-s | Sed-e ' s/^/'/g;s/$/"\n/g"; >/tmp/test.csv

Here we first execute the SQL statement using the-e parameter of the MySQL command, then use-N to remove the column name from the output result, and-s to remove the various dashes from the output.

The SED command is then used to replace all the relevant data in the output, replacing three, 1. At the beginning of the line add ", add at the end" and wrap, add "," to separate each field.

3. Through the mysqldump to achieve

Copy Code code as follows:

Mysqldump-u username-p-t-t/path/to/directory dbname table_name ', '

It's the same as the 1 plan.

Just take it as a note.