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