Create An Output File From A SQL Query

Sometimes, keeping files as hard copy, or just being able to move things in and around using csv files or such like can be great. However, doing so when you don’t have access to a mysql client (like phpmyadmin) can be a bit tricky. Hopefully this script can help you out. Of course you can change things inside it to make a whole wealth of different files with different data inside it. Have some fun (if you like it!). Either run from a scripting language mysql_query("SELECT..."); Or from logging into the command line mysql:

mysql -u root -p
use [DATABASENAME];
SELECT...;

Or by running on command line: mysql -u root -p [DATABASENAME] -e "SELECT ..."

Tab delimited SQL output

SELECT title, author, publisher, isbn
FROM book
INTO OUTFILE '/tmp/outputfile.txt'

Comma separated (csv) SQL output

SELECT title, author, publisher, isbn
FROM book
INTO OUTFILE '/tmp/outputfile.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';