I recently came across an issue where I wanted to output the results of a (rather large) SQL query to a CSV file. Historically, I have been copying the DB from a backup to my local machine, but as it has grown larger, it has become increasingly difficult to do (as a result of the time it takes to download, unzip and install).
As a result, I have been looking into the possible ways in which the query might be executed on the live database (it’s only a select, so no danger of changing code) but still getting the data back in a usable form. So far, I have come up with two possibilities.
Some additional lines can be added into your query which will output a file at the location you have specified:
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
This is a suggestion that I saw on StackOverflow. Unfortunately, the server I wish to use is an RDS instance, and to the above will not work (/tmp/ cannot be written to).
The alternative method I have used is to output a tab-delimited file rather than a CSV. This can simply be done by redirecting the output of the command – e.g:
mysql -u<username> -p<password> -h <host> db_name < db_query.sql > /tmp/orders.csv