Sometimes you want to save the output of an SQL query to a file for later review.
For instance we have this query: SELECT * FROM table WHERE foo='bar' .
To log all row into a file we add the following to our query: INTO OUTFILE '/tmp/file.txt'.
The query will then look like this:
SELECT * FROM table WHERE foo='bar' INTO OUTFILE '/tmp/file.txt'
By default the contents of this file will be tab-seperated data. You can now import this easily into Libreoffice Calc or Excel.
You need to pay attention where to write the output file to. MySQL usually runs on it own user account and therefore has no write access to your home directory. In the example I used the /tmp directory as it is world writeable.
Field seperation
Optionally you can change the field seperator with the following syntax:
SELECT * FROM table WHERE foo='bar' INTO OUTFILE '/tmp/file.txt' FIELDS SEPERATED BY ';'
In the above example I changed the seperator to a semicolon. Different versions of Excel behave differently when you feed them CSV data. Sometimes it will ask you how to import it (good), but most of the time it will throw all data into one cell (bad). Changing the seperator will help to get Excel do the proper thing.
While we are at it, the OPTIONALLY ENCLOSED BY option can be used to enclose strings in output fields which can contain seperator characters.
SELECT * FROM table WHERE foo='bar' INTO OUTFILE '/tmp/file.txt' FIELDS SEPERATED BY ';' OPTIONALLY ENCLOSED BY '"'
In the last example we seperate the fields with the ; character and enclose each string field with double quotes.
For the full list of options see the manual.