[MySQL] Run A Complex MySQL Select Statement from Remote MySQL CLI and Export to CSV

on

I thought this was pretty cool and worth sharing.

Let say we have:

  1. A complex MySQL select statement that we want to run via the MySQL command line
  2. We only have remote access to the database (we don't have local access to the instance - my use case was actually an AWS RDS MySQL DB)
  3. From the same MySQL CLI we want to export the results of our complex MySQL select statement to CSV.

And it turns out it is quite easy.

Firstly, save your MySQL select query as something like "Complex MySQL Select Query.sql"

To run it in MySQL CLI and get the output, we do:

mysql.exe -uUSERNAME -pPASSWORD -hHOSTNAME -P3307 < "Complex MySQL Select Query.sql" > "Query Results.tsv"

Note: My MySQL instance used non-default port 3307 (instead of 3306) which is why I have the -P3307.

The output is a TSV (Tab Seperated Values) so we need to convert this to CSV. The easiest way to do this is to open excel, load the TSV, and import with TAB as a delimiter and voila your data! Then save as CSV.


Note: If you're using MySQL workbench, the default install path and where you'll find MySQL.exe is:
C:\Program Files\MySQL\MySQL Workbench 8.0 CE

Comments

Post a Comment