schnippy/export_query_csv.md

## export_query_csv.md

      
    Raw
  

              export_query_csv.md
            
          
    TIL: How to export a MySQL query directly to CSV

I'm often running a single query that produces a set of results that I would like to export into a CSV.
For example, in a query against the url_alias table, I will want to grab the following and have it export into a spreadsheet rather than
mysql> SELECT source,alias FROM url_alias WHERE alias LIKE "library%";
+-----------+----------------------------------------------------------------+
| source    | alias                                                          |
+-----------+----------------------------------------------------------------+
| node/1000 | library                                                        |
| node/1019 | library/map
.. etc 
+-----------+----------------------------------------------------------------+

To start with I'll need to check what my permissions are on my target MySQL instance and I can do that with this query from the command line which checks the value of secure_file_priv:
mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

That shows me what the value for that system variable is currently -- if its set to a value that I like, I'm fine and can move to the next step, but if its NULL or if I want to change it to another directory, I'm going to need to edit the my.cnf file and add the following:
secure_file_priv="MY_DIRECTORY"

Now I can take my query and add the following magic which will wrap the output of my query into a file, exported to my filesystem:
INTO OUTFILE 'MY_DIRECTORY/myexport.csv' 
  FIELDS TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '"' 
  LINES TERMINATED BY '\n'

So my final combined query looks something like this:
SELECT source, alias 
  INTO OUTFILE 'MY_DIRECTORY/myexport.csv' 
  FIELDS TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '"' 
  LINES TERMINATED BY '\n' 
  FROM url_alias 
  WHERE alias LIKE "library%";