Explanation of the method of exporting query results to csv through mysql

To export the query results of mysql to csv, you will generally use php to connect to mysql to execute the query, and use php to generate the returned query results in csv format and then export it.

But this is more troublesome and requires the server to install php to achieve this.

Use mysql directly to export csv method

We can use the into outfile, fields terminated by, optionally enclosed by, line terminated by statement to achieve export csv

The format and function of the statement

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

fields terminated by 'Field delimiter'
Define the delimiter between fields

optionally enclosed by 'Field wrapper'
Define the characters surrounding the field (numeric fields are invalid)

lines terminated by 'interline delimiter'
Define the delimiter 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 talle will be exported to the /tmp/table.csv file. Each field is separated by , , and the field content is a string surrounded by ”. Each record uses \r\n to wrap the line.

This article explains the method of exporting query results to csv through mysql. For more related content, please pay attention to the php Chinese website.

Related recommendations:

php array_push and $arr[]=$ Performance comparison between values

How to use php to set a session that strictly controls the expiration time

How to use php Method to determine whether memcache key/value exists

The above is the detailed content of Explanation of the method of exporting query results to csv through mysql. For more information, please follow other related articles on the PHP Chinese website!