MySQL export data in csv format _ MySQL

This article describes how to export MySQL data in csv format. you can refer to the following solutions for more information:

1. into outfile

The code is as follows:

SELECT * FROM mytable
Into outfile '/tmp/mytable.csv'
Fields terminated ','
Optionally enclosed '"'
Lines terminated by '\ n ';

I found a particularly serious problem during usage. I cannot insert query conditions, such as where, that is, I can only export the entire table. I don't know if there is a problem with my writing, if you have any idea, please leave a message for me.

The second problem is that the outfile path must have the write permission. the mysql process permission is generally mysql user, so it is best to export it to the/tmp directory.

2. combine sed

The code is as follows:

Mysql-uroot test-e "select ip from server where. name like '% abc %' "-N-s | sed-e's/^/"/g; s/$/"\ n/g';> /tmp/test.csv

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

Then, use the sed command to replace all the relevant data in the output result, with three replicas, 1. add ", add at the end of a row" and line feed at the beginning of the row, and add "," to separate each field.

3. implemented through mysqldump

The code is as follows:

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

Similar to solution 1.

Take a note.