How to export mysql query results to csv

To export MySQL query results to csv , you usually use php to connect to mysql to execute the query, use php to generate the returned query results in csv format and then export them.

But this is more troublesome and requires PHP to be installed on the server.

Directly use mysql to export csv method

We can use into outfile, fields terminated by, optionally enclosed by, line terminated by statements to export csv

Statement format and function

into outfile 'Exported directory and file name'
Specify the export directory and file name

fields terminated by 'field separator'
Defines the separator between fields

optionally enclosed by 'Field Encloser'
Defines the characters that enclose the field (not valid for numeric fields)

lines terminated by 'line separator'
Defines the separator for each line

example:

mysql -u root
use test;
select * from table into outfile '/tmp/table.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

After execution, the recorded data in talbe will be exported to the /tmp/table.csv file. Each field is separated by , and the field content is a string surrounded by ", and each record uses \r\n for line breaks.

The above method of exporting MySQL query results to csv is all I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.