Tutorials

How to save MYSQL query result to a file ?

If you require to save the results of your MYSQL query to a CSV or Excel sheet, you can do so with the help of 'INTO OUTFILE'. This saves the query result as a 'CSV'. You can open this CSV file in Excel and save it as a .XLS file as well.

The syntax of INTO OUTFILE is as follows :

SELECT .... FROM .... WHERE .... 
INTO OUTFILE 'PATH_OF/FILE' 
[FIELDS TERMINATED BY] 
[OPTIONALLY ENCLOSED BY] 
[LINES TERMINATED BY]  

The optional parameters of this command are :

  • FIELDS TERMINATED BY : Separates each field of the result by the given character.

  • OPTIONALLY ENCLOSED BY : Encloses each field within the given value.

  • LINES TERMINATED BY : Separates each row of the result with the given value.

For Example :

SELECT field_one, fields_two 
FROM table_db
INTO OUTFILE '/srv/temp/myresult.csv'
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

In the above example, the result would be saved in the 'myresult.csv' file of the '/srv/temp' folder. The resulted fields would be separated by ',' and each row would be separated by a new line (\n).

Alternatively if you have SSH access to the server, you can also use the 'mysqldump' command to save the result of a query:

mysqldump -u username -ppassword -h hostname --where=fieldone=1099 --tab=filepathtosaveresult databasename


Copyright © 2018 WPOven