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=field_one=1099 --tab=file_path_to_save_result database_name